0

I have some issues with my MySQL server

# Time: 2017-10-30T09:25:41.253828Z # User@Host: dbXXXXXXXXXXXX[dbXXXXXXXXXXXX] @ localhost [] Id: 469 # Query_time: 26.990475 Lock_time: 0.000039 Rows_sent: 0 Rows_examined: 1 use dbXXXXXXXXXXXX; SET timestamp=1509355541; UPDATE online SET last_ping=1509355514 WHERE `device`='wggggn4gggk2gggggXYgEokgggggpOggf';

The table is super-simple and contains around 1000 rows https://cdn.pbrd.co/images/GRjN6fE.png

I have no idea why it takes so long sometimes. The last_ping is updated every 5 seconds when the device is online.

Or this one from WordPress:

# Time: 2017-10-30T07:55:24.755752Z # User@Host: sobotiste[sobotiste] @ localhost [] Id: 22317 # Query_time: 9.586909 Lock_time: 0.000113 Rows_sent: 0 Rows_examined: 1 use sobotiste; SET timestamp=1509350124; UPDATE `wp_options` SET `option_value` = '1509351915' WHERE `option_name` = '_transient_timeout_4__974818834';

What should I do?

Version of server (from PMA):

Server: Localhost via UNIX socket Server type: MySQL Server version: 5.7.20-0ubuntu0.16.04.1-log - (Ubuntu) Protocol version: 10 User: root@localhost Server charset: UTF-8 Unicode (utf8)

my.cnf:

[mysqld] query_cache_type=1 long_query_time=8 slow_query_log=ON query_cache_limit=32M innodb_buffer_pool_size=3G innodb_log_file_size=256M innodb_log_buffer_size=4M join_buffer_size=512K max_heap_table_size=64M tmp_table_size=64M table_open_cache=1024 innodb_lru_scan_depth=256 key_buffer_size=32M
Improve this question
1
  • Could you please describe the environment? OS, hypervisor, disk configuration. MySQL startup options. Commented Oct 30, 2017 at 12:46

1 Answer 1

Reset to default
2

When you have performance problems you have to see the entire picture to understand where you can optimize. Focusing on one single symptom will hide other important details you are missing.

Usually on Wordpress, each SQL query is running in a single transaction. This means that the UPDATE/DELETE/INSERT/ALTER query returns that is it completed only after the data is on disk.

The query could be affected by other queries that are locking rows or tables, but this is not the case for you because Lock_time is low.

This means that this simple query is the problem by itself. This query depends on the disk system to be fast. Your MySQL disk speed will be affected by other clients using it, like:

  • other VMs using the same disk array
  • other processes on the same machine or on a different machine which uses the same disk array
  • background check and recovery on disk array
  • storage migration at array or hypervisor level

What can you do from inside your machine. First determine if you are running inside a VM. If it is a VM chances that it is shared with other tenants are very high.

Check the latency on the disk. For Linux run iostat -x 1 and look on await column. Should be as low as possible. For Windows check https://blogs.technet.microsoft.com/askcore/2012/02/07/measuring-disk-latency-with-windows-performance-monitor-perfmon/

A latency over 200ms is high.

Remember that the performance depends on your perception. In 2000 the performance of a fast application would be considered slow today.

Improve this answer
4
  • It's running on Microsoft Azure (4 cores, 8GB RAM). You can look at stats from Munin (pinf.sk/ihost/d/1509366954-1160019329.png) Commented Oct 30, 2017 at 12:36
  • The disk is sometimes slow. Munin is showing average for a time frame of 5 minutes or more. This means you will see the 1-5 seconds when the storage was frozen and the data will be smoother by average. So 167.37ms wait time in average could be a few seconds of waiting on storage. Commented Oct 30, 2017 at 12:45
  • Is there a way I can make the disk faster? Commented Oct 30, 2017 at 12:51
  • 1
    Yes. Use SSD. Use newer SSD. Use the disk only for your application. Make statistics on the disk read/write operation sizes and change the strip size and cluster size to make is smaller or bigger. Use RAID10 instead of RAID5 or 6. Another solution is avoiding the disk. Do you need to run those queries? If the data is only for statistics, why not caching the data into memory and write in one single update once per minute. Another solution if you do not care about the consistency of the data to write those queries to run asynchronous so that the web pages are not waiting for them. Commented Oct 30, 2017 at 13:00

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.