MySQL Forums
Forum List  »  Performance

Explaination for Server behavior at High Connection counts.
Posted by: Don Courtney
Date: February 07, 2005 04:07PM

Subj: Server has sudden drop in throughput at 3000-3100 connections.
Using v4.0.21, 4.1.8, 4.1.9

This is definitely on the edge - more of a capacity issue maybe?

Overall I am getting very good throughput and resp time for a simple workload
doing transactions via a JDBC connection. However I am seeing, regardless of O.S. (linux/solaris)
that around 3000-3150 connections that a well behaving mysqld literally drops off the
cliff. It has a huge jump in User CPU. One can only guess at the internal contention
in Mysql.

There are three transactions - each does a Select for Update on a row. The workload is completely
random so it is a real stress-or.

I am using innodb storage engine. With hardly any configuration parameters.
What is commented out has been experimented with.

The I/O stats don't show hot disks - it seems to be an internal thrash of some sort.
I was wondering if anyone could comment on what might be going on WITHIN mysql and if
there are any configuration parameters that might help get MySQL to go further out - to say
3500 connections?

By and Large though mysql does ok....
Thanks
Don Courtney

Below are some fragments of the configuration and SHOW_INNODB_STATUS
output.

setup /ibdata to point to large storage in this case:
# /export2/home/mysql/ibdata
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = igendata:4G:autoextend
set-variable = max_connections=3500
#set-variable = innodb_buffer_pool_size=1800M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_buffer_pool_size=800M
#set-variable = innodb_additional_mem_pool_size = 80M
#set-variable = innodb_file_io_threads = 4 default
#set-variable = innodb_log_buffer_size = 1M default
# This variable is "unknown"
#set-variable = innodb_thread_concurrency = 8 default - check SHOW INNODB STATUS
#innodb_thread_concurrency = 4
# Setting this to 32 HURT
#innodb_thread_concurrency =32

# This permits the application to give the threads system a hint for the
# desired number of threads that should be run at the same time. This
# value only makes sense on systems that support the thread_concurrency()
# function call (Sun Solaris, for example).
# You should try [number of CPUs]*(2..4) for thread_concurrency
# default == 10?
#thread_concurrency = 20

#set-variable = query_cache=200M
set-variable = innodb_log_file_size=900M
set-variable = innodb_log_buffer_size=500k
innodb_flush_log_at_trx_commit=1
#innodb_flush_log_at_trx_commit0=0
set-variable = innodb_lock_wait_timeout=50
innodb_flush_method=fdatasync
#innodb_flush_method=O_DSYNC


The SHOW_INNODB_STATUS
Status:
=====================================
050204 10:33:16 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5469581, signal count 1947369
--Thread 11897 has waited at lock0lock.c line 3401 for 0.00 seconds the semaphore:
Mutex at 14b7e30 created file srv0srv.c line 758, lock var 1
waiters flag 0
wait is ending
--Thread 13309 has waited at lock0lock.c line 3401 for 0.00 seconds the semaphore:
Mutex at 14b7e30 created file srv0srv.c line 758, lock var 1
waiters flag 0
wait is ending
--Thread 13125 has waited at lock0lock.c line 3401 for 0.00 seconds the semaphore:
Mutex at 14b7e30 created file srv0srv.c line 758, lock var 1
waiters flag 0
wait is ending
Mutex spin waits 9569190, rounds 111097857, OS waits 4473902
RW-shared spins 18571, OS waits 10099; RW-excl spins 774732, OS waits 691210
------------
TRANSACTIONS
------------
Trx id counter 0 75599844
Purge done for trx's n:o < 0 847 undo n:o < 0 0
Total number of lock structs in row lock hash table 2309
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 14661
MySQL thread id 14652, query id 19096328 wgs93-235 129.148.93.235 mysql
show innodb status
---TRANSACTION 0 75594470, not started, OS thread id 12514 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 12505, query id 19095607 10.10.20.200 mysql statistics
select custid from customer_125 where custid = 642155 for update
---TRANSACTION 0 75598885, not started, OS thread id 14585 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 14576, query id 19095848 10.10.20.200 mysql statistics
select custid from customer_125 where custid = 373186 for update
---TRANSACTION 0 75599439, not started, OS thread id 14655
MySQL thread id 14646, query id 19096291 10.10.20.200 mysql
---TRANSACTION 0 75599787, not started, OS thread id 12763
MySQL thread id 12754, query id 19096186 10.10.20.200 mysql
---TRANSACTION 0 75598842, not started, OS thread id 12241 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 12232, query id 19095601 10.10.20.200 mysql statistics
select custid from customer_125 where custid = 436041 for update
---TRANSACTION 0 75594639, not started, OS thread id 14594
MySQL thread id 14585, query id 19095353 10.10.20.200 mysql
---TRANSACTION 0 75599256, not started, OS thread id 12710 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 12701, query id 19095470 10.10.20.200 mysql statistics
select custid from customer_125 where custid = 500222 for update
---TRANSACTION 0 75594286, not started, OS thread id 12343 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 12334, query id 19095650 10.10.20.200 mysql statistics
select custid from customer_125 where custid = 811431 for update
---TRANSACTION 0 75598944, not started, OS thread id 14091 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 14082, query id 19095272 10.10.20.200 mysql statistics
select custid from customer_125 where custid = 622915 for update
---TRANSACTION 0 75594757, not started, OS thread id 12388 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 12379, query id 19096233 10.10.20.200 mysql statistics
select custid from customer_125 where custid = 283078 for update

Options: ReplyQuote


Subject
Views
Written By
Posted
Explaination for Server behavior at High Connection counts.
4062
February 07, 2005 04:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.