Вот такой запрос лидирует по частоте и по времени:
SELECT BE.ID as ID,BE.NAME as NAME,BE.PREVIEW_TEXT as PREVIEW_TEXT,BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1=1 AND ( ((((BE.IBLOCK_ID = '39')))) AND ((((BE.NAME LIKE '5277-9999')))) ) AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
Такое ощущение, что это какая то битрикс компонента выполняется.
>> MySQLTuner 1.2.0 — Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password — SECURITY RISK!
— General Statistics — [--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95
[OK] Operating on 64-bit architecture
— Storage Engine Statistics — [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6M (Tables: 14)
[--] Data in InnoDB tables: 1G (Tables: 238)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 1
— Security Recommendations — [!!] User 'root@localhost' has no password set.
— Performance Metrics — [--] Up for: 1d 2h 32m 39s (15M q [159.264 qps], 125K conn, TX: 31B, RX: 9B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 5.2G global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 5.5G (34% of installed RAM)
[OK] Slow queries: 0% (0/15M)
[OK] Highest usage of available connections: 8% (8/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/8.6M
[OK] Key buffer hit rate: 100.0% (593K cached / 0 reads)
[OK] Query cache efficiency: 66.1% (8M cached / 13M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 242K sorts)
[!!] Joins performed without indexes: 12488
[!!] Temporary tables created on disk: 28% (143K on disk / 502K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 749K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 99% (21M immediate / 21M locks)
[OK] InnoDB data size / buffer pool: 1.4G/4.0G
— Recommendations — General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
Спасибо за утилиту! Уже кое что начало проясняться.
Я так полагаю следует поменять настройки на предлагаемые?
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# this lines for DB recovery:
#max_allowed_packet=128M
#default-character-set=cp1251
#character-set-server=cp1251
#collation-server=cp1251_bin
#init-connect=«SET NAMES cp1251»
# ### skip-character-set-handshake
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
SELECT BE.ID as ID,BE.NAME as NAME,BE.PREVIEW_TEXT as PREVIEW_TEXT,BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1=1 AND ( ((((BE.IBLOCK_ID = '39')))) AND ((((BE.NAME LIKE '5277-9999')))) ) AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
Такое ощущение, что это какая то битрикс компонента выполняется.