1

I have mysql 5.6 server which handles thousands of users online. All tables are Innodb. The problem is, that once or twice a day it writes huge amount of data to disk, or it slows down a lot. Than when I do show processlist it shows my many connections in init mode with comit status. That comit status last for seconds. My server is hosted on digitalocean.com with 32GB ram, SSD drives. Those SSD drives should be fast enough to handle load like 50MB/S. I see on the graph, that it writes on average around 7MB/s. When the graph stays at fixed 7MB/s eveerything is fine. But sometimes it skyrrocket to 50MB/S, or it drops to 1 MB/s. Everytime I had seen that slow mysql with hanging comit, the graph was significanly down or up compared to average 7 MB/s. I think I have something configured wrong, I need DB to write data constantly without peeks.

Here is the log from show processlist:

# User@Host: xxxx[xxxx] @  [x.x.x.x]  Id:  1973
# Query_time: 10.575590  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1442129285;
commit;
# User@Host: xxxx[xxxx] @  [x.x.x.x]  Id:  1969
# Query_time: 10.575776  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1442129285;
commit;
# User@Host: xxxx[xxxx] @  [x.x.x.x]  Id:  1986
# Query_time: 10.573407  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1442129285;
commit;
# User@Host: xxxx[xxxx] @  [x.x.x.x]  Id:  1980
# Query_time: 10.565923  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1442129285;
commit;

Here is my config realted to IO:

wait_timeout=180
interactive_timeout=180

#collation_server=utf8_general_ci
#have_openssl=YES
#have_ssl=YES
innodb_autoinc_lock_mode=2
innodb_flush_method=O_DIRECT

innodb_buffer_pool_instances=2
innodb_buffer_pool_size=23844618240

innodb_io_capacity=4000
innodb_io_capacity_max=8000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

#log_slow_queries=ON

open_files_limit=65535
slow_query_log=ON
sort_buffer_size=2097152
sync_master_info=0
sync_relay_log=0
sync_relay_log_info=0
#system_time_zone=UTC
#time_zone=UTC

max_connections=1263

Here is InnoDB status:

    LOG
---
Log sequence number 977317803626
Log flushed up to   977317803626
Pages flushed up to 977317651125
Last checkpoint at  977317650850
0 pending log writes, 0 pending chkp writes
19379418 log i/o's done, 52.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 24403476480; in additional pool allocated 0
Dictionary memory allocated 808197
Buffer pool size   1455358
Free buffers       1997
Database pages     1393457
Old database pages 514350
Modified db pages  914
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 23853945, not young 102799162
61.30 youngs/s, 139.99 non-youngs/s
Pages read 16739292, created 170687, written 53202611
29.96 reads/s, 0.22 creates/s, 116.82 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1393457, unzip_LRU len: 0
I/O sum[21338]:cur[106], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   727679
Free buffers       996
Database pages     696689
Old database pages 257163
Modified db pages  471
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11962922, not young 50931719
30.61 youngs/s, 72.17 non-youngs/s
Pages read 8363107, created 85725, written 27752850
14.74 reads/s, 0.17 creates/s, 59.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 696689, unzip_LRU len: 0
I/O sum[10669]:cur[53], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   727679
Free buffers       1001
Database pages     696768
Old database pages 257187
Modified db pages  443
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11891023, not young 51867443
30.69 youngs/s, 67.82 non-youngs/s
Pages read 8376185, created 84962, written 25449761
15.22 reads/s, 0.04 creates/s, 57.82 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 696768, unzip_LRU len: 0
I/O sum[10669]:cur[53], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 1805, id 140059716314880, state: sleeping
Number of rows inserted 4561349, updated 29374965, deleted 1787040, read 256358875085
12.43 inserts/s, 50.13 updates/s, 0.52 deletes/s, 938849.96 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Thank you for any help.

EDIT:

My Show Global Status file is here: http://pastebin.com/GEWdwam7

EDIT2:

Here is link to my hdd usage graph. Can anybody explain me why are those drops and spikes? When I have user peek the graph is plan line at 7 MB. When there is half the people graph looks like the one I uploaded. I never had problem with my server when the graph was plain. Problem occurs during spikes. Sometimes it spikes from 1 MB to 70Mb/s for few minutes. How should I configure my server to prevent any spikes and to have almost constant write IOPS?

  • Please add output of SHOW GLOBAL STATUS; to the question. (Maybe as a pastebin link if it is too big) – jkavalik Sep 13 '15 at 20:43
  • Hello, I edited the answer with the link to that file. – Libor Havlicek Sep 13 '15 at 21:13
  • Please add SHOW VARIABLES; Many of the STATUS values are useless without knowing the corresponding VARIABLES. – Rick James Sep 13 '15 at 22:52
  • "Thousands of users" -- Is this a hosting service? Do you have any control over the queries that are run? – Rick James Sep 13 '15 at 22:54
  • It is on digitalocean.com. I have virual server, with full root access. DB server is separate machine from app server. I have developed the app server myself, I have controll on every single query it runs. – Libor Havlicek Sep 13 '15 at 23:04

Your Answer

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

Browse other questions tagged or ask your own question.