zabbix更新 mysql優化

./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 "

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   |

mysql> show global status like 'Innodb_buffer_pool_pages_total';

+--------------------------------+-------+

| Variable_name                  | Value |

| Innodb_buffer_pool_pages_total | 8191  |

mysql> show global status like 'Innodb_page_size';

+------------------+-------+

| Variable_name    | Value |

| Innodb_page_size | 16384 |

上述三項名額的含義如下：

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)

本文轉自 bobo5900pro  51CTO部落格，原文連結：http://blog.51cto.com/bobo5900pro/1841140，如需轉載請自行聯系原作者