Cacti log sais: SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect

В логах системы мониторинга Cacti стали появляться ошибки:

04/11/2013 09:47:59 AM — SYSTEM STATS: Time:59.6091 Method:spine Processes:4 Threads:24 Hosts:69 HostsPerProcess:18 DataSources:8422 RRDsProcessed:2826
04/11/2013 09:47:59 AM — POLLER: Poller[0] Maximum runtime of 58 seconds exceeded. Exiting.
04/11/2013 09:47:13 AM — PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
04/11/2013 09:47:13 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:13 AM — SPINE: Poller[0] ERROR: SQL Failed! Error:’2013′, Message:’Lost connection to MySQL server during query’, SQL Fragment:’UPDATE host SET status=’3′, status_event_count=’0′, status_fail_date=’2013-04-11 07:26:00′, status_rec_date=’0000-00-00 00:00:00′, status_last_error=’Host responded to SNMP, UDP: Ping timed out’, min_time=’0.085000′, max_time=’19601.960000′, cur_time=’597.089530′, avg_time=’2.193197′, total_polls=’117277′, failed_polls=’3671′, availability=’96.8698′ WHERE id=’71»
04/11/2013 09:47:13 AM — SPINE: Poller[0] FATAL: MySQL Error:’2013′, Message:’Lost connection to MySQL server during query’ (Spine thread)
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect
04/11/2013 09:47:04 AM — SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect

В связи с этим появились пробелы на графиках.
Первым делом я разнёс кактус и базу на разные физические машины. Это не помогло. Какти стал отрабатывать в 10 раз дольше с 26 секунд до 260.
Вернул базу обратно. Погуглив нашёл много рекомендаций. Многие рекомендуют:
max_allowed_packet= 64M
wait_timeout= 6000
Это тоже особо не помогло.

Все таблицы cacti в при записе в таблицу, таблица блокируется. Поэтому есть смысл конвертировать в

# mysql -uroot -p -Dcacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1203
Server version: 5.5.29-0ubuntu0.12.10.1-log (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show tables;
+—————————————+
| Tables_in_cacti |
+—————————————+
| cdef |
| cdef_items |
| colors |
| dashboard_graphs |
| data_input |
| data_input_data |
| data_input_fields |
| data_local |
| data_template |
| data_template_data |
| data_template_data_rra |
| data_template_rrd |
| graph_local |
| graph_template_input |
| graph_template_input_defs |
| graph_templates |
| graph_templates_gprint |
| graph_templates_graph |
| graph_templates_item |
| graph_tree |
| graph_tree_items |
| host |
| host_graph |
| host_snmp_cache |
| host_snmp_query |
| host_template |
| host_template_graph |
| host_template_snmp_query |
| plugin_aggregate_color_template_items |
| plugin_aggregate_color_templates |
| plugin_config |
| plugin_db_changes |
| plugin_discover_hosts |
| plugin_discover_template |
| plugin_hmib_hrDevices |
| plugin_hmib_hrProcessor |
| plugin_hmib_hrSWInstalled |
| plugin_hmib_hrSWRun |
| plugin_hmib_hrSWRun_ignore |
| plugin_hmib_hrSWRun_last_seen |
| plugin_hmib_hrStorage |
| plugin_hmib_hrSystem |
| plugin_hmib_hrSystemTypes |
| plugin_hmib_processes |
| plugin_hmib_types |
| plugin_hooks |
| plugin_mikrotik_processes |
| plugin_mikrotik_processor |
| plugin_mikrotik_storage |
| plugin_mikrotik_system |
| plugin_mikrotik_trees |
| plugin_mikrotik_users |
| plugin_nectar |
| plugin_nectar_items |
| plugin_notification_lists |
| plugin_realms |
| plugin_thold_contacts |
| plugin_thold_host_failed |
| plugin_thold_log |
| plugin_thold_template_contact |
| plugin_thold_threshold_contact |
| poller |
| poller_command |
| poller_item |
| poller_output |
| poller_output_rt |
| poller_reindex |
| poller_time |
| rra |
| rra_cf |
| settings |
| settings_graphs |
| settings_tree |
| snmp_query |
| snmp_query_graph |
| snmp_query_graph_rrd |
| snmp_query_graph_rrd_sv |
| snmp_query_graph_sv |
| syslog |
| syslog_alert |
| syslog_facilities |
| syslog_host_facilities |
| syslog_hosts |
| syslog_incoming |
| syslog_logs |
| syslog_priorities |
| syslog_remove |
| syslog_removed |
| syslog_reports |
| syslog_statistics |
| thold_data |
| thold_template |
| user_auth |
| user_auth_perms |
| user_auth_realm |
| user_log |
| version |
| weathermap_auth |
| weathermap_data |
| weathermap_groups |
| weathermap_maps |
| weathermap_settings |
+—————————————+
102 rows in set (0.27 sec)

Скопировав в тестовый редактор делаем пару несложных операций поск+замена:
1) «| » = на «ALTER TABLE »
2) «|» = » ENGINE = ;»
3) » » = » »

Получилось:

ALTER TABLE cdef ENGINE = INNODB;
ALTER TABLE cdef_items ENGINE = INNODB;
ALTER TABLE colors ENGINE = INNODB;
ALTER TABLE dashboard_graphs ENGINE = INNODB;
ALTER TABLE data_input ENGINE = INNODB;
ALTER TABLE data_input_data ENGINE = INNODB;
ALTER TABLE data_input_fields ENGINE = INNODB;
ALTER TABLE data_local ENGINE = INNODB;
ALTER TABLE data_template ENGINE = INNODB;
ALTER TABLE data_template_data ENGINE = INNODB;
ALTER TABLE data_template_data_rra ENGINE = INNODB;
ALTER TABLE data_template_rrd ENGINE = INNODB;
ALTER TABLE graph_local ENGINE = INNODB;
ALTER TABLE graph_template_input ENGINE = INNODB;
ALTER TABLE graph_template_input_defs ENGINE = INNODB;
ALTER TABLE graph_templates ENGINE = INNODB;
ALTER TABLE graph_templates_gprint ENGINE = INNODB;
ALTER TABLE graph_templates_graph ENGINE = INNODB;
ALTER TABLE graph_templates_item ENGINE = INNODB;
ALTER TABLE graph_tree ENGINE = INNODB;
ALTER TABLE graph_tree_items ENGINE = INNODB;
ALTER TABLE host ENGINE = INNODB;
ALTER TABLE host_graph ENGINE = INNODB;
ALTER TABLE host_snmp_cache ENGINE = INNODB;
ALTER TABLE host_snmp_query ENGINE = INNODB;
ALTER TABLE host_template ENGINE = INNODB;
ALTER TABLE host_template_graph ENGINE = INNODB;
ALTER TABLE host_template_snmp_query ENGINE = INNODB;
ALTER TABLE plugin_aggregate_color_template_items ENGINE = INNODB;
ALTER TABLE plugin_aggregate_color_templates ENGINE = INNODB;
ALTER TABLE plugin_config ENGINE = INNODB;
ALTER TABLE plugin_db_changes ENGINE = INNODB;
ALTER TABLE plugin_discover_hosts ENGINE = INNODB;
ALTER TABLE plugin_discover_template ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrDevices ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrProcessor ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrSWInstalled ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrSWRun ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrSWRun_ignore ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrSWRun_last_seen ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrStorage ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrSystem ENGINE = INNODB;
ALTER TABLE plugin_hmib_hrSystemTypes ENGINE = INNODB;
ALTER TABLE plugin_hmib_processes ENGINE = INNODB;
ALTER TABLE plugin_hmib_types ENGINE = INNODB;
ALTER TABLE plugin_hooks ENGINE = INNODB;
ALTER TABLE plugin_mikrotik_processes ENGINE = INNODB;
ALTER TABLE plugin_mikrotik_processor ENGINE = INNODB;
ALTER TABLE plugin_mikrotik_storage ENGINE = INNODB;
ALTER TABLE plugin_mikrotik_system ENGINE = INNODB;
ALTER TABLE plugin_mikrotik_trees ENGINE = INNODB;
ALTER TABLE plugin_mikrotik_users ENGINE = INNODB;
ALTER TABLE plugin_nectar ENGINE = INNODB;
ALTER TABLE plugin_nectar_items ENGINE = INNODB;
ALTER TABLE plugin_notification_lists ENGINE = INNODB;
ALTER TABLE plugin_realms ENGINE = INNODB;
ALTER TABLE plugin_thold_contacts ENGINE = INNODB;
ALTER TABLE plugin_thold_host_failed ENGINE = INNODB;
ALTER TABLE plugin_thold_log ENGINE = INNODB;
ALTER TABLE plugin_thold_template_contact ENGINE = INNODB;
ALTER TABLE plugin_thold_threshold_contact ENGINE = INNODB;
ALTER TABLE poller ENGINE = INNODB;
ALTER TABLE poller_command ENGINE = INNODB;
ALTER TABLE poller_item ENGINE = INNODB;
ALTER TABLE poller_output ENGINE = INNODB;
ALTER TABLE poller_output_rt ENGINE = INNODB;
ALTER TABLE poller_reindex ENGINE = INNODB;
ALTER TABLE poller_time ENGINE = INNODB;
ALTER TABLE rra ENGINE = INNODB;
ALTER TABLE rra_cf ENGINE = INNODB;
ALTER TABLE settings ENGINE = INNODB;
ALTER TABLE settings_graphs ENGINE = INNODB;
ALTER TABLE settings_tree ENGINE = INNODB;
ALTER TABLE snmp_query ENGINE = INNODB;
ALTER TABLE snmp_query_graph ENGINE = INNODB;
ALTER TABLE snmp_query_graph_rrd ENGINE = INNODB;
ALTER TABLE snmp_query_graph_rrd_sv ENGINE = INNODB;
ALTER TABLE snmp_query_graph_sv ENGINE = INNODB;
ALTER TABLE syslog ENGINE = INNODB;
ALTER TABLE syslog_alert ENGINE = INNODB;
ALTER TABLE syslog_facilities ENGINE = INNODB;
ALTER TABLE syslog_host_facilities ENGINE = INNODB;
ALTER TABLE syslog_hosts ENGINE = INNODB;
ALTER TABLE syslog_incoming ENGINE = INNODB;
ALTER TABLE syslog_logs ENGINE = INNODB;
ALTER TABLE syslog_priorities ENGINE = INNODB;
ALTER TABLE syslog_remove ENGINE = INNODB;
ALTER TABLE syslog_removed ENGINE = INNODB;
ALTER TABLE syslog_reports ENGINE = INNODB;
ALTER TABLE syslog_statistics ENGINE = INNODB;
ALTER TABLE thold_data ENGINE = INNODB;
ALTER TABLE thold_template ENGINE = INNODB;
ALTER TABLE user_auth ENGINE = INNODB;
ALTER TABLE user_auth_perms ENGINE = INNODB;
ALTER TABLE user_auth_realm ENGINE = INNODB;
ALTER TABLE user_log ENGINE = INNODB;
ALTER TABLE version ENGINE = INNODB;
ALTER TABLE weathermap_auth ENGINE = INNODB;
ALTER TABLE weathermap_data ENGINE = INNODB;
ALTER TABLE weathermap_groups ENGINE = INNODB;
ALTER TABLE weathermap_maps ENGINE = INNODB;
ALTER TABLE weathermap_settings ENGINE = INNODB;

Полученые строки скармливаем мускулу:

mysql> ALTER TABLE cdef ENGINE = ;
Query OK, 39 rows affected (18.89 sec)
Records: 39 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE cdef_items ENGINE = ;
Query OK, 105 rows affected (14.09 sec)
Records: 105 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE colors ENGINE = ;
Query OK, 173 rows affected (12.81 sec)
Records: 173 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE dashboard_graphs ENGINE = ;
Query OK, 0 rows affected (14.39 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_input ENGINE = ;
Query OK, 77 rows affected (0.63 sec)
Records: 77 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_input_data ENGINE = ;
Query OK, 71619 rows affected (2 min 8.90 sec)
Records: 71619 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_input_fields ENGINE = ;
Query OK, 518 rows affected (26.97 sec)
Records: 518 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_local ENGINE = ;
Query OK, 5378 rows affected (44.52 sec)
Records: 5378 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_template ENGINE = ;
Query OK, 187 rows affected (32.04 sec)
Records: 187 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_template_data ENGINE = ;
Query OK, 5565 rows affected (37.87 sec)
Records: 5565 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_template_data_rra ENGINE = ;
Query OK, 48697 rows affected (47.57 sec)
Records: 48697 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE data_template_rrd ENGINE = ;
Query OK, 13174 rows affected (1 min 5.69 sec)
Records: 13174 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_local ENGINE = ;
Query OK, 5124 rows affected (24.21 sec)
Records: 5124 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_template_input ENGINE = ;
Query OK, 581 rows affected (40.03 sec)
Records: 581 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_template_input_defs ENGINE = ;
Query OK, 2393 rows affected (17.69 sec)
Records: 2393 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_templates ENGINE = ;
Query OK, 177 rows affected (31.32 sec)
Records: 177 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_templates_gprint ENGINE = ;
Query OK, 12 rows affected (17.21 sec)
Records: 12 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_templates_graph ENGINE = ;
Query OK, 5301 rows affected (38.39 sec)
Records: 5301 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_templates_item ENGINE = ;
Query OK, 57427 rows affected (2 min 57.29 sec)
Records: 57427 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_tree ENGINE = ;
Query OK, 9 rows affected (16.62 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE graph_tree_items ENGINE = ;
Query OK, 101 rows affected (8.17 sec)
Records: 101 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE host ENGINE = ;
Query OK, 82 rows affected (15.85 sec)
Records: 82 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE host_graph ENGINE = ;
Query OK, 873 rows affected (13.74 sec)
Records: 873 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE host_snmp_cache ENGINE = ;
Query OK, 22578 rows affected (3 min 27.95 sec)
Records: 22578 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE host_snmp_query ENGINE = ;
Query OK, 261 rows affected (18.10 sec)
Records: 261 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE host_template ENGINE = ;
Query OK, 21 rows affected (8.90 sec)
Records: 21 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE host_template_graph ENGINE = ;
Query OK, 210 rows affected (18.98 sec)
Records: 210 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE host_template_snmp_query ENGINE = ;
Query OK, 45 rows affected (30.08 sec)
Records: 45 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_aggregate_color_template_items ENGINE = ;
Query OK, 44 rows affected (9.10 sec)
Records: 44 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_aggregate_color_templates ENGINE = ;
Query OK, 4 rows affected (22.54 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_config ENGINE = ;
Query OK, 14 rows affected (19.87 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_db_changes ENGINE = ;
Query OK, 18 rows affected (15.74 sec)
Records: 18 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_discover_hosts ENGINE = ;
Query OK, 0 rows affected (2.39 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_discover_template ENGINE = ;
Query OK, 1 row affected (1.47 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrDevices ENGINE = ;
Query OK, 0 rows affected (10.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrProcessor ENGINE = ;
Query OK, 0 rows affected (20.46 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrSWInstalled ENGINE = ;
Query OK, 0 rows affected (10.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrSWRun ENGINE = ;
Query OK, 0 rows affected (9.56 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrSWRun_ignore ENGINE = ;
Query OK, 0 rows affected (13.90 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrSWRun_last_seen ENGINE = ;
Query OK, 0 rows affected (14.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrStorage ENGINE = ;
Query OK, 0 rows affected (11.51 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrSystem ENGINE = ;
Query OK, 0 rows affected (25.78 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_hrSystemTypes ENGINE = ;
Query OK, 14 rows affected (25.88 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_processes ENGINE = ;
Query OK, 0 rows affected (26.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hmib_types ENGINE = ;
Query OK, 51 rows affected (7.62 sec)
Records: 51 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_hooks ENGINE = ;
Query OK, 113 rows affected (17.16 sec)
Records: 113 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_mikrotik_processes ENGINE = ;
Query OK, 0 rows affected (13.75 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_mikrotik_processor ENGINE = ;
Query OK, 0 rows affected (11.83 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_mikrotik_storage ENGINE = ;
Query OK, 0 rows affected (29.76 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_mikrotik_system ENGINE = ;
Query OK, 12 rows affected (4.00 sec)
Records: 12 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_mikrotik_trees ENGINE = ;
Query OK, 0 rows affected (4.56 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_mikrotik_users ENGINE = ;
Query OK, 0 rows affected (11.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_nectar ENGINE = ;
Query OK, 1 row affected (16.40 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_nectar_items ENGINE = ;
Query OK, 0 rows affected (31.96 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_notification_lists ENGINE = ;
Query OK, 1 row affected (14.28 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_realms ENGINE = ;
Query OK, 21 rows affected (23.27 sec)
Records: 21 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_thold_contacts ENGINE = ;
Query OK, 0 rows affected (12.41 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_thold_host_failed ENGINE = ;
Query OK, 0 rows affected (14.40 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_thold_log ENGINE = ;
Query OK, 0 rows affected (5.47 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_thold_template_contact ENGINE = ;
Query OK, 0 rows affected (7.75 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE plugin_thold_threshold_contact ENGINE = ;
Query OK, 0 rows affected (16.41 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE poller ENGINE = ;
Query OK, 0 rows affected (28.69 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE poller_command ENGINE = ;
Query OK, 0 rows affected (7.66 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE poller_item ENGINE = ;
Query OK, 9002 rows affected (33.93 sec)
Records: 9002 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE poller_output ENGINE = ;
Query OK, 273 rows affected (10.39 sec)
Records: 273 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE poller_output_rt ENGINE = ;
Query OK, 0 rows affected (20.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE poller_reindex ENGINE = ;
Query OK, 684 rows affected (15.39 sec)
Records: 684 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE poller_time ENGINE = ;
Query OK, 4 rows affected (16.50 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE rra ENGINE = ;
Query OK, 10 rows affected (9.13 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE rra_cf ENGINE = ;
Query OK, 23 rows affected (25.11 sec)
Records: 23 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE settings ENGINE = ;
Query OK, 221 rows affected (25.32 sec)
Records: 221 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE settings_graphs ENGINE = ;
Query OK, 105 rows affected (13.87 sec)
Records: 105 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE settings_tree ENGINE = ;
Query OK, 0 rows affected (2.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE snmp_query ENGINE = ;
Query OK, 21 rows affected (6.15 sec)
Records: 21 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE snmp_query_graph ENGINE = ;
Query OK, 61 rows affected (13.49 sec)
Records: 61 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE snmp_query_graph_rrd ENGINE = ;
Query OK, 207 rows affected (42.41 sec)
Records: 207 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE snmp_query_graph_rrd_sv ENGINE = ;
Query OK, 80 rows affected (29.48 sec)
Records: 80 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE snmp_query_graph_sv ENGINE = ;
Query OK, 69 rows affected (8.83 sec)
Records: 69 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog ENGINE = ;
Query OK, 0 rows affected (13.36 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_alert ENGINE = ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_facilities ENGINE = ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_host_facilities ENGINE = ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_hosts ENGINE = ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_incoming ENGINE = ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_logs ENGINE = ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_priorities ENGINE = ;
Query OK, 9 rows affected (0.02 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_remove ENGINE = ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_removed ENGINE = ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_reports ENGINE = ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE syslog_statistics ENGINE = ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE thold_data ENGINE = ;
Query OK, 0 rows affected (16.75 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE thold_template ENGINE = ;
Query OK, 0 rows affected (50.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE user_auth ENGINE = ;
Query OK, 27 rows affected (28.32 sec)
Records: 27 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE user_auth_perms ENGINE = ;
Query OK, 0 rows affected (21.43 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE user_auth_realm ENGINE = ;
Query OK, 63 rows affected (28.27 sec)
Records: 63 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE user_log ENGINE = ;
Query OK, 1891 rows affected (43.15 sec)
Records: 1891 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE version ENGINE = ;
Query OK, 1 row affected (15.22 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE weathermap_auth ENGINE = ;
Query OK, 2 rows affected (36.72 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE weathermap_data ENGINE = ;
Query OK, 0 rows affected (14.56 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE weathermap_groups ENGINE = ;
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE weathermap_maps ENGINE = ;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE weathermap_settings ENGINE = ;
Query OK, 0 rows affected (34.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

Готово. Тоже не помогло (

Имеем: 04/11/2013 11:17:02 AM — SYSTEM STATS: Time:54.4238 Method:spine Processes:4 Threads:24 Hosts:69 HostsPerProcess:18 DataSources:8419 RRDsProcessed:1265
Вместо: 04/11/2013 11:08:08 AM — SYSTEM STATS: Time:60.7363 Method:spine Processes:4 Threads:24 Hosts:69 HostsPerProcess:18 DataSources:8419 RRDsProcessed:4243

Тюним MySQL для работы с cacti:
_buffer_pool_size=1G
table_open_cache=1K
thread_cache_size=256
_max_dirty_pages_pct=90
_log_file_size=32M
_doublewrite=0
_file_per_table=1
_flush_method=O_DIRECT
_open_files=500
_support_xa=0
_thread_concurrency=16

mysql> flush tables;
Query OK, 0 rows affected (1 min 53.86 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.23 sec)

mysql> quit
Bye

Удаляем временные файлы ib_logfile и перезапускаем сервер, так как он уже и так безбожно тупит.

# /etc/init.d/mysql stop
# cd /var/lib/mysql/
# rm ib_logfile*
# reboot

Не помогло (

One Reply to “Cacti log sais: SPINE: Poller[0] WARNING: SQL Failed! Error:’2006′, Message:’MySQL server has gone away’, Attempting to Reconnect”

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*