roi wrote:do you think it will be good if we use persistent connection to mysql?
If your database was remote then persistent connections may be needed.
Looking closer at ...
[mysqld]
query_cache_type=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_size=128M
connect_timeout=5
max_connections=300
thread_cache=100
thread_concurrency=120
thread_stack=512000
key_buffer_size=512K
sort_buffer_size=100K
read_buffer_size=100K
max_allowed_packet=64M
... some of those numbers look odd. Here's MySQL defualt settings:
back_log: 5
bdb_cache_size: 1048540
binlog_cache_size: 32768
connect_timeout: 5
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
flush_time: 0
interactive_timeout: 28800
join_buffer_size: 131072
key_buffer_size: 1048540
long_query_time: 10
lower_case_table_names: 0
max_allowed_packet: 1048576
max_binlog_cache_size: 4294967295
max_connect_errors: 10
max_connections: 100
max_delayed_threads: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_sort_length: 1024
max_tmp_tables: 32
max_write_lock_count: 4294967295
myisam_sort_buffer_size: 8388608
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
read_buffer_size: 131072
read_rnd_buffer_size: 262144
slow_launch_time: 2
sort_buffer: 2097116
table_cache: 64
thread_concurrency: 10
thread_stack: 131072
tmp_table_size: 1048576
wait_timeout: 28800
from http://dev.mysql.com/doc/refman/4.1/en/ ... eters.html
You only have one CPU so thread_concurrency should be around 2 (4 maybe for Hyperthreading?) not 120 though. That could be one problem. The default read_buffer_size and sort_buffer_size are 128K and 2M respectively - 100K is kind of odd value. Default key_buffer is 1M, a medium size is 16M - 512K seems small. thread_stack should probably be a multiple of 1024 -- 512K = 524288. Finding the right query_cache_size is an art so good luck with that.
I think thread_cache is suppose to be thread_cache_size (bug from 4.1.16 ini file). Only reason to use thread_cache_size I can find is if you have a memory leak. In which case, thread_cache_size should equal max_connections+1.
That just what sticks out to me but I'm certainly no expert.
Have you looked at the defualt cfn/ini file that come with MySQL now? There's four files specifically for different size databases from small to huge. Seems a good place to start tweekin'.