Server version: 5.0.91-community MySQL Community Edition (GPL)
 

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 64M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
max_connections = 500

#log-slow-queries = /var/log/mysql-slow-queries.log
#log-error = /var/log/mysql-error.log


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 

em đã thử edit my.cnf và enable mysqld log và slow queries log nhưng khi restart mysql thì log file không được tạo ra.
 

[mysqld]
.....
.....
.....
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1 

tail -f /var/log/mysql/mysql-slow.log
# Query_time: 4 Lock_time: 0 Rows_sent: 81212 Rows_examined: 477199
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%à¸%' OR p.es_name_eng LIKE '%à¸%' AND p.es_cid='3' );
# Time: 110101 0:24:03
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 10 Lock_time: 0 Rows_sent: 20 Rows_examined: 479659
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%à¸%' OR p.es_name_eng LIKE '%à¸%' AND p.es_cid='3' ) ORDER BY es_update_on DESC LIMIT 2440, 20;
# Time: 110101 0:24:06
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 45964 Rows_examined: 406703
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%ห%' OR p.es_name_eng LIKE '%ห%' AND p.es_cid='8' );
# Time: 110101 0:24:13
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 20 Rows_examined: 431243
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%ห%' OR p.es_name_eng LIKE '%ห%' AND p.es_cid='8' ) ORDER BY es_update_on DESC LIMIT 24520, 20;
# Time: 110101 0:24:17
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 10 Lock_time: 0 Rows_sent: 61731 Rows_examined: 438237
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%à¸%' OR p.es_name_eng LIKE '%à¸%' );
# Time: 110101 0:24:19
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 2 Lock_time: 0 Rows_sent: 20 Rows_examined: 482137
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%à¸%' OR p.es_name_eng LIKE '%à¸%' ) ORDER BY es_update_on DESC LIMIT 43880, 20;
# Time: 110101 0:24:42
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 18 Lock_time: 0 Rows_sent: 172482 Rows_examined: 659739
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%ส%' OR p.es_name_eng LIKE '%ส%' AND p.es_cid='3' );
# Time: 110101 0:24:55
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 14 Lock_time: 0 Rows_sent: 117465 Rows_examined: 549705
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%à¸%' OR p.es_name_eng LIKE '%à¸%' AND p.es_cid='3' );
# Time: 110101 0:24:59
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 20 Rows_examined: 558225
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%à¸%' OR p.es_name_eng LIKE '%à¸%' AND p.es_cid='3' ) ORDER BY es_update_on DESC LIMIT 8500, 20;
# Time: 110101 0:25:09
# User@Host: nalueng_main[nalueng_main] @ localhost []
# Query_time: 27 Lock_time: 0 Rows_sent: 20 Rows_examined: 659759
SELECT DISTINCT p.* FROM esb2b_phone p, esb2b_phone_categories pc WHERE p.es_cid=pc.es_id AND p.es_active='1' AND (p.es_name_thai LIKE '%ส%' OR p.es_name_eng LIKE '%ส%' AND p.es_cid='3' ) ORDER BY es_update_on DESC LIMIT 0, 20;

 

http://www.ovaistariq.net/479/mysql-query-optimization-tip-1-avoid-using-wildcard-character-at-the-start-of-a-like-pattern/
 

Việc bạn cung cấp hosting thì hơi khó vì mỗi ông code một kiểu rồi vứt lên server (query gì mà 13 giây còn chưa xong ) hành hạ tài nguyên server của bạn. Mình nghĩ nên tạo một số policy như 1 query quá lâu hoặc cpu quá cao thì hệ thống sẽ kill cái process đó nhằm đảm bảo tính ổn định của server.