If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > optimizing mysql 5.1

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-21-10, 11:42
gadelkareem gadelkareem is offline
Registered User
 
Join Date: Aug 2010
Posts: 1
optimizing mysql 5.1

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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On