Dotaz: Pretizeni MySQL

[Doktor]

Mysql procesy mi zatezuji server viz:

 3648 mysql     16   0 29596  28m 3272 R 46.0  5.7   0:05.70 mysqld
 3644 mysql      9   0 29596  28m 3272 S 30.8  5.7   0:04.89 mysqld
 3610 mysql      9   0 29596  28m 3272 S 18.9  5.7   0:07.37 mysqld
 3649 mysql     15   0 29596  28m 3272 R 17.5  5.7   0:03.88 mysqld
 3616 mysql      9   0 29596  28m 3272 S 16.4  5.7   0:08.88 mysqld
 3647 mysql      9   0 29596  28m 3272 S  7.1  5.7   0:02.90 mysqld

Ktera volba v my.cnf by teoreticky mohla mit vliv na to aby se zas load snizil? Zatim to vypada ze si bere hodne z CPU. Moje Mycnf vypada nasledovne:

[mysqld]
#skip-networking
user=mysql
skip-locking
set-variable    = key_buffer=256M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=256
set-variable    = sort_buffer=1M
set-variable    = net_buffer_length=8K
set-variable    = myisam_sort_buffer_size=64M
set-variable    = thread_cache=8
set-variable    = thread_concurrency=4
set-variable    = max_connections=1000
set-variable    = max_connect_errors=100
#set-variable    = wait_timeout=120
server-id       = 1
innodb_data_home_dir = /home/mysql
innodb_data_file_path = ibdata1:10M:autoextend
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /home/mysql
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
#.._arch_dir must be the same as .._log_group_home_dir
innodb_log_arch_dir = /home/mysql
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=35M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#innodb_fast_shutdown=1
#set-variable = innodb_thread_concurrency=5

Jeste vypis pameti:

             total       used       free     shared    buffers     cached
Mem:        514980     505764       9216          0      47408     125836
-/+ buffers/cache:     332520     182460
Swap:       738980      45568     693412

Odpovědi

[Michal Čihař]

No konfigurací toho asi moc nevyřešíš, spíš udělej indexy....

[Michal]

Indexy jsem delal timto zpusobem, ale nevim jestli je to spravne

alter table thread add index (threadid);

A to nepomohlo

[Messa]

Indexy nad ID sloupečky jsou jediné indexy, které tam máš?

[razor]

Teoreticky že zvýšiš paměť přidělenou mysql. viz. Ještě můžeš skusit použít query cache.