1

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:

enter image description here

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

1
  • Unfortunately the screenshot you provided is unreadable (too low resolution). Please post a better one. Also please post an updated "show status" telling us how much time passed between the two run. Jan 3, 2015 at 18:14

1 Answer 1

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 :-)


enter image description here

3
  • edite question after fist answer ...
    – Pardis
    Jan 3, 2015 at 18:05
  • comment Update with provide screen attach.
    – Pardis
    Jan 3, 2015 at 20:04
  • As I am using MONyog to monitor the server for real-time queries and it also has the option to monitor the general logs and slow logs by clicking a button which in-turn fires sql commands to the server saving me time to write sql statements to do the same. It now also monitors real-time queries with performance schema support that enables us to monitor short-lived queries also. This is life-saver I would say.
    – Mathew
    Nov 15, 2015 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.