Most important are the data buffers.

I'll assume you're using MyISAM so try this to get the size of cache for MyISAM indexes:

SHOW VARIABLES LIKE 'key_buffer_size'

That's the size in bytes.

Then you can guess the optimum value (making sure it's with limits of your available RAM) by doing:

SHOW TABLE STATUS WHERE Engine='MyISAM';

The sum of all the "Index_length" columns would be your optimum key_buffer size (plus a few megs).

Ammended configuration:

While simulating 500 concurrent users on our Drupal installation we made the following observations:


1) In 500 users test, 415 users passed and 85 users failed. Users failed due to database constraints.
2) Row locks and table scan occurs throughout the test.
3) Time consuming queries also occurs throughout the test.
4) After users completed their actions, Connections are not closed and tables are opened because table cache value is not set properly.
5) CPU usage on database reaches maximum of 96% and average of 76%. In specific, the maximum of 90% occurs for particular period of time.
6) CPU usage is normal for web server
7) The Following were recorded at runtime:

Slow-queries 2,064
Buffer-pool-reads 8220
Row-lock waits 361
Handler-read-rnd 2134
Tmp_disk_tables 256
Opened-tables 896
Max-connections 250

My.ini:

[mysqld]
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
wait_timeout= 3600
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

set-variable=long_query_time=1
log-slow-queries = /database/data/log_slow_queries.log

myisam_sort_buffer_size =32M

datadir=/database/data
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=300
set-variable = max_allowed_packet=64M
default-storage-engine = innodb
log-bin=/database/data/mysql-bin

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

What should be the configurational changes you recommend?

I still have no idea how large your MyISAM indexes are, so I can't make an absolutely accurate value for your settings. But these might help:

MyISAM key buffer:
key_buffer = 512M // I'm just guessing your indexes are <= 512 Mb in size

(You list two key_buffer's, one being an alias in you config, remove one).

I have no idea what size you tmp tables are when created, nor do I know if they contain syntax that forces them to be written on disk, but to possibly reduce tmp tables being written to disk you could try:

tmp_table_size = 128M
max_heap_table_size = 128M

Hi shyamala,

I agree with Speeple, and I am not the SQL expert, but may be I think you will get the right solution on the SQL forum. Just search on the internet "sql forum".

John Philips

Foreclosed Homes