High MySQL throughput

Question

On a big server with many user and website from 11AM - 00AM our MySQL throughput realised in max (60 - 80M) this timestamp our customer website load slowing .

We haven't any Disk IO , and server HDD : 6 * 600 G SAS 15K + 64 G RAM

MySQL config :

innodb_buffer_pool_size=8192M (2 G above mysqltuner recommendation)

Screenshot:

Any idea to solve this problem?

---

This comment add after first answer :

Hello Dear Damiano : Thank you so much your answer . about your comment : I will attach the complete screen from server monitor . (end of this comment)

about slow query : I have done this , but every 24 hrs we have under 500 slow query . and most them not occur repeatedly .

an other thing , this server big and we have High traffic web site on it , so I can not ask customer don't use server resource .

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11573    |
| Qcache_free_memory      | 27291504 |
| Qcache_hits             | 83817933 |
| Qcache_inserts          | 36512628 |
| Qcache_lowmem_prunes    | 15633028 |
| Qcache_not_cached       | 4338965  |
| Qcache_queries_in_cache | 29930    |
| Qcache_total_blocks     | 106130   |
+-------------------------+----------+

---

Here You are all thing need : http://www.4shared.com/zip/jX54_Dprba/data.html

1 screen shot + 1txt file from output server

Answer 1

From the graph you provided (that I'm reporting below, for the convenience of other readers), I saw you're running munin to monitor your server.

As such, it would help to have a look also to other graphs (like CPU, Memory, I/O and, even more important, network-traffic) that unfortunately where not included in your question.

Anyway, the munin mysql throughput plugin (that generate the graph below) describe itself as: "mysql_bytes - Plugin to monitor the number of bytes sent from and received by mysql"

So based on the graph, we saw that your MySQL is serving content to the underlying network at a speed ranging from 40MBps (Mega Bytes per second) to 70/80MBps.

Should this not be normal in your particular case, than, in addition to give a look to this answer, you can check/monitor what's going on on your server, searching for high-traffic query results.

As a first step, I suggest:

• show processlist: to see which users are connected and which query are they running on which DB, running for how much time (should they be "long");

• enable "slow query log": to see which queries are requiring lots of time (and, hence, might impact your performances while, at the same time, having high changes to be optimized);

Should this not be enough to get a figure on what is going on, you can:

• enable "general log": please note that this will cause a write-log for every query run by your mysql instance. This might seriously impact your performance so... use it wisely.

As a first guess, I think you are running under two conditions:

1. a small number of queries, returning very large recordsets, executed relatively few times (...so to generate 40/80MB of traffic) (eg.: a single recordset made by 100.000 records, each of 300 bytes, counts for up to 30MB of traffic. So two of them, per second, can... answer your problem).

2. a sligher higher number of queries, returning relatively small recordsets, but executed very frequently (...so to generate 40/80MB of traffic as well) (eg: a single recordset made by 10 records, each of 1000 bytes, counts for up to 1MB of traffic when executed 100 times per second).

The slow-query-log will help you catching the first issue. The general-log is the only one helping in the second case.

Also:

• if you are NOT experiencing any issues in terms of storage-I/O, than everything is happening in RAM and... mysql surely cached all the recordsets. In such a case show status will help you figuring out what's going on.

Should you provide further details, we might be more helpful.

P.S.: I'm not a mysql-performance-tuner at all so don't blame me for all the (more important) tuning issues I've surely missed :-)

---