[MDEV-9096] lot of active connection cause mysql slow ? Created: 2015-11-07  Updated: 2016-01-08  Resolved: 2016-01-07

Status: Closed
Project: MariaDB Server
Component/s: Locking
Affects Version/s: 10.0.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sysdljr Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

CentOS 6.3 , MariaDB server 10.0.21

Attachments: File my.cnf     Zip Archive mysql querstion.zip    

 Description   

hi, All
usually , our mysql server run status:
thread_connected about 1000, thread_running <10
recently , At 11:34 of every Saturday , when concurrent connection increased
thread_connected about 3000, thread_running = 1000
mysql server slow down, Almost stop read and write, continuous about 1 minutes.
After thread_running < 10, mysql read and write resume normal.

At problem time point, show engine innodb status show:
--Thread 139904453748480 has waited at btr0cur.cc line 586 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7f3ddae57a10 '&new_index->lock'
a writer (thread id 139906901120768) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.cc line 586
Last time write locked in file /home/buildbot/buildbot/build/mariadb-10.0.21/storage/xtradb/btr/btr0cur.cc line 577

pt-stalk capture files in Attachmens:mysql querstion.zip , please help analyze ,thanks.

 Comments   
Comment by Elena Stepanova [ 2015-11-08 ]

Hi,

First thing that comes to mind is that you might have too low innodb_thread_concurrency value in your configuration. There is no cnf file in your archive, but from the attached data it appears that it's 64, is it so?

I would suggest to increase it, or better still, to set it temporarily to 0 (set global innodb_thread_concurrency=0, to see if it changes the picture, and if it does, later tune it further if necessary.

Comment by Daniel Black [ 2015-11-09 ]

your sdb device is at 100% utilization for the time of this sample. vmstat is showing bo is mainly read. Can't tell if this is mysql (look for backups or other IO events), however if it is mysql, I'd suggest more ram/innodb buffer pool and/or validate that your select queries are using indexes correctly.

Comment by sysdljr [ 2015-11-09 ]

Thank you very much .

Elena Stepanova
Before the problem , we set innodb_thread_concurrency=0, max_connections= 4000 .
when problem occur, thread_connected = 4000, thread_running=2600, mysql read and write very slow.
After , first we set innodb_thread_concurrency=48， the problem too.
then ,we set innodb_thread_concurrency= 64, thread_cache_size = 1024, the same problem.

Daniel Black,
at the time point, no backup, only select ,insert,update statement more than usually ,and these sql statement have index , running very quickly usually (about 1~10 ms)
show global status like 'innodb_buffer_pool_read%'
Innodb_buffer_pool_read_requests 152175204527
Innodb_buffer_pool_reads 13209692

current buffer pool status show , Innodb_buffer_pool hit rate very high, not too small

server's my.cnf file:
my.cnf

Comment by Daniel Black [ 2015-11-09 ]

sysdljr regardless of your innodb_buffer_pool_read hit rate your IO is saturated on sdb and you'll continue to have a problem until the IO capacity of the storage is increases or the volume of block read requests is decreased.

Comment by sysdljr [ 2015-11-09 ]

Daniel Black ，thanks you quickly reply.
At the week, we plan mirgate db to high performance server, then continue trace and observation

Comment by Elena Stepanova [ 2015-12-07 ]

sysdljr, any news?

Comment by Elena Stepanova [ 2016-01-07 ]

If you have any new information on this issue, please comment to re-open the report.

Comment by sysdljr [ 2016-01-08 ]

Thanks all.
now mysql server run normal.

Generated at Thu Feb 08 07:32:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.