I am trying to optimize mysql 5.1.50 on 2 x Quad-Core Xeon with 16GB ram and Centos 5.5, I also run nginx and PHP-FPM on the same server.
Here's my.cnf file.
Code:
[mysqld]
back_log = 200
skip-networking
skip-name-resolve
skip-external-locking
skip-innodb
max_connections = 2048
key_buffer = 500M
key_buffer_size = 500M
join_buffer_size = 10M
read_rnd_buffer_size = 8M
sort_buffer_size = 10M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 300M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 2
thread_cache_size = 64
thread_concurrency=16
thread_stack = 192K
wait_timeout = 7200
interactive_timeout=7200
connect_timeout = 5
table_cache = 4096
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 20M
max_connect_errors = 5
query_cache_size =100M
query_cache_type = 1
query_prealloc_size = 1M
query_alloc_block_size = 1M
default-storage-engine = MyISAM
collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake
long_query_time=1
[mysql.server]
user=mysql
[mysqld_safe]
nice = -20
open_files_limit=100000
[mysqldump]
quick
max_allowed_packet = 100M
[mysql]
[myisamchk]
key_buffer = 500M
sort_buffer = 500M
read_buffer = 100M
write_buffer = 100M
[mysqlhotcopy]
interactive-timeout
This is results from mysqlslap
Code:
mysqlslap --user=root --auto-generate-sql --concurrency=200 --number-of-queries=10000 --number-char-cols=10 --number-int-cols=10
Benchmark
Average number of seconds to run all queries: 38.128 seconds
Minimum number of seconds to run all queries: 38.128 seconds
Maximum number of seconds to run all queries: 38.128 seconds
Number of clients running queries: 200
Average number of queries per client: 50
but mysql uses 400% CPU and only 2% RAM...is there a way to make mysql use more RAM and less CPU?