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.

[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

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?

Using 400% of anything is impossible.

that's what top shows under %cpu

What it actually means is an average of 25% of EACH CORE so it shows as 2x4x25%


http://stackoverflow.com/questions/1...high-cpu-usage

thanks for this info
the benchmark generates random queries so It's probably something on the configuration not the DB structure, I would like to know if my.cnf file is not well configured

That will depend on what usage you have set the configuration for and what use you are putting the server to.

I have a DB contains 6 tables with about 6million records ( 2.5 GB )
I do not do a lot of inserts or updates, I only select data

And how efficient are the queries?

such as using "SELECT * FROM"

pretty fast ( 0.001 sec )
but I'm more concerned about the load because it's a new server and I'm going to move a high traffic domain to it.

You using INNODB for the tables?