Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » MySQL Replication
MySQL Replication [message #853] Mon, 05 March 2007 00:32 Go to next message
LinuxFreak is currently offline LinuxFreak
Messages: 11
Registered: March 2007
Location: Pakistan
Junior Member
Dear Peter,
Hello,

I am working for a company which have huge traffic on their website. They have huge Database and using MySQL Replication.

REPLICATION MYSQL SERVER A
REPLICATION MYSQL SERVER B
REPLICATION MYSQL SERVER C ----> WEBSITE <---> MASTER MYSQL SERVER
REPLICATION MYSQL SERVER D
REPLICATION MYSQL SERVER E

Now, ALL SELECT's statements executed from SERVER A,B,C,D,E and ALL INSERT,DELETE,UPDATE,CREATE,DROP's statements executed from MASTER MYSQL SERVER.

The problem is there will be very high load on SERVER A,B,C,D,E. Can you please help me with that. Please ask me if you need any info.

Best Regards.

Farrukh Ahmed
Report message to a moderator
Re: MySQL Replication [message #854 is a reply to message #853 ] Mon, 05 March 2007 04:16 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
I'm not sure what your question has to do with MySQL Replication.

you have high load on your severs, I assume from MySQL you need to check MySQL settings and most important queries you're running to see if you can optimize them. Check EXPLAIN statement for the queries to start with.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: MySQL Replication [message #855 is a reply to message #854 ] Mon, 05 March 2007 06:07 Go to previous messageGo to next message
LinuxFreak is currently offline LinuxFreak
Messages: 11
Registered: March 2007
Location: Pakistan
Junior Member
Dear Peter,
Hello,

This is an example query which took 13.48 Seconds to EXECUTE and when there 40 to 50 queries then server load spikes to 120.

We have Dual Xeon 3.6 GHz with 4 GB of RAM on this replication node.

mysql> EXPLAIN SELECT keyword AS title, rank, ondate
-> FROM (SELECT * FROM seo_keyword_report
-> WHERE (rank /10) >1
-> ORDER BY ondate DESC
-> )keywords
-> GROUP BY keyword
-> ORDER BY rank ASC
-> limit 4,0;
+----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- -------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- -------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 233308 | Using temporary; Using filesort |
| 2 | DERIVED | seo_keyword_report | ALL | NULL | NULL | NULL | NULL | 389861 | Using filesort |
+----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- -------------------+
2 rows in set (13.48 sec)

Best Regards.

Farrukh Ahmed
Report message to a moderator
Re: MySQL Replication [message #856 is a reply to message #855 ] Mon, 05 March 2007 06:10 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Right.

You've got to rewrite this query - derived table you're using is going to be very slow.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: MySQL Replication [message #857 is a reply to message #853 ] Mon, 05 March 2007 06:16 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Forgot to mention.

The query is also designed wrong. Why would you sort in derived table if you sort again in external query - derived tables do not need order by (unless you use limit)

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: MySQL Replication [message #858 is a reply to message #857 ] Mon, 05 March 2007 06:23 Go to previous messageGo to next message
LinuxFreak is currently offline LinuxFreak
Messages: 11
Registered: March 2007
Location: Pakistan
Junior Member
Dear Peter,
Hello,

Can you please give me an better example of above query which i have mentioned.

Best Regards.

Farrukh Ahmed
Report message to a moderator
Re: MySQL Replication [message #859 is a reply to message #855 ] Mon, 05 March 2007 08:10 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
You can do something like:

select * from seo_keyword_report where rank>0.1 group by keyword order by rank ask limit 4

I'm not sure what you have your derived table for at all.

But in general you need to normalize the data so you do not need to do group by keyword in this case you can do it fast.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: MySQL Replication [message #860 is a reply to message #853 ] Mon, 05 March 2007 08:24 Go to previous messageGo to next message
LinuxFreak is currently offline LinuxFreak
Messages: 11
Registered: March 2007
Location: Pakistan
Junior Member
Dear Peter,
Hello,

I am pasting you describe of my tables;

mysql> describe seo_keyword_report;
+------------------+------------------+------+-----+-------- -------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+-------- -------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| keyword | varchar(255) | | MUL | | |
| search_engine | varchar(255) | | | | |
| rank | int(11) | YES | | NULL | |
| url | tinytext | | | | |
| competetion | int(10) unsigned | | | 0 | |
| ondate | timestamp | YES | | 0000-00-00 00:00:00 | |
+------------------+------------------+------+-----+-------- -------------+----------------+
7 rows in set (0.00 sec)

and indexes on this table

mysql> show index from seo_keyword_report;
+-----------------------------+------------+------------+--- -----------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------------+------------+------------+--- -----------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+
| seo_keyword_report | 0 | PRIMARY | 1 | id | A | 389883 | NULL | NULL | | BTREE | |
| seo_keyword_report | 1 | keyword | 1 | keyword | A | 129961 | NULL | NULL | | BTREE | |
+-----------------------------+------------+------------+--- -----------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+
2 rows in set (0.00 sec)

The problem is when i removed ORDER BY ondate DES and ORDER BY rank ASC then this query will return results in seconds but we need latest date result for which we need to use ORDER BY ondate DES and also we need rank in ascending order so we have to use this ORDER BY rank ASC

Best Regards.

Farrukh Ahmed
Report message to a moderator
Re: MySQL Replication [message #861 is a reply to message #860 ] Mon, 05 March 2007 08:31 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Well Change order by to sort by rank,date when or whatever.

But again as I mentioned you need to normalize your data for best speed.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: MySQL Replication [message #862 is a reply to message #853 ] Mon, 05 March 2007 08:42 Go to previous messageGo to next message
LinuxFreak is currently offline LinuxFreak
Messages: 11
Registered: March 2007
Location: Pakistan
Junior Member
Dear Peter,
Hello,

I used this query manually but its giving me an error.

mysql> select title AS keyword, rank, ondate from seo_keyword_report where rank>10 sort by rank,date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sort by rank,date' at line 1

Best Regards.

Farrukh Ahmed
Report message to a moderator
Re: MySQL Replication [message #863 is a reply to message #862 ] Mon, 05 March 2007 08:57 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
order by rank.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Previous Topic:autoincrement in mysql vs oracle
Next Topic:Backup: "Can't create/Write to file "F:\etc. etc.....
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 02:04:02 EDT 2009

Total time taken to generate the page: 0.28207 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.