While simulating 500 concurrent users on our Drupal installation we made the following observations:
- In 500 users test, 415 users passed and 85 users failed. Users failed due to database constraints.
- Row locks and table scan occurs throughout the test.
- Time consuming queries also occurs throughout the test.
- After users completed their actions, Connections are not closed and tables are opened because table cache value is not set properly.
- CPU usage on database reaches maximum of 96% and average of 76%. In specific, the maximum of 90% occurs for particular period of time.
- CPU usage is normal for web server
- The Following were recorded at runtime:
Row-lock waits 361
key_buffer = 128M
join_buffer_size = 2M
read_buffer_size = 1M
sort_buffer_size = 8M
table_cache = 2000
thread_cache_size = 32
interactive_timeout = 25
connect_timeout = 4
max_allowed_packet = 64M
max_connect_errors = 100
query_cache_limit = 32M
query_cache_size = 96M
query_cache_type = 1
tmp_table_size = 64M
max_heap_table_size = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_prealloc_size = 65536
query_alloc_block_size = 131072
open_files_limit = 8196
key_buffer_size = 64M
thread_stack = 128K
log-slow-queries = /database/data/log_slow_queries.log
set-variable = max_allowed_packet=64M
default-storage-engine = innodb
What should be the configurational changes you recommend?