Сильно тормозит сайт из-за Mysql. прошу совета знающих людей

ch33ch

Новичок
Привет всем, надеюсь на помощь профессионалов, многого еще не знаю, но самостоятельно решить проблему не получается, пытаюсь долгое время разобраться в медленной работе сайта, а именно: долгом ответе сервера из-за работы Mysql, /usr/sbin/mysqld грузит оперативной памяти 30-50%, сервер отвечает по тестам 6-15 секунд, после грузится остальное уже быстро. (см вложение)
Имеется виртуальный сервер на Xeon 2*2.50GHz, 1 гб ОЗУ, стоит ubuntu 16.04.3, PHP 7.0.8, MySQL 5.7.16. Сайт на wordpress в режиме FastCGI (Nginx + PHP-FPM). Процессор грузится не более чем на 10% в среднем, посетителей в день 150-400. Грешу на не правильную настройку Mysql, могу ошибаться конечно, поэтому отправляю все конфиги, где я изменял значения по умолчанию:

Код:
bind-address        127.0.0.1
expire-logs-days    10
innodb-buffer-pool-size    270мб
innodb-flush-method    O_DIRECT
innodb-log-file-size    68мб
interactive-timeout    300
key-buffer-size        16мб
local-infile        FALSE
max-allowed-packet    16мб
max-binlog-size        100мб
max-connections         151
max-connect-errors    1000
max-join-size           1000000
max_heap_table_size     96мб
myisam-recover-options    BACKUP
pid-file        /var/run/mysqld/mysqld.pid
query-cache-size    32мб
read_buffer_size        128кб
symbolic-links        FALSE
table-definition-cache    2000
tmp_table_size          96мб
thread-cache-size    8
thread-stack        196608
wait-timeout        300

Код:
[!!] Overall possible memory usage with other process exceeded memory
[!!] Temporary tables created on disk: 46% (6K on disk / 14K total)
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[!!] Read Key buffer hit rate: 50.0% (12 cached / 6 reads)
[!!] InnoDB Write Log efficiency: 89% (32712 hits/ 36753 total)
When making adjustments, make tmp_table_size/max_heap_table_size equal   
    Reduce your SELECT DISTINCT queries which have no LIMIT clause           
Variables to adjust:                                                         
    tmp_table_size (> 16M)                                                   
    max_heap_table_size (> 6M)

Код:
fastcgi_cache_path /etc/nginx/vhosts-resources/NAME.ru levels=2 keys_zone=NAME:100m max_size=256m inactive=60m;
 
server {
server_name NAME.ru www.NAME.ru;
charset UTF-8;
index index.html index.php;
disable_symlinks if_not_owner from=$root_path;
include /etc/nginx/vhosts-includes/*.conf;
include /etc/nginx/vhosts-resources/NAME.ru/*.conf;
access_log /var/www/httpd-logs/NAME.ru.access.log;
error_log /var/www/httpd-logs/NAME.ru.error.log notice;
ssi on;
set $root_path /var/www/www-root/data/www/NAME.ru;
root $root_path;
rewrite ^/sitemap(-+([a-zA-Z0-9_-]+))?\.xml$ "/index.php?xml_sitemap=params=$2" last;
rewrite ^/sitemap(-+([a-zA-Z0-9_-]+))?\.xml\.gz$ "/index.php?xml_sitemap=params=$2;zip=true" last;
rewrite ^/sitemap(-+([a-zA-Z0-9_-]+))?\.html$ "/index.php?xml_sitemap=params=$2;html=true" last;
rewrite ^/sitemap(-+([a-zA-Z0-9_-]+))?\.html.gz$ "/index.php?xml_sitemap=params=$2;html=true;zip=true" last;
 
if ($http_cookie ~* "comment_author_|wordpress_(?!test_cookie)|wp-postpass_" ) {
set $do_not_cache 1;
}
fastcgi_cache_bypass $do_not_cache;
fastcgi_no_cache $do_not_cache;
fastcgi_pass_header Cookie;
fastcgi_cache_use_stale error timeout invalid_header http_500;
fastcgi_cache NAME;
fastcgi_cache_key "$request_method|$http_if_modified_since|$http_if_none_match|$host|$request_uri";
fastcgi_cache_valid 200 301 302 304 8h;
fastcgi_cache_valid 404 8h;
fastcgi_ignore_headers Cache-Control Expires Set-Cookie;
 
location ~* ^.+.(jpg|jpeg|gif|png|ico|css|pdf|ppt|txt|bmp|rtf|js|zip|rar)$ {
   root /var/www/www-root/data/www/NAME.ru;
   expires 10d;
   access_log off;
   log_not_found off;
    }
 
location @php {
    fastcgi_index index.php;
    fastcgi_param PHP_ADMIN_VALUE "sendmail_path = /usr/sbin/sendmail -t -i -f [email protected]";
    fastcgi_pass unix:/var/www/php-fpm/www-root.sock;
    fastcgi_split_path_info ^((?U).+\.ph(?:p\d*|tml))(/?.+)$;
    try_files $uri =404;
     include fastcgi_params;
    }
 
location ~ \.php$ {
   include /etc/nginx/fastcgi_params;
   fastcgi_index index.php;
   fastcgi_ignore_client_abort off;
   fastcgi_connect_timeout 60;
   fastcgi_send_timeout 180;
   fastcgi_read_timeout 180;
   fastcgi_buffer_size 128k;
   fastcgi_buffers 4 256k;
   fastcgi_busy_buffers_size 256k;
   fastcgi_temp_file_write_size 256k;
   fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
   try_files $uri @wordpress;
   fastcgi_pass unix:/var/www/php-fpm/www-root.sock;
   }
     
location @wordpress {
   include /etc/nginx/fastcgi_params;
   fastcgi_param SCRIPT_NAME /index.php;
   fastcgi_param SCRIPT_FILENAME $document_root/index.php;
   fastcgi_index index.php;
   fastcgi_pass unix:/var/www/php-fpm/www-root.sock;
   }

location ~ ^/(wp-admin/.*\.php|wp-login\.php|wp-register\.php|(feed|comment/feed)(/.*)?)$ {
   try_files $uri @wordpress;
   set $do_not_cache 1;
   fastcgi_cache_bypass 1;
   fastcgi_no_cache 1;
   fastcgi_pass unix:/var/www/php-fpm/ch33ch.sock; #не забудьте поменять
   fastcgi_index index.php;
   include /etc/nginx/fastcgi_params;
   fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
   }
 
location / {
   try_files $uri $uri/ /index.php?$args;
   location ~ [^/]\.ph(p\d*|tml)$ {
   try_files /does_not_exists @php;
   }
    }
 
 gzip on;
 gzip_disable "msie6";
 gzip_min_length 1000;
 gzip_proxied     any;
 gzip_comp_level 7;
 gzip_buffers 16 8k;
 gzip_types text/plain text/css application/json application/x-javascript text/xml application/xml application/xml+rss text/javascript application/javascript;
 expires 3d;
 server_tokens off;
 open_file_cache max=65000 inactive=200s;
 open_file_cache_valid 300s;
 open_file_cache_min_uses 2;
 open_file_cache_errors on;
 listen IP:80 default_server;
}

Код:
user www-data;
worker_processes  2;

error_log  /var/log/nginx/error.log warn;
pid        /var/run/nginx.pid;


events {
   worker_connections  1024;
}


http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  /var/log/nginx/access.log  main;

    sendfile        on;
    tcp_nopush     on;

    keepalive_timeout  65;

    gzip on;
    gzip_disable "msie6";
    gzip_vary on;
    gzip_min_length 1000;
    gzip_comp_level 7;
    gzip_buffers 16 8k;
    gzip_static on;
    gzip_proxied any;
    gzip_types
       text/plain
       text/css
       text/js
       text/xml
       text/javascript
       application/javascript
       application/x-javascript
       application/json
       application/xml
       application/xml+rss;
       server_tokens off;

    include /etc/nginx/conf.d/*.conf;
    include /etc/nginx/vhosts/*/*.conf;
    client_max_body_size 128m;
    server {
        server_name localhost;
    disable_symlinks if_not_owner;
    listen 80;
    include /etc/nginx/vhosts-includes/*.conf;
    location @fallback {
        error_log /dev/null crit;
        proxy_pass http://127.0.0.1:8080;
        proxy_redirect http://127.0.0.1:8080 /;
        proxy_set_header Host $host;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        access_log off ;
    }
    }
}

Код:
max_execution_time        30
max_file_uploads        20
max_input_nesting_level        64
max_input_time            60
max_input_vars            1000
memory_limit            128M
output_buffering        4096
post_max_size            200M
realpath_cache_size        16K
realpath_cache_ttl        120
upload_max_filesize        200M
opcache.enable            On
opcache.file_cache_only        0
opcache.file_update_protection    2
opcache.force_restart_timeout    180
opcache.inherited_hack        On
opcache.interned_strings_buffer    4
opcache.lockfile_path    /    tmp
opcache.max_accelerated_files    2000
opcache.max_file_size        0
opcache.max_wasted_percentage    5
opcache.memory_consumption    64
memcache.chunk_size        32768
memcache.compress_threshold    20000
memcache.lock_timeout        15
session.auto_start        0
session.cache_expire        180
session.cache_limiter        nocache
session.save_handler        files
session.save_path        /var/lib/php/sessions
session.serialize_handler    php

Много менял параметры mysql, но казалось становилось хуже и хуже, после тюнинга mysql также. Dns 85 милисекунд отвечает. Картина такая, что жму обновить страницу на сайте и сейчас 10-20 он думает и потом обновляет, сервис tools.pingdom.com выдал такое(см вложение). 22 секунды думает перед тем как выдать ответ, база весит 130 мб, это интернет-магазин, у 12200 товаров c десяток характеристик, которых он видно долго ищет в базе, на сайте около 13000 страниц, стили фото и скрипты грузятся за 2 секунды после ответа сервера.
По сайту в error.log постоянно пишет "8202 using uninitialized "do_not_cache" variable, client: ....."
Пробовал другой сайт закинуть на этот сервер, небольшой, база 20 мб, 50 страниц, тоже wordpress, грузится до 0.5 секунд. Не понимаю как нужно сократить время запросов. Жду любых советов от Вас и поправок, что мне нужно еще прислать для более полного понимания работы? подскажите куда мне копать? Спасибо за любую помощь заранее
 

Вложения

antson

Новичок
Партнер клуба
судя по странице там у тебя 10 запросов вида select count() group by
подбор шин в вордпрес не входит. Думаю, надо пинать того кто писал этот модуль.
Если делал сам, то проверить наличие индексов . Потом внимательно смотреть explain запросов.

на сколько помню справочник типоразмеров 200-500 тысяч записей.
товарная база по шинам 2-10 тысяч (в наличие и под заказ)

далее практикуются 2 подхода
а) классика 3 нормальная форма
б) денормализация до 1ой дублированием полей для поиска в таблице товаров.

в случае а при отсутствии индексов по ключу связи эксплайн покажет фулскан по 5млн записей ...
ну остальное можно гадать до бесконечности не видя структуры и эксплайнов
 
Последнее редактирование:

ch33ch

Новичок
Отключил count в подборе, время загрузки с 22 сек до 6 упало, поставил временные таблицы 100 мб, max-join-size 1000000, innodb-log-file-size до 128 мб, убрал fastcgi_cache_bypass и грузится стал до 2.5 сек в среднем. Но это точно не решение проблемы.
По индексам... не знаю как правильно делать и вообще как делать (это для меня новое). Подскажите с чего мне начать?
 

antson

Новичок
Партнер клуба
@ch33ch, запрос был по объединению таблиц товары и справочник_типоразмеров ? я угадал ? объяснять дальше долго . по хорошему нужно целый учебник рассказать.
но самое очевидное проверить использование на условии объединения таблиц.
ахз как вы сделали. ввели поля типа инт у товара или тупо по названию соединяете ? см. выше. нужна структура таблиц и вывод
explain select ...

что оптимизировать можно собрать включив sql query slow log
 

antson

Новичок
Партнер клуба
ну или в раздел работа, чтобы Вам это допили.. 100-200 баксов должно хватить
 

ch33ch

Новичок
>запрос был по объединению таблиц товары и справочник_типоразмеров ?
была такая мысль, но нет знаний, сейчас просто товары с параметрами, по которым тупо идет выборка select'ом, объединения никакого нет. Увидел еще какие много времени выполняются, доходит даже до 1.5-2.5 секунд.
Про скорость запросов sql query slow log посмотрю, попробую что-нибудь упростить или убрать.
>"проверить использование на условии объединения таблиц" и "структура таблиц и вывод explain select"
пойду читать книжки... Еще момент - нет ли у меня в настройках nginx или mysql каких нибудь грубых ошибок? (p.s. земля круглая, я с Грязей)
 
Сверху