Mysql tuning – cacti

Enable Slow Query Log

1. Enter the MySQL shell and run the following command:

set global slow_query_log = ‘ON’;

2. Enable any other desired options. Here are some common examples:

Log details for queries expected to retrieve all rows instead of using an index:

set global log_queries_not_using_indexes = ‘ON’

Set the path to the slow query log on my.cnf:

log_slow_queries = 1
slow_query_log_file = /var/log/mysql/slow-query.log

Set the amount of time a query needs to run before being logged:

set global long_query_time = ’20’;

(default is 10 seconds)
3. Confirm the changes are active by entering the MySQL shell and running the following command:

show variables like ‘%slow%’;

Create INDEXes for cacti

CREATE INDEX `data_template_data_id` ON `data_input_data` (`data_template_data_id`);
CREATE INDEX `host_id_snmp_query_id_snmp_index` ON data_local (`host_id`,`snmp_query_id`,`snmp_index`);
CREATE INDEX `local_data_id_data_source_name` ON data_template_rrd (`local_data_id`,`data_source_name`);
CREATE INDEX `graph_template_id_local_graph_id` ON graph_templates_item (`graph_template_id`,`local_graph_id`);
CREATE INDEX `local_graph_template_item_id` ON graph_templates_item (`local_graph_template_item_id`);
CREATE INDEX `host_id_snmp_query_id_snmp_index` ON host_snmp_cache (`host_id`,`snmp_query_id`,`snmp_index`);
CREATE INDEX `local_data_id_rrd_path` ON poller_item (`local_data_id`,`rrd_path`);
CREATE INDEX `host_id_rrd_next_step` ON poller_item (`host_id`,`rrd_next_step`);
create index order_key3 on graph_tree_items (order_key(3));
create index order_key6 on graph_tree_items (order_key(6));
create index order_key9 on graph_tree_items (order_key(9));
CREATE INDEX host_id_snmp_query_id ON host_snmp_cache (host_id,snmp_query_id);
CREATE INDEX host_id_snmp_port ON poller_item (host_id,snmp_port);
CREATE INDEX task_item_id ON graph_templates_item (task_item_id);