zabbix升级 mysql优化

shaoqin

　　./configure --enable-server --enable-agent --with-mysql --enable-ipv6 --with-net-snmp --with-libcurl --with-libxml2
　　

　　apt-get install libmysqlclient-dev
　　

　　--aptitude search snmp | grep dev
　　

　　apt-get install  libxml2 libxml2-dev
　　

　　apt-get install libsnmp-dev
　　

　　apt-get install libcurl4-gnutls-dev
　　

　　fuser -k 80/tcp
　　

　　

　　mysql -u zabbix -p zabbix < /usr/local/zabbix-3.2.0alpha1/database/mysql/schema.sql
　　mysql -u zabbix -p zabbix < /usr/local/zabbix-3.2.0alpha1/database/mysql/images.sql
　　mysql -u zabbix -p zabbix < /usr/local/zabbix-3.2.0alpha1/database/mysql/data.sql
　　

　　apt-get dist-upgrade -u
　　

　　--default-character-set=UTF8
　　

　　*************************************************************************************************************************
　　

　　wget http://repo.zabbix.com/zabbix/3.0/debian/pool/main/z/zabbix/zabbix-server-mysql_3.0.4-1+wheezy_amd64.deb
　　

　　dpkg -i zabbix-server-mysql_3.0.4-1+wheezy_amd64.deb
　　

　　apt-get update
　　

　　

　　apt-get install zabbix-server-mysql zabbix-frontend-php
　　********************************************************************************************************************************************************************
　　

　　统计数据库大小
　　SELECT table_schema , sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"  FROM information_schema.TABLES GROUP BY table_schema;
　　

　　统计表大小并排序
　　SELECT table_name AS "Tables",
　　round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
　　FROM information_schema.TABLES
　　WHERE table_schema = 'zabbix'
　　ORDER BY (data_length + index_length) DESC;
　　

　　

　　use zabbix;
　　truncate table history;
　　optimize  table history;
　　truncate table history_str;
　　truncate table history_uint;
　　

　　**********************************************************************
　　#!/bin/bash
　　

　　# Author: ZhiPeng Wang.
　　# Last Modified: 2014/6/2
　　

　　User="root"
　　Passwd="wangzhipeng"
　　Date=`date -d $(date -d "-30 day" +%Y%m%d) +%s` #取30天之前的时间戳
　　

　　$(which mysql) -u${User} -p${Passwd} -e "
　　use zabbix;
　　DELETE FROM history WHERE 'clock' < $Date;
　　optimize table history;
　　DELETE FROM history_str WHERE 'clock' < $Date;
　　optimize table history_str;
　　DELETE FROM history_uint WHERE 'clock' < $Date;
　　optimize table history_uint;
　　DELETE FROM  trends WHERE 'clock' < $Date;
　　optimize table  trends;
　　DELETE FROM trends_uint WHERE 'clock' < $Date;
　　optimize table trends_uint;
　　DELETE FROM events WHERE 'clock' < $Date;
　　optimize table events;
　　

　　

　　该参数定义了数据缓冲区buffer pool大小，类似于oracle的db_cache_size

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

那么如何设置该参数大小呢？首先查看运行时buffer pool相关数据指标：

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 314   |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 8191  |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

上述三项指标的含义如下：

Innodb_buffer_pool_pages_data
The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and
clean pages.

Innodb_buffer_pool_pages_total
The total size of the InnoDB buffer pool, in pages.

Innodb_page_size
InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be
easily converted to bytes

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%
当结果 < 95% 则减少 innodb_buffer_pool_size，
建议设置大小为： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)
　　

　　

　　
Linux下面 my.cnf
innodb_buffer_pool_size = 10240M