Спасибо за подробную помощь, сделал вывод mysqltuner - Администрирование серверов

Проблема с MySQL

Продюсер · 2013-01-21T05:04:37.0000000Z

После переустановки системы столкнулся с проблемой, что мускул падает переодически в разное время( закономерности не выявил). Поднять удается только рестартом сервиса mysqld. Пробовал различные конфиги my.ini, все безуспешно. Вот лог(за несколько часов до ): ******************* 130116 20:30:12 InnoDB: Waiting for the background threads to start 130116 20:30:13 InnoDB: 1.1.8 started; log sequence number 1726150 /usr/libexec/mysqld: File './mysql-bin.000013' not found (Errcode: 2) 130116 20:30:13 [ERROR] Failed to open log (file './mysql-bin.000013', errno 2) 130116 20:30:13 [ERROR] Could not open log file 130116 20:30:13 [ERROR] Can't init tc log 130116 20:30:13 [ERROR] Aborting 130116 20:30:13 InnoDB: Starting shutdown... 130116 20:30:14 InnoDB: Shutdown completed; log sequence number 1726150 130116 20:30:14 [Note] /usr/libexec/mysqld: Shutdown complete 130116 20:30:14 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 130116 20:31:01 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130116 20:31:01 [Note] Plugin 'FEDERATED' is disabled. 130116 20:31:01 InnoDB: The InnoDB memory heap is disabled 130116 20:31:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130116 20:31:01 InnoDB: Compressed tables use zlib 1.2.3 130116 20:31:01 InnoDB: Using Linux native AIO 130116 20:31:01 InnoDB: Initializing buffer pool, size = 128.0M 130116 20:31:01 InnoDB: Completed initialization of buffer pool 130116 20:31:01 InnoDB: highest supported file format is Barracuda. 130116 20:31:01 InnoDB: Waiting for the background threads to start 130116 20:31:02 InnoDB: 1.1.8 started; log sequence number 1726150 /usr/libexec/mysqld: File './mysql-bin.000013' not found (Errcode: 2) 130116 20:31:02 [ERROR] Failed to open log (file './mysql-bin.000013', errno 2) 130116 20:31:02 [ERROR] Could not open log file 130116 20:31:02 [ERROR] Can't init tc log 130116 20:31:02 [ERROR] Aborting 130116 20:31:02 InnoDB: Starting shutdown... 130116 20:31:03 InnoDB: Shutdown completed; log sequence number 1726150 130116 20:31:03 [Note] /usr/libexec/mysqld: Shutdown complete 130116 20:31:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 130116 20:34:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130116 20:34:21 [Note] Plugin 'FEDERATED' is disabled. 130116 20:34:21 InnoDB: The InnoDB memory heap is disabled 130116 20:34:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130116 20:34:21 InnoDB: Compressed tables use zlib 1.2.3 130116 20:34:21 InnoDB: Using Linux native AIO 130116 20:34:21 InnoDB: Initializing buffer pool, size = 128.0M 130116 20:34:21 InnoDB: Completed initialization of buffer pool 130116 20:34:21 InnoDB: highest supported file format is Barracuda. 130116 20:34:21 InnoDB: Waiting for the background threads to start 130116 20:34:22 InnoDB: 1.1.8 started; log sequence number 1726150 /usr/libexec/mysqld: File './mysql-bin.000013' not found (Errcode: 2) 130116 20:34:22 [ERROR] Failed to open log (file './mysql-bin.000013', errno 2) 130116 20:34:22 [ERROR] Could not open log file 130116 20:34:22 [ERROR] Can't init tc log 130116 20:34:22 [ERROR] Aborting 130116 20:34:22 InnoDB: Starting shutdown... 130116 20:34:23 InnoDB: Shutdown completed; log sequence number 1726150 130116 20:34:23 [Note] /usr/libexec/mysqld: Shutdown complete 130116 20:34:23 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 130116 20:38:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130116 20:38:14 [Warning] You need to use --log-bin to make --binlog-format work. 130116 20:38:14 [Note] Plugin 'FEDERATED' is disabled. 130116 20:38:14 InnoDB: The InnoDB memory heap is disabled 130116 20:38:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130116 20:38:14 InnoDB: Compressed tables use zlib 1.2.3 130116 20:38:14 InnoDB: Using Linux native AIO 130116 20:38:14 InnoDB: Initializing buffer pool, size = 128.0M 130116 20:38:14 InnoDB: Completed initialization of buffer pool 130116 20:38:14 InnoDB: highest supported file format is Barracuda. 130116 20:38:14 InnoDB: Waiting for the background threads to start 130116 20:38:15 InnoDB: 1.1.8 started; log sequence number 1726150 130116 20:38:15 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130116 20:38:15 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 130116 20:38:15 [Note] Server socket created on IP: '0.0.0.0'. 130116 20:38:15 [Note] Event Scheduler: Loaded 0 events 130116 20:38:15 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.28' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 130116 20:42:22 [ERROR] Got an error from thread_id=291, /builddir/build/BUILD/mysql-5.5.28/storage/myisam/ha_myisam.cc:912 130116 20:42:22 [ERROR] MySQL thread id 291, OS thread handle 0x7f93e8c70700, query id 3606 localhost dom Checking table CHECK TABLE `post` 130116 23:37:11 [Warning] IP address '114.80.154.66' could not be resolved: Name or service not known 130117 0:12:51 [Warning] IP address '116.247.118.204' could not be resolved: Name or service not known 130117 17:08:22 mysqld_safe Number of processes running now: 0 130117 17:08:22 mysqld_safe mysqld restarted 130117 20:53:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130117 20:53:31 [Warning] You need to use --log-bin to make --binlog-format work. 130117 20:53:31 [Note] Plugin 'FEDERATED' is disabled. 130117 20:53:31 InnoDB: The InnoDB memory heap is disabled 130117 20:53:31 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130117 20:53:31 InnoDB: Compressed tables use zlib 1.2.3 130117 20:53:31 InnoDB: Using Linux native AIO 130117 20:53:31 InnoDB: Initializing buffer pool, size = 128.0M 130117 20:53:31 InnoDB: Completed initialization of buffer pool 130117 20:53:31 InnoDB: highest supported file format is Barracuda. 130117 20:53:31 InnoDB: Waiting for the background threads to start 130117 20:53:32 InnoDB: 1.1.8 started; log sequence number 1726150 130117 20:53:32 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130117 20:53:32 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 130117 20:53:32 [Note] Server socket created on IP: '0.0.0.0'. 130117 20:53:32 [Note] Event Scheduler: Loaded 0 events 130117 20:53:32 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.28' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 130117 22:12:45 [Warning] IP address '218.93.205.46' could not be resolved: Name or service not known 130118 22:10:33 [Warning] IP address '210.102.55.141' could not be resolved: Name or service not known 130119 2:48:35 [Warning] IP address '219.136.252.240' could not be resolved: Name or service not known 130120 3:48:43 [Warning] IP address '117.28.254.69' could not be resolved: Name or service not known 130121 00:53:15 mysqld_safe Number of processes running now: 0 130121 00:53:15 mysqld_safe mysqld restarted 130121 0:53:15 [Warning] You need to use --log-bin to make --binlog-format work. 130121 0:53:15 [Note] Plugin 'FEDERATED' is disabled. 130121 0:53:15 InnoDB: The InnoDB memory heap is disabled 130121 0:53:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130121 0:53:15 InnoDB: Compressed tables use zlib 1.2.3 130121 0:53:15 InnoDB: Using Linux native AIO 130121 0:53:15 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 130121 0:53:15 InnoDB: Completed initialization of buffer pool 130121 0:53:15 InnoDB: Fatal error: cannot allocate memory for the buffer pool 130121 0:53:15 [ERROR] Plugin 'InnoDB' init function returned error. 130121 0:53:15 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130121 0:53:15 [ERROR] Unknown/unsupported storage engine: InnoDB 130121 0:53:15 [ERROR] Aborting 130121 0:53:15 [Note] /usr/libexec/mysqld: Shutdown complete 130121 00:53:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 130121 07:06:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130121 7:06:40 [Warning] You need to use --log-bin to make --binlog-format work. 130121 7:06:40 [Note] Plugin 'FEDERATED' is disabled. 130121 7:06:40 InnoDB: The InnoDB memory heap is disabled 130121 7:06:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130121 7:06:40 InnoDB: Compressed tables use zlib 1.2.3 130121 7:06:40 InnoDB: Using Linux native AIO 130121 7:06:40 InnoDB: Initializing buffer pool, size = 128.0M 130121 7:06:40 InnoDB: Completed initialization of buffer pool 130121 7:06:40 InnoDB: highest supported file format is Barracuda. 130121 7:06:40 InnoDB: Waiting for the background threads to start 130121 7:06:41 InnoDB: 1.1.8 started; log sequence number 1726150 130121 7:06:41 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130121 7:06:41 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 130121 7:06:41 [Note] Server socket created on IP: '0.0.0.0'. 130121 7:06:41 [Note] Event Scheduler: Loaded 0 events 130121 7:06:41 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.28' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) Как видно все грохнулось в 00:53 и только утром я его перезапустил. Подскажите куда копать? какая еще информация нужна для выяснения проблемы?

36

Продюсер

21 января 2013, 16:36

#21

Эти строчки добавить в my.сnf ?

Сделал...через час вывод покажу.

---------- Добавлено 21.01.2013 в 21:51 ----------

Спустя час:

[root@92 ~]# ./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 362M (Tables: 1063)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 8)
[!!] Total fragmented tables: 10

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 4m 31s (20K q [5.200 qps], 1K conn, TX: 542M, RX: 4M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 96.0M global + 2.5M per thread (40 max threads)
[OK] Maximum possible memory usage: 196.0M (38% of installed RAM)
[OK] Slow queries: 0% (0/20K)
[OK] Highest usage of available connections: 10% (4/40)
[OK] Key buffer size / total MyISAM indexes: 32.0M/131.5M
[OK] Key buffer hit rate: 97.7% (259K cached / 6K reads)
[OK] Query cache efficiency: 53.1% (6K cached / 13K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 18% (33 on disk / 181 total)
[OK] Thread cache hit rate: 99% (4 created / 1K connections)
[OK] Table cache hit rate: 93% (544 open / 583 opened)
[OK] Open file limit used: 24% (1K/4K)
[OK] Table locks acquired immediately: 99% (12K immediate / 12K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries

A

130

admak

21 января 2013, 19:22

#22

уже значительно лучше, давайте подождем до утра - пусть соберется статистика.

.............

MT

49

MarcusTven

21 января 2013, 19:41

#23

1 часа всегда мало, ребят.

Вообще статистика и вывод mysqltuner'a адекватный - это полный цикл дня (24 часа). Ибо если запускать это под ночь, то статистика будет кривая и днем вылезут косяки.

marcus@cluster:~$

36

Продюсер

22 января 2013, 03:10

#24

Вот утренние анализы:

[root@92 ~]# ./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 362M (Tables: 1063)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 8)
[!!] Total fragmented tables: 29

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 22m 20s (204K q [5.466 qps], 17K conn, TX: 5B, RX: 45M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 96.0M global + 2.5M per thread (40 max threads)
[OK] Maximum possible memory usage: 196.0M (38% of installed RAM)
[OK] Slow queries: 0% (0/204K)
[OK] Highest usage of available connections: 22% (9/40)
[OK] Key buffer size / total MyISAM indexes: 32.0M/131.7M
[OK] Key buffer hit rate: 99.5% (2M cached / 12K reads)
[OK] Query cache efficiency: 54.9% (71K cached / 130K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 15K sorts)
[OK] Temporary tables created on disk: 12% (156 on disk / 1K total)
[OK] Thread cache hit rate: 99% (9 created / 17K connections)
[OK] Table cache hit rate: 82% (1K open / 1K opened)
[OK] Open file limit used: 52% (2K/4K)
[OK] Table locks acquired immediately: 99% (127K immediate / 127K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries

[root@92 ~]#

A

130

admak

22 января 2013, 05:50

#25

полет нормальный, посмотрим как он дневную нагрузку перенесет.

36

Продюсер

22 января 2013, 15:18

#26

Вечер, вот так:

[root@92 ~]# ./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 363M (Tables: 1063)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 8)
[!!] Total fragmented tables: 32

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 22h 31m 46s (565K q [6.971 qps], 45K conn, TX: 15B, RX: 134M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 96.0M global + 2.5M per thread (40 max threads)
[OK] Maximum possible memory usage: 196.0M (38% of installed RAM)
[OK] Slow queries: 0% (1/565K)
[OK] Highest usage of available connections: 22% (9/40)
[OK] Key buffer size / total MyISAM indexes: 32.0M/132.0M
[OK] Key buffer hit rate: 99.7% (6M cached / 17K reads)
[OK] Query cache efficiency: 55.5% (203K cached / 366K selects)
[!!] Query cache prunes per day: 17436
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 47K sorts)
[OK] Temporary tables created on disk: 15% (764 on disk / 4K total)
[OK] Thread cache hit rate: 99% (9 created / 45K connections)
[OK] Table cache hit rate: 64% (1K open / 1K opened)
[OK] Open file limit used: 53% (2K/4K)
[OK] Table locks acquired immediately: 99% (381K immediate / 381K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_size (> 48M)

[root@92 ~]#

A

130

admak

22 января 2013, 17:57

#27

замечательно, немного уменьшим расход памяти на коннекты, за сутки было максимум 9 коннектов и увеличим память на кеш запросов т.к. его не хватает.


max_connections = 30
query_cache_size = 72M

36

Продюсер

22 января 2013, 19:08

#28

Спасибо за подробную помощь, сделал.

112

doctorpc

22 января 2013, 22:13

#29

В свое время очень помог небольшой скрипт на python, который показывает расход памяти разными службами.

Может случиться, что другая служба использует всю оперативную память в том числе те 196.0Mb, которые у Вас сейчас отданы Mysql.

Скрипт прикреплен. Требует установленный python на сервере.

apt-get install python

Дальше скрипт должен быть исполняемым и запускается командой

python ps_mem.py

Также полезным для анализа используемой памяти может быть команда

free -m

[ATTACH]118944[/ATTACH]

zip ps_mem.zip

Fastvps.ru - Благодарности! / Сколько ресурсов может кушать Качественный хостинг и без

36

Продюсер

23 января 2013, 15:36

#30

Еще спустя сутки:

[root@92 ~]# ./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 364M (Tables: 1063)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1009K (Tables: 8)
[!!] Total fragmented tables: 33

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 35m 5s (674K q [9.103 qps], 53K conn, TX: 18B, RX: 164M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 120.0M global + 2.5M per thread (30 max threads)
[OK] Maximum possible memory usage: 195.0M (38% of installed RAM)
[OK] Slow queries: 0% (0/674K)
[OK] Highest usage of available connections: 46% (14/30)
[OK] Key buffer size / total MyISAM indexes: 32.0M/133.0M
[OK] Key buffer hit rate: 99.7% (8M cached / 22K reads)
[OK] Query cache efficiency: 57.2% (257K cached / 450K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 67K sorts)
[OK] Temporary tables created on disk: 25% (1K on disk / 6K total)
[OK] Thread cache hit rate: 99% (33 created / 53K connections)
[OK] Table cache hit rate: 51% (586 open / 1K opened)
[OK] Open file limit used: 25% (1K/4K)
[OK] Table locks acquired immediately: 99% (475K immediate / 475K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries

Google: E-E-A-T не является фактором ранжирования

Что такое Power BI и зачем это нужно бизнесу

Проблема с MySQL