LINUX.ORG.RU

Гуру! Помогите настроить сервер!

 , , ,


0

3

Здравствуйте, о глубокоуважаемые форумчане! Помогите нубу настроить сервер. Сам я ничего не знаю, по русски говоря - ни в зуб ногой. Сайт тормозит, тормозит серьезно, время отклика составляет до 3 с ((((

Напишите, что сделать, я сделаю и выложу результат... Я более чем уверен, что ваши советы помогут !

VPS Six cores 24 GB, debian 9, apache

---------------------- My.cnf ---------

  • !includedir /etc/mysql/conf.d/
  • !includedir /etc/mysql/mariadb.conf.d/
  • [mysqld]
  • slow_query_log = /var/log/mysql/mysql-slow.log
  • long_query_time = 1
  • key_buffer_size = 8192M
  • sort_buffer_size = 64M
  • query_cache_type = 1
  • query_cache_size = 256M
  • query_cache_limit = 4M
  • table_open_cache = 2048
  • query_cache_limit = 32M
  • thread_cache_size = 16
  • max_heap_table_size = 128M
  • tmp_table_size = 128M

---------------------- Mysqltuner ---------

  • [--] Skipped version check for MySQLTuner script
  • [OK] Logged in using credentials from debian maintenance account.
  • [OK] Currently running supported MySQL version 10.1.26-MariaDB-0+deb9u1
  • [OK] Operating on 64-bit architecture
  • -------- Log file Recommendations ------------------------------------------------------------------
  • [--] Log file: /var/log/mysql/error.log(0B)
  • [OK] Log file /var/log/mysql/error.log exists
  • [OK] Log file /var/log/mysql/error.log is readable.
  • [!!] Log file /var/log/mysql/error.log is empty
  • [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
  • [OK] /var/log/mysql/error.log doesn't contain any warning.
  • [OK] /var/log/mysql/error.log doesn't contain any error.
  • [--] 0 start(s) detected in /var/log/mysql/error.log
  • [--] 0 shutdown(s) detected in /var/log/mysql/error.log
  • -------- Storage Engine Statistics -----------------------------------------------------------------
  • [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
  • [--] Data in MyISAM tables: 530M (Tables: 32)
  • [--] Data in InnoDB tables: 416K (Tables: 20)
  • [OK] Total fragmented tables: 0
  • -------- Security Recommendations ------------------------------------------------------------------
  • [OK] There are no anonymous accounts for any database users
  • [OK] All database users have passwords assigned
  • [--] There are 612 basic passwords in the list.
  • -------- CVE Security Recommendations --------------------------------------------------------------
  • [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
  • -------- Performance Metrics -----------------------------------------------------------------------
  • [--] Up for: 15h 33m 58s (722K q [12.888 qps], 27K conn, TX: 21G, RX: 1G)
  • [--] Reads / Writes: 97% / 3%
  • [--] Binary logging is disabled
  • [--] Physical Memory : 23.6G
  • [--] Max MySQL memory : 18.2G
  • [--] Other process memory: 171.9M
  • [--] Total buffers: 8.6G global + 64.8M per thread (151 max threads)
  • [--] P_S Max memory usage: 0B
  • [--] Galera GCache Max memory usage: 0B
  • [OK] Maximum reached memory usage: 10.4G (44.25% of installed RAM)
  • [OK] Maximum possible memory usage: 18.2G (77.31% of installed RAM)
  • [OK] Overall possible memory usage with other process is compatible with memory available
  • [OK] Slow queries: 0% (0/722K)
  • [OK] Highest usage of available connections: 18% (28/151)
  • [OK] Aborted connections: 0.96% (262/27159)
  • [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
  • [!!] Query cache may be disabled by default due to mutex contention.
  • [OK] Query cache efficiency: 26.3% (223K cached / 850K selects)
  • [!!] Query cache prunes per day: 141704
  • [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 335K sorts)
  • [OK] No joins without indexes
  • [!!] Temporary tables created on disk: 36% (72K on disk / 197K total)
  • [OK] Thread cache hit rate: 99% (45 created / 27K connections)
  • [OK] Table cache hit rate: 96% (149 open / 155 opened)
  • [OK] Open file limit used: 1% (183/16K)
  • [OK] Table locks acquired immediately: 99% (713K immediate / 715K locks)
  • -------- Performance schema ------------------------------------------------------------------------
  • [--] Performance schema is disabled.
  • [--] Memory used by P_S: 0B
  • [--] Sys schema isn't installed.
  • -------- ThreadPool Metrics ------------------------------------------------------------------------
  • [--] ThreadPool stat is enabled.
  • [--] Thread Pool Size: 6 thread(s).
  • [--] Using default value is good enough for your version (10.1.26-MariaDB-0+deb9u1)
  • -------- MyISAM Metrics ----------------------------------------------------------------------------
  • [!!] Key buffer used: 19.0% (1B used / 8B cache)
  • [OK] Key buffer size / total MyISAM indexes: 8.0G/137.2M
  • [OK] Read Key buffer hit rate: 100.0% (1B cached / 64K reads)
  • [OK] Write Key buffer hit rate: 98.2% (40K cached / 39K writes)
  • -------- InnoDB Metrics ----------------------------------------------------------------------------
  • [--] InnoDB is enabled.
  • [--] InnoDB Thread Concurrency: 0
  • [OK] InnoDB File per table is activated
  • [OK] InnoDB buffer pool / data size: 128.0M/416.0K
  • [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
  • [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
  • [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
  • [!!] InnoDB Read buffer efficiency: 85.41% (1896 hits/ 2220 total)
  • [!!] InnoDB Write Log efficiency: 81.54% (53 hits/ 65 total)
  • [OK] InnoDB log waits: 0.00% (0 waits / 12 writes)
  • -------- AriaDB Metrics ----------------------------------------------------------------------------
  • [--] AriaDB is enabled.
  • [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
  • [OK] Aria pagecache hit rate: 97.0% (153M cached / 4M reads)
  • -------- TokuDB Metrics ----------------------------------------------------------------------------
  • [--] TokuDB is disabled.
  • -------- XtraDB Metrics ----------------------------------------------------------------------------
  • [--] XtraDB is disabled.
  • -------- RocksDB Metrics ---------------------------------------------------------------------------
  • [--] RocksDB is disabled.
  • -------- Spider Metrics ----------------------------------------------------------------------------
  • [--] Spider is disabled.
  • -------- Connect Metrics ---------------------------------------------------------------------------
  • [--] Connect is disabled.
  • -------- Galera Metrics ----------------------------------------------------------------------------
  • [--] Galera is disabled.
  • -------- Replication Metrics -----------------------------------------------------------------------
  • [--] Galera Synchronous replication: NO
  • [--] No replication slave(s) for this server.
  • [--] Binlog format: STATEMENT
  • [--] XA support enabled: ON
  • [--] Semi synchronous replication Master: Not Activated
  • [--] Semi synchronous replication Slave: Not Activated
  • [--] This is a standalone server
  • -------- Recommendations ---------------------------------------------------------------------------
  • General recommendations:
  • MySQL was started within the last 24 hours - recommendations may be inaccurate
  • Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
  • Increasing the query_cache size over 128M may reduce performance
  • When making adjustments, make tmp_table_size/max_heap_table_size equal
  • Reduce your SELECT DISTINCT queries which have no LIMIT clause
  • Performance schema should be activated for better diagnostics
  • Consider installing Sys schema from https://github.com/mysql/mysql-sys
  • Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
  • Variables to adjust:
  • query_cache_size (=0)
  • query_cache_type (=0)
  • query_cache_size (> 256M) [see warning above]
  • tmp_table_size (> 128M)
  • max_heap_table_size (> 128M)
  • performance_schema = ON enable PFS
  • innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
  • innodb_buffer_pool_instances (=1)

----------------

Кол-во записей в базе около 1 млн, таблиц 32.

----------------

Помогите, пожалуйста, советом, что нужно изменить и что проверить ?



Последнее исправление: Booble (всего исправлений: 7)

нужно включить и смотреть лог медленных запросов. 100% пхпшники создавали таблицы и писали запросы по статьям с хабра.

redixin ★★★★
()

Для начала из поста не очевидно откуда уверенность в том что узкое место это mysql. Копрокод может тормозить и вовсе не обращаясь к БД

MrClon ★★★★★
()

цепляйся к процессу php-fpm strace'ом и смотри где конкретно тормозит

ну и slow queries, конечно (но могут ничо не показать, если один запрос выполняется за 0,001с, а на генерацию странички таких запросов 2-3k)

slowpony ★★★★★
()
Ответ на: комментарий от MrClon

Тормоза начались, при увеличении кол-ва записей, и после того как я полез в настройки my.cnf

Booble
() автор топика
Ответ на: комментарий от Booble

Покрути long_query_time там вроде 10 по умолчанию. Поставь 0.1

Про то что пхп тормозит это и так понятно, но 3 секунды это даже для пхп много. Так тормозить может разве что руби

redixin ★★★★
()
Последнее исправление: redixin (всего исправлений: 1)
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.