SELECT Intensive mysql running slow. Avg 2 second response time

[Muni Bajpai]

Hi all,

I would like an help for an optimal my.cnf for mysql 4.1 on a machine with apache + php.

Its a dual core xeon 3.0 and 2GB of RAM 250 GB SATA.
=============================================================================
My site is SELECT intensive with lots of short connections for Selects.
Mtop output
load average: 0.07, 0.25, 0.37 mysqld 4.1.20-log up 0 day(s), 5:18 hrs
1 threads: 1 running, 75 cached. Queries/slow: 1.0M/0 Cache Hit: 99.81%
Opened tables: 232 RRN: 4.8M TLW: 2.1K SFJ: 21 SMP: 0 QPS: 0

Recommendations below come from various places in the MySQL manual.

Cache Hit Ratio: 99.81%
Tmp tables converted to disk: 0.00%
(Increase tmp_table_size to decrease this)
Number of joins without keys (Select full joins): 21
(Look for joins which don't use indexes until SFJ = 0)
Full index scans: 0.00%
Index utilization: 0.80%
Range or Table Scans: 0.48%

===============================================================================

Existing my.cnf

skip-networking
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords = 1
back_log = 75
max_connections = 300
key_buffer = 128M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 256
thread_cache_size = 128
interactive_timeout = 10
wait_timeout = 20
connect_timeout = 20
max_allowed_packet = 16M
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 16M
max_connect_errors = 20
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 8M
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 512K
query_alloc_block_size = 1M
thread_concurrency = 4
default-storage-engine = MyISAM
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2

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

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M


Thanks in advance.

Muni



Edited 2 time(s). Last edit at 09/25/2006 08:31AM by Muni Bajpai.