MySQL Replication

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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.

3

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 | | 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.

4

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)

5

Dear Peter,
Hello,

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

Best Regards.

6

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.

7

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.

8

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.

9

order by rank.