Форум Сайтостроение Администрирование серверов

Долго выполняются запросы

nikjda · 2015-12-28T17:55:54.0000000Z

Сервер: 1x3Ghz, RAM 2Gb, Disk 20Gb ОС: centos-6-x86_64 Работа сервера: Настройки my.cnf : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 max_connections = 1000 interactive_timeout=180 wait_timeout=180 key_buffer_size = 128M sort_buffer_size = 32M table_cache = 2048 query_cache_limit = 1M query_cache_size = 128M query_cache_type = 1 thread_cache_size = 16 table_open_cache = 1024 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Что выдает mysqltuner.pl : [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.73 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +CSV +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 115M (Tables: 48) [--] Data in InnoDB tables: 96K (Tables: 4) [!!] Total fragmented tables: 4 -------- Security Recommendations ------------------------------------------- [!!] User '@localhost' is an anonymous account. [!!] User '@vds5157.myarena.ru' is an anonymous account. [!!] User '@localhost' has no password set. [!!] User '@vds5157.myarena.ru' has no password set. [!!] User 'root@127.0.0.1' has no password set. [!!] User 'root@vds5157.myarena.ru' has no password set. [!!] User '@localhost' has user name as password. [!!] User '@vds5157.myarena.ru' has user name as password. [!!] There is no basic password file list! -------- Performance Metrics ------------------------------------------------- [--] Up for: 6m 10s (1K q [3.503 qps], 17 conn, TX: 1M, RX: 97K) [--] Reads / Writes: 92% / 8% [--] Binary logging is disabled [--] Total buffers: 282.0M global + 32.8M per thread (1000 max threads) [OK] Maximum reached memory usage: 380.2M (18.57% of installed RAM) [!!] Maximum possible memory usage: 32.3G (1612.89% of installed RAM) [OK] Slow queries: 0% (0/1K) [OK] Highest usage of available connections: 0% (3/1000) [!!] Aborted connections: 5.88% (1/17) [!!] Query cache efficiency: 14.1% (167 cached / 1K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts) [OK] Temporary tables created on disk: 0% (0 on disk / 2 total) [OK] Thread cache hit rate: 82% (3 created / 17 connections) [OK] Table cache hit rate: 72% (18 open / 25 opened) [OK] Open file limit used: 0% (33/5K) [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 18.2% (24M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/20.2M [!!] Read Key buffer hit rate: 94.7% (320 cached / 17 reads) [!!] Write Key buffer hit rate: 0.0% (79 cached / 79 writes) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 8.0M/96.0K [!!] InnoDB Used buffer: 4.69% (24 used/ 512 total) [OK] InnoDB Read buffer efficiency: 97.81% (761 hits/ 778 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 2 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Remove Anonymous User accounts - there are 2 anonymous accounts. Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Reduce or eliminate unclosed connections and network issues Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_limit (> 1M, or use smaller result sets) Никогда ранее с этим не работал (оптимизация сервера базы данных)

419

netwind

30 декабря 2015, 15:14

#11

nikjda:
До того момента, как база данных располагалась на веб-хостинге, то никаких проблем не было с запросами. Это случилось после переезда на VDS сервер. Из этого можно выявить причину - не настроен сервер

Не пойму в чем логика? shared мог быть просто мощнее.

Кстати, на новых версиях mysql уже давно можно дробное значение прописывать . То есть long_query_time = 0.3 вполне будет работать. Сделайте, если хотите еще больше информации собрать.

А вы захотите.

Кнопка вызова админа ()

Mysql сильно нагружает сервер. Как извлечь квадратный корень Фундаментальная проблема материалистических теорий

Open AI тестирует память для ChatGPT

Переиграть и победить: как анализировать конкурентов для продвижения сайта

Долго выполняются запросы