Slow write performance at random times

[Gerry]

I have an issue with our MySQL server which is used by quite some different users. It is a shared hosting server, so I can’t change much about the queries being issued.

The problem is that sometimes there are a lot of UPDATE and INSERT queries queued up, sometimes for 5-10 seconds, sometimes for more than 1 minute. The queries in queue are mostly random, I don’t see a pattern in it. When one query is blocking everything, the others don’t get processed either.

In attachment a screenshot that illustrates the problem.

The tables are mostly MyISAM, although there are some users using InnoDB.

Does anyone have a pointer as to what variables I could tweak?
The server is running 5.0.87-d10-ourdelta65 and has 8 cores at 2.93Ghz and 32GB RAM. The config is below.

ft_min_word_len=3innodb_file_per_table = 1innodb_thread_concurrency = 16innodb_buffer_pool_size = 2048Minnodb_additional_mem_pool_size = 12Minnodb_flush_log_at_trx_commit = 2myisam_sort_buffer_size = 256Mkey_buffer = 1Gsort_buffer_size = 10Mmax_connections=1000max_user_connections=40max_allowed_packet=16Mquery_cache_size=8192Mquery_cache_limit=2Mtable_cache=4096bind-address = 0.0.0.0wait_timeout=45connect_timeout=10interactive_timeout=45max_heap_table_size=384Mtmp_table_size=256Mread_buffer_size=512Kjoin_buffer_size=8Mthread_cache_size = 20log_slow_queries = /var/log/mysql/mysql-slow.loglog-error = /var/log/mysql/err.loglog_warningsskip-name-resolve

[justin.swanhart]

MyISAM tables use table level locking.

Move the remainder of your tables to InnoDB.

[Gerry]

Is that the only option I have? Since it’s a shared hosting environment, I’m afraid I don’t have much input on what customers are doing other than trying to cope with whatever they throw at us.

Oh yes, by the way, the queries in the screenshot are almost all on different databases & tables, there’s no relation between them other than all waiting to be executed.

[justin.swanhart]

Queries in ‘Locked’ state only get that way due to MyISAM table level locking.

You are seeing classic table level lock problem.

Your screenshot clearly shows the blocking statements. You have an application that is updating “whos_online” and “sessions”, etc, and that holds locks.

[Gerry]

Thank you for your reply. )

Last night we restarted the MySQL server and we are not seeing this problem at all anymore today. The number of queries is the same but it didn’t stall at all anymore.
I can also see the cache being more efficient and the load being significantly less (~1.00 avg to 0.2 avg).

Any thoughts as to why that is?

[Gerry]

The server has been up for 3 days now, with 3 days of comparable load, number of queries and traffic.
However, what I’m seeing is that every day the number of slow queries is increasing.

Day one: 13
Day two: 45
Day three: 125
Day four (partial, 06:00-11:00): 90

I have examined the slow query log and what I’m seeing is that when there’s a queue of write queries, there are InnoDB tables involved as well, such as this one:

Time: 101126 9:26:36# User@Host: drupaluser[drupaluser] @ [11.22.33.44]# Thread_id: 4969510 Schema: drupaldb# Query_time: 68.889095 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 0 Rows_affected: 1 Rows_read: 2use drupaldb;INSERT INTO term_node (nid, vid, tid) VALUES (16390, 16390, 35);

A really simple query it would seem.

I really don’t understand how this could happen, even with InnoDB. Also, it is really remarkable that every day it gets a bit worse, seeing how the slow queries increase. Any ideas or pointers?

[sterin]

Can we start to establish if you are seeing high CPU or high I/O wait?
By judging from your posts I’m guessing high I/O wait but it’s important to know.

How large is the total amount of data that this server handles?

And if you are using InnoDB a lot then why is the InnoDB buffer set to only 2GB when you have 32GB on the machine?
Even with the MyISAM key cache that is set to 1GB it still feels like you should increase these values with so much memory in the machine.

As for trying to know what InnoDB is doing you can use the
“SHOW ENGINE INNODB STATUS” command to get better knowledge about it.

[Gerry]

When the queue is building up, there is no iowait at all, the server has a RAID10 with four 15k RPM SAS disks. In fact, the server has 0.0% iowait overall (we monitor that).

I don’t know if the CPU is heavily used at those times but seeing from the graphs I guess not. There is no spike at all. I had that impression as well when I witnessed it, it seemed as if MySQL was just pausing for some time.

The total amount of data is roughly 14GB, of which about 1.5GB is in InnoDB tables.

Thanks for the tips, I’ll increase the InnoDB buffer & MyISAM key caches at the next restart.