Добрый день, в двух словах имеется сервер:
Intel(R) Xeon(R) CPU E5-1650 0 @ 3.20GHz
centos 6.4 3.2.13-grsec-xxxx-grs-ipv6-64
64G MEM + ssd 300G
MySql Server version: 5.5.30-30.1-log Percona Server (GPL), Release rel30.1, Revision 465
конфиг мускула
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 1
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 54G
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
Вывод top:
top - 07:16:14 up 5:31, 2 users, load average: 3.45, 2.69, 2.67
Tasks: 201 total, 1 running, 200 sleeping, 0 stopped, 0 zombie
Cpu(s): 97.2%us, 0.2%sy, 0.0%ni, 2.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 66008104k total, 9167572k used, 56840532k free, 105064k buffers
Swap: 14334972k total, 0k used, 14334972k free, 780548k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3779 mysql 20 0 61.2g 6.8g 6276 S
1160.2 10.7 1237:23 mysqld
3915 nginx 20 0 49848 6384 936 S 1.0 0.0 0:05.55 nginx
4016 apache 20 0 374m 21m 7360 S 0.7 0.0 0:14.40 php-fpm
4023 apache 20 0 386m 33m 7340 S 0.7 0.1 0:13.63 php-fpm
3996 apache 20 0 369m 16m 7236 S 0.3 0.0 0:09.78 php-fpm
4014 apache 20 0 372m 19m 7340 S 0.3 0.0 0:12.66 php-fpm
4015 apache 20 0 370m 17m 7360 S 0.3 0.0 0:21.56 php-fpm
4018 apache 20 0 370m 16m 7324 S 0.3 0.0 0:11.61 php-fpm
4111 apache 20 0 369m 16m 7300 S 0.3 0.0 0:10.51 php-fpm
6961 apache 20 0 370m 16m 7288 S 0.3 0.0 0:14.30 php-fpm
7008 apache 20 0 372m 17m 7364 S 0.3 0.0 0:08.75 php-fpm
7010 apache 20 0 369m 16m 7232 S 0.3 0.0 0:12.13 php-fpm
8686 apache 20 0 370m 16m 7116 S 0.3 0.0 0:06.81 php-fpm
8689 apache 20 0 370m 17m 7296 S 0.3 0.0 0:09.65 php-fpm
Вывод mytop:
MySQL on localhost (5.5.30-30.1-log) up 0+05:32:09 [07:17:36]
Queries: 2.9M qps: 154 Slow: 568.8k Se/In/Up/De(%): 20/00/00/00
qps now: 168 Slow qps: 31.6 Threads: 4 ( 4/ 36) 20/00/00/00
Key Efficiency: 100.0% Bps in/out: 6.9k/59.7k Now in/out: 7.4k/42.1k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
612306 root localhost 4xxxx 0 Query show full processlist
612849 4xxxx localhost 4xxxx 0 Query SELECT * FROM geolocation WHERE geolocation_domain= 'xxxxxxx.xxx' ORDER BY geolocation_datetime DESC
612850 4xxxx localhost 4xxxx 0 Query SELECT `backlinks_google` FROM backlinks WHERE backlinks_domain='nastyvideotube.com' ORDER BY backlinks_
612851 4xxxx localhost 4xxxx 0 Query SELECT * FROM alexa WHERE alexa_domain= 'xxxxxxx.xxxx' LIMIT 1
612846 4xxxx localhost 4xxxx 1 Query SELECT `backlinks_google` FROM backlinks WHERE backlinks_domain='xxxxxxx.com' ORDER BY backlinks_dat
slow-log включен, вся проблема в то что если взять запрос который по slow-log якобы выполняется 3-4сек и выполнить с консоли мускула то он отрабатывает за 0.5сек, куда копать ?
второй вопрос как просмотреть все запросы что в данный момент в базе SHOW FULL PROCESSLIST - показывает тоже самое что и mytop
query_cache выключен с ним ситуация еще хуже
