Всем здравствуйте!
Получил в наследство сервер и с ним имеются определенные проблемы. Одна из имеющихся - мускул потребляет ~80-90% CPU. Движок системы - xoops и exoops. Версия не менялась как я понял с 2005 года.
Буквально пару дней назад вырос параметр softirq у CPU на графиках munin.
В какую сторону копать-смотреть?
На сервере
[root@se ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 2
On-line CPU(s) list: 0,1
Thread(s) per core: 1
Core(s) per socket: 2
CPU socket(s): 1
NUMA node(s): 1
Vendor ID: AuthenticAMD
CPU family: 15
Model: 43
Stepping: 1
CPU MHz: 2211.387
BogoMIPS: 4421.89
L1d cache: 64K
L1i cache: 64K
L2 cache: 512K
NUMA node0 CPU(s): 0,1
free -m
total used free shared buffers cached
Mem: 3271 2819 452 0 155 1219
-/+ buffers/cache: 1443 1827
Swap: 1913 30 1883
[mysqld]
log-error=/var/log/mysql/error.log
user=mysql
log-slow-queries=/var/log/mysql/log-slow-queries.log
long_query_time = 1
query_cache_size = 96M
thread_cache_size=70
wait_timeout=30
max_user_connections=40
max_connections=100
#default-character-set=cp1251
#skip-grant-tables
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/fast/mysql
socket=/fast/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
init-connect="SET NAMES cp1251"
##############
key_buffer = 256M
max_allowed_packet = 16M
table_cache = 3072
table_definition_cache = 3072
sort_buffer_size = 3M
read_buffer_size = 3M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 456M
tmp_table_size = 192M
max_heap_table_size = 192M
################
#thread_cache_size = 8
#key_buffer_size = 256M
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
socket=/fast/mysql/mysql.sock
10 самых долгих запросов:
Count: 73882 Time=3.51s (259218s) Lock=0.69s (50637s) Rows=1.0 (73868), 2users@localhost
SELECT COUNT(*) FROM xps33_stories WHERE published > N AND published <= N AND (expired = N OR expired > N) AND ihome=N
Count: 51493 Time=5.77s (297255s) Lock=0.01s (389s) Rows=1.7 (87203), 2users@localhost
SELECT * FROM xps33_xoopscomments WHERE (com_rootid = 'S' AND com_id >= 'S')
Count: 16046 Time=4.19s (67210s) Lock=0.00s (19s) Rows=15.0 (240635), diginew[diginew]@localhost
SELECT p.*, t.* FROM xps33_bb_posts AS p LEFT JOIN xps33_bb_posts_text AS t ON t.post_id = p.post_id WHERE (p.forum_id IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND p.approved = 'S') ORDER BY p.post_time DESC LIMIT N, N
Count: 13468 Time=4.33s (58271s) Lock=0.01s (80s) Rows=15.0 (201938), 2users@localhost
SELECT p.*, t.* FROM xps33_bb_posts AS p LEFT JOIN xps33_bb_posts_text AS t ON t.post_id = p.post_id WHERE (p.forum_id IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND p.approved = 'S') ORDER BY p.post_time ASC LIMIT N, N
Count: 12061 Time=2.51s (30264s) Lock=0.30s (3608s) Rows=1.0 (12048), seti[seti]@localhost
select count(*) from m_seti_ann_annonces where cid=N and valid='S'
Count: 9215 Time=4.56s (42019s) Lock=0.43s (3949s) Rows=29.7 (273582), diginew[diginew]@localhost
SELECT s.*, t.* FROM xps33_stories s, xps33_topics t WHERE (published > N AND published <= N) AND (expired = N OR expired > N) AND (s.topicid=t.topic_id) AND ihome=N AND t.topic_frontpage=N ORDER BY s.published DESC LIMIT N, N
Count: 5534 Time=2.41s (13320s) Lock=0.00s (1s) Rows=15.0 (82992), diginew[diginew]@localhost
SELECT p.*, t.* FROM xps33_bb_posts AS p LEFT JOIN xps33_bb_posts_text AS t ON t.post_id = p.post_id WHERE (p.forum_id IN (N) AND p.approved = 'S') ORDER BY p.post_time ASC LIMIT N, N
Count: 4613 Time=2.53s (11687s) Lock=0.00s (3s) Rows=15.0 (69193), diginew[diginew]@localhost
SELECT p.*, t.* FROM xps33_bb_posts AS p LEFT JOIN xps33_bb_posts_text AS t ON t.post_id = p.post_id WHERE (p.forum_id IN (N) AND p.approved = 'S') ORDER BY p.post_time DESC LIMIT N, N
Count: 4439 Time=2.30s (10226s) Lock=0.02s (87s) Rows=10.0 (44350), diginew[diginew]@localhost
SELECT * FROM xps33_ams_article n, xps33_ams_text t, xps33_ams_audience a WHERE t.storyid=n.storyid AND n.audienceid=a.audienceid AND published > N AND published <= N AND (expired = N OR expired > N) AND t.current=N ORDER BY published DESC LIMIT N, N
Count: 4235 Time=3.21s (13609s) Lock=0.50s (2099s) Rows=0.0 (195), seti[seti]@localhost
SELECT lid, title, type, description, date, email, submitter, photo, view FROM m_seti_ann_annonces WHERE valid='S' and date < (UNIX_TIMESTAMP()-N*N)
