Explaination for Server behavior at High Connection counts.

[Don Courtney]

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