<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" media="screen" href="/~d/styles/rss2full.xsl"?><?xml-stylesheet type="text/css" media="screen" href="http://feeds.feedburner.com/~d/styles/itemcontent.css"?><rss xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:wfw="http://wellformedweb.org/CommentAPI/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:sy="http://purl.org/rss/1.0/modules/syndication/" xmlns:slash="http://purl.org/rss/1.0/modules/slash/" xmlns:feedburner="http://rssnamespace.org/feedburner/ext/1.0" version="2.0">

<channel>
	<title>Zabbix Zone</title>
	
	<link>http://zabbixzone.com</link>
	<description>Free Knowledge for All</description>
	<lastBuildDate>Tue, 08 Nov 2011 10:33:35 +0000</lastBuildDate>
	<language>en</language>
	<sy:updatePeriod>hourly</sy:updatePeriod>
	<sy:updateFrequency>1</sy:updateFrequency>
	<generator>http://wordpress.org/?v=3.3.1</generator>
		<atom10:link xmlns:atom10="http://www.w3.org/2005/Atom" rel="self" type="application/rss+xml" href="http://feeds.feedburner.com/zabbixzone" /><feedburner:info uri="zabbixzone" /><atom10:link xmlns:atom10="http://www.w3.org/2005/Atom" rel="hub" href="http://pubsubhubbub.appspot.com/" /><item>
		<title>MySQL performance tips for Zabbix</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/ak8-XT3cr6w/</link>
		<comments>http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/#comments</comments>
		<pubDate>Mon, 24 Oct 2011 20:24:14 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[database]]></category>
		<category><![CDATA[mysql]]></category>
		<category><![CDATA[performance]]></category>
		<category><![CDATA[zabbix]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=121</guid>
		<description><![CDATA[Most of these tips is useful for many application, but I&#8217;ll keep focus on Zabbix. Use a Dedicated Server Database is the main bottleneck from Zabbix. Try to use a Dedicated Server for MySQL and make sure that server has great resources (CPU, memory and fast disks). This is the specs for an environment with [...]]]></description>
			<content:encoded><![CDATA[<p>Most of these tips is useful for many application, but I&#8217;ll keep focus on Zabbix.</p>
<ol>
<li><strong>Use a Dedicated Server</strong>
<p>Database is the main bottleneck from Zabbix. Try to use a Dedicated Server for MySQL and make sure that server has great resources (CPU, memory and fast disks).</p>
<p>This is the specs for an environment with <strong>3000 values per second</strong>:</p>
<p><strong>Dell PowerEdge R610</strong><br />
CPU: Intel Xeon L5520 2.27GHz (16 cores)<br />
Memory: 24GB RAM<br />
Disks: 6x SAS 10k with RAID10 by hardware
	</li>
<li><strong>Create one file per table</strong>
<p></p><pre class="crayon-plain-tag"><code>innodb_file_per_table=1</code></pre>
<p>By default, InnoDB creates all tables inside an unique datafile. With this option the <strong>new tables</strong> will have your own datafile. So after the change, You&#8217;ll need recreate the tables.</p>
<p>It opens some possibilities like put your tables in different filesystems and makes backup with more consistency.</p>
<blockquote><p><strong>Peter Zaitsev</strong> &#8211; <a href="http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/" target="_blank">http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/</a><br />
innodb_file_per_table &#8211; If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can&#8217;t reclaim.</p></blockquote>
<blockquote><p><strong>Tristan &#8211; cPanel Staff</strong> &#8211; <a href="http://forums.cpanel.net/f43/innodb_file_per_table-converting-per-table-data-innodb-167942.html" target="_blank">http://forums.cpanel.net/f43/innodb_file_per_table-converting-per-table-data-innodb-167942.html</a><br />
<strong>Issue with shared InnoDB /var/lib/mysql/ibdata1 storage</strong><br />
InnoDB tables currently store data and indexes into a shared tablespace (/var/lib/mysql/ibdata1). Due to the shared tablespace, data corruption for one InnoDB table can result in MySQL failing to start up on the entire machine. Repairing InnoDB corruption can be extremely difficult to perform and can result in data loss for tables that were not corrupted originally during that repair process.</p></blockquote>
<p>Some discussions about this:<br />
<a href="http://dom.as/2009/05/21/innodb-tablespace/" target="_blank">http://dom.as/2009/05/21/innodb-tablespace/</a><br />
<a href="http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table" target="_blank">http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table</a></p>
<p>Personally I lost all my data because the ibdata file crashed (all data inside one file).</p>
<p>Ref.: <a target="_blank" href="http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html">http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html</a>
	</li>
<li><strong>Percona vs Community Edition</strong>
<p><a target="_blank" href="http://www.percona.com/software/percona-server/">Percona Server</a> is a modified version from <a href="http://www.mysql.com/products/community/">MySQL Community Edition</a>.</p>
<p>Some benchmarks show us about Percona performance advantages:<br />
<a target="_blank" href="http://www.percona.com/software/percona-server/benchmarks/">http://www.percona.com/software/percona-server/benchmarks/</a>
	</li>
<li><strong>Use partitioning tables and disable the Housekeeper</strong>
<p>Housekeeper reduces the MySQL performance (see <a href="http://zabbixzone.com/zabbix/history-tables-housekeeper/" title="History Tables – Housekeeper" target="_blank">History Tables – Housekeeper</a>). So a simple alternative is use the Partitioning native resource from MySQL.</p>
<p>In this blog there is another article about it: <a href="http://zabbixzone.com/zabbix/partitioning-tables/" title="Partitioning Tables" target="_blank">Partitioning Tables</a>
	</li>
<li><strong>Use tmpfs filesystem for Temporary Files</strong>
<p>Using memory instead of local disks will allow a much faster creation of temporary tables on MySQL.</p>
<p>First, create the mountpoint:</p><pre class="crayon-plain-tag"><code>mkdir /tmp/mysqltmp</code></pre>
<p>Add this line in your <strong>/etc/fstab</strong>:</p><pre class="crayon-plain-tag"><code>tmpfs /tmp/mysqltmp tmpfs rw,uid=mysql,gid=mysql,size=1G,nr_inodes=10k,mode=0700 0 0</code></pre>
<p>Make sure to adjust the <strong>size parameter</strong>. For reference, use 08~10% from physical memory.</p>
<p>Finally, you need to define this path in <strong>/etc/my.cnf</strong> and restart MySQL:</p><pre class="crayon-plain-tag"><code>tmpdir = /tmp/mysqltmp</code></pre><p></li>
<li><strong>Set your Buffer Pool properly</strong>
<p>This is one of most important parameters in <strong>/etc/my.cnf</strong>. It defines how much memory InnoDB can use.</p>
<p>I recommend something like 70~80% from physical memory:</p><pre class="crayon-plain-tag"><code>innodb_buffer_pool_size=14G</code></pre>
<p>Ref.: <a href="http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/" target="_blank">http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/</a>
	</li>
</ol>
<p>It&#8217;s the <strong>/etc/my.cnf</strong> sample for a server with 24GB RAM:</p><pre class="crayon-plain-tag"><code>[mysqld]
# paths
datadir                         = /var/lib/mysql/data
tmpdir                          = /tmp/mysqltmp

# network
connect_timeout                 = 60
wait_timeout                    = 28800
max_connections                 = 2048
max_allowed_packet              = 64M
max_connect_errors              = 1000

# limits
tmp_table_size                  = 512M
max_heap_table_size             = 256M
table_cache                     = 512

# logs
log_error                       = /var/log/mysql/mysql-error.log
slow_query_log_file             = /var/log/mysql/mysql-slow.log
slow_query_log                  = 1
long_query_time                 = 20

# innodb
innodb_data_home_dir            = /var/lib/mysql/data
innodb_data_file_path           = ibdata1:128M;ibdata2:128M:autoextend:max:4096M
innodb_file_per_table           = 1
innodb_status_file              = 1
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size         = 14G
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000
innodb_flush_log_at_trx_commit  = 2
innodb_support_xa               = 0
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 128M

# experimental
innodb_stats_update_need_lock   = 0

# other stuff
event_scheduler                 = 1
query_cache_type                = 0</code></pre><p><p><p><p><p>
<p>If you need, there are some references about MySQL parameters:<br />
<a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html" target="_blank">http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html</a><br />
<a href="http://www.mysqlperformanceblog.com/" target="_blank">http://www.mysqlperformanceblog.com/</a></p>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/easy-update-on-custom-scripts/" rel="bookmark" class="crp_title">Easy Update on Custom Scripts</a></li><li><a href="http://zabbixzone.com/zabbix/making-your-frontend-faster/" rel="bookmark" class="crp_title">Making your Frontend Faster</a></li><li><a href="http://zabbixzone.com/zabbix/history-tables-housekeeper/" rel="bookmark" class="crp_title">History Tables &#8211; Housekeeper</a></li><li><a href="http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/" rel="bookmark" class="crp_title">Backuping only the Zabbix Configuration</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/feed/</wfw:commentRss>
		<slash:comments>4</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/</feedburner:origLink></item>
		<item>
		<title>Backuping only the Zabbix Configuration</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/5dsiDhXa_24/</link>
		<comments>http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/#comments</comments>
		<pubDate>Tue, 31 May 2011 02:38:31 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[backup]]></category>
		<category><![CDATA[database]]></category>
		<category><![CDATA[mysql]]></category>
		<category><![CDATA[scripts]]></category>
		<category><![CDATA[zabbix]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=126</guid>
		<description><![CDATA[This script is a simple way to backup all configuration tables (eg. templates, hostgroups, hosts, triggers&#8230;) without the history data. As the result is very small (around 30 MB), is possible run this backup many times per day. https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-backupconf.sh#!/bin/bash # # zabbix-mysql-backupconf.sh # v0.2 - 20111105 # # Configuration Backup for Zabbix 1.8 w/MySQL # [...]]]></description>
			<content:encoded><![CDATA[<p>This script is a simple way to backup all configuration tables (eg. templates, hostgroups, hosts, triggers&#8230;) without the history data.</p>
<p>As the result is very small (around 30 MB), is possible run this backup many times per day.</p>
<p><a title="Configuration Backup Script for Zabbix 1.8 with MySQL" href="http://pastebin.com/vkP3pW5X" target="_blank">https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-backupconf.sh</a></p><pre class="crayon-plain-tag"><code>#!/bin/bash
#
# zabbix-mysql-backupconf.sh
# v0.2 - 20111105
#
# Configuration Backup for Zabbix 1.8 w/MySQL
#
# Author: Ricardo Santos (rsantos at gmail.com)
# http://zabbixzone.com
#
# Thanks for suggestions from:
# - Oleksiy Zagorskyi (zalex)
# - Petr Jendrejovsky
#

# mysql config
DBHOST=&quot;localhost&quot;
DBNAME=&quot;zabbix&quot;
DBUSER=&quot;zabbix&quot;
DBPASS=&quot;YOURMYSQLPASSWORDHERE&quot;

# some tools
MYSQLDUMP=&quot;`which mysqldump`&quot;
GZIP=&quot;`which gzip`&quot;
DATEBIN=&quot;`which date`&quot;
MKDIRBIN=&quot;`which mkdir`&quot;

# target path
MAINDIR=&quot;/var/lib/zabbix/backupconf&quot;
DUMPDIR=&quot;${MAINDIR}/`${DATEBIN} +%Y%m%d%H%M`&quot;
${MKDIRBIN} -p ${DUMPDIR}

# configuration tables
CONFTABLES=( actions applications autoreg_host conditions config dchecks dhosts \
drules dservices escalations expressions functions globalmacro graph_theme \
graphs graphs_items groups help_items hostmacro hosts hosts_groups \
hosts_profiles hosts_profiles_ext hosts_templates housekeeper httpstep \
httpstepitem httptest httptestitem ids images items items_applications \
maintenances maintenances_groups maintenances_hosts maintenances_windows \
mappings media media_type node_cksum nodes opconditions operations \
opmediatypes profiles proxy_autoreg_host proxy_dhistory proxy_history regexps \
rights screens screens_items scripts service_alarms services services_links \
services_times sessions slides slideshows sysmaps sysmaps_elements \
sysmaps_link_triggers sysmaps_links timeperiods trigger_depends triggers \
user_history users users_groups usrgrp valuemaps )

# tables with large data
DATATABLES=( acknowledges alerts auditlog_details auditlog events \
history history_log history_str history_str_sync history_sync history_text \
history_uint history_uint_sync trends trends_uint )

# CONFTABLES
for table in ${CONFTABLES[*]}; do
        DUMPFILE=&quot;${DUMPDIR}/${table}.sql&quot;
        echo &quot;Backuping table ${table}&quot;
        ${MYSQLDUMP} -R --opt --extended-insert=FALSE \
                -h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} --tables ${table} &gt;${DUMPFILE}
        ${GZIP} -f ${DUMPFILE}
done

# DATATABLES
for table in ${DATATABLES[*]}; do
        DUMPFILE=&quot;${DUMPDIR}/${table}.sql&quot;
        echo &quot;Backuping schema table ${table}&quot;
        ${MYSQLDUMP} -R --opt --no-data	\
                -h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} --tables ${table} &gt;${DUMPFILE}
        ${GZIP} -f ${DUMPFILE}
done

echo
echo &quot;Backup Completed - ${DUMPDIR}&quot;</code></pre>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/easy-update-on-custom-scripts/" rel="bookmark" class="crp_title">Easy Update on Custom Scripts</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li><li><a href="http://zabbixzone.com/zabbix/making-your-frontend-faster/" rel="bookmark" class="crp_title">Making your Frontend Faster</a></li><li><a href="http://zabbixzone.com/zabbix/history-and-trends/" rel="bookmark" class="crp_title">History and Trends</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/feed/</wfw:commentRss>
		<slash:comments>9</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/</feedburner:origLink></item>
		<item>
		<title>Partitioning Tables</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/hsaLsNxFwwc/</link>
		<comments>http://zabbixzone.com/zabbix/partitioning-tables/#comments</comments>
		<pubDate>Wed, 18 May 2011 05:09:21 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[database]]></category>
		<category><![CDATA[mysql]]></category>
		<category><![CDATA[performance]]></category>
		<category><![CDATA[server]]></category>
		<category><![CDATA[zabbix]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=101</guid>
		<description><![CDATA[Many performance problems on Zabbix are related to the database and it became visible only when we have thousands of items. On another side, we have to handle enormous tables and it makes impossible operational routines like housekeeping and backup. Use all information and scripts at your own risk. Make backups! My suggestion is disable [...]]]></description>
			<content:encoded><![CDATA[<p>Many <strong>performance problems</strong> on Zabbix are <strong>related to the database</strong> and it became visible only when we have thousands of items. On another side, we have to handle <strong>enormous tables</strong> and it makes impossible operational routines like housekeeping and backup.</p>
<p><strong>Use all information and scripts at your own risk. Make backups!<br />
</strong></p>
<p>My suggestion is <a title="History Tables – Housekeeper" href="http://zabbixzone.com/zabbix/history-tables-housekeeper/" target="_blank">disable housekeeping</a> and to partition some tables based on <strong>clock</strong>, an <a href="http://en.wikipedia.org/wiki/Unix_time" target="_blank">unix timestamp</a> field. If you wanna know more about partitioning access the <a href="http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html" target="_blank">MySQL Reference Manual &#8211; Partitioning Overview</a>.</p>
<p>&nbsp;</p>
<p><strong>The Concept</strong></p>
<p><a href="http://zabbixzone.com/wp-content/uploads/2011/05/partitioning.png"><img class="size-full wp-image-102 alignright" title="Partitioning Concept" src="http://zabbixzone.com/wp-content/uploads/2011/05/partitioning.png" alt="" width="247" height="257" /></a>An easy way to handle partitioning is splitting the tables into <strong>two groups</strong> based on <strong>their growth</strong>:</p>
<p><strong>Daily</strong> &#8211; basically the largest tables</p>
<ul>
<li>history</li>
<li>history_log</li>
<li>history_str</li>
<li>history_text</li>
<li>history_uint</li>
</ul>
<p>See more information about <a title="History and Trends" href="http://zabbixzone.com/zabbix/history-and-trends/">history tables</a>.</p>
<p><strong>Monthly</strong> &#8211; other huge tables</p>
<ul>
<li>acknowledges</li>
<li>alerts</li>
<li>auditlog</li>
<li>events</li>
<li>service_alarms</li>
<li>trends</li>
<li>trends_unit</li>
</ul>
<p>&nbsp;</p>
<p><strong>Step 1 &#8211; Preparing the Tables</strong></p>
<p>To partition a table using the <strong>clock</strong>, the field needs belong to the primary key or not have one. So we need make some key changes on this tables:</p><pre class="crayon-plain-tag"><code>ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledgedid` (`acknowledgeid`);
ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alertid` (`alertid`);
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditid` (`auditid`);
ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `eventid` (`eventid`);
ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `servicealarmid` (`servicealarmid`);
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;</code></pre><p>&nbsp;</p>
<p><strong>Step 2 &#8211; Setup Monthly Partitions</strong></p>
<p>Repeat this step with each table (see above) with appropriate months.</p>
<p>In this example, we&#8217;ll create the monthly partitions to <strong>events table</strong> since <strong>May 2011</strong> until <strong>December 2011</strong>:</p><pre class="crayon-plain-tag"><code>ALTER TABLE `events` PARTITION BY RANGE( clock ) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-06-01 00:00:00&quot;)),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-07-01 00:00:00&quot;)),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-08-01 00:00:00&quot;)),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-09-01 00:00:00&quot;)),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-10-01 00:00:00&quot;)),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-11-01 00:00:00&quot;)),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-12-01 00:00:00&quot;)),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2012-01-01 00:00:00&quot;))
);</code></pre><p>&nbsp;</p>
<p><strong>Step 3 &#8211; Setup Daily Partitions</strong></p>
<p>Repeat this step with each table (see above) with appropriate days.</p>
<p>In this example, we&#8217;ll create the daily partitions to <strong>history_uint table</strong> since <strong>15th May </strong>until <strong>22th May</strong>:</p><pre class="crayon-plain-tag"><code>ALTER TABLE `history_uint` PARTITION BY RANGE( clock ) (
PARTITION p20110515 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-16 00:00:00&quot;)),
PARTITION p20110516 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-17 00:00:00&quot;)),
PARTITION p20110517 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-18 00:00:00&quot;)),
PARTITION p20110518 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-19 00:00:00&quot;)),
PARTITION p20110519 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-20 00:00:00&quot;)),
PARTITION p20110520 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-21 00:00:00&quot;)),
PARTITION p20110521 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-22 00:00:00&quot;)),
PARTITION p20110522 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-23 00:00:00&quot;))
);</code></pre><p>&nbsp;</p>
<p><strong>Maintaining the Partitions Manually<br />
</strong></p>
<p>Adding new partitions:</p><pre class="crayon-plain-tag"><code>ALTER TABLE `history_uint` ADD PARTITION (
PARTITION p20110523 VALUES LESS THAN (UNIX_TIMESTAMP(&quot;2011-05-24 00:00:00&quot;))
);</code></pre><p>Dropping partitions (Housekeeping):</p><pre class="crayon-plain-tag"><code>ALTER TABLE `history_uint` DROP PARTITION p20110515;</code></pre><p>&nbsp;</p>
<p><strong>Step 4 &#8211; Auto </strong><strong>Daily </strong><strong>Partitioning<br />
</strong></p>
<p>Make sure that you created the partitioning correctly on the history tables (Step 3)</p>
<p>This script drops and creates automatically the daily partitioning.</p>
<p>The default is keep only last 3 days, if you wanna more days change @mindays variable.</p>
<p>Don&#8217;t forget to put it on cron:</p><pre class="crayon-plain-tag"><code>mysql -B -h localhost -u zabbix -pPASSWORD zabbix -e &quot;CALL create_zabbix_partitions();&quot;</code></pre><p><strong> </strong><strong>Procedures</strong><a href="https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-autopartitioning.sql" target="_blank"></p>
<p>https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-autopartitioning.sql</a></p>
<pre class="crayon-plain-tag"><code>/**************************************************************
  MySQL Auto Partitioning Procedure for Zabbix 1.8
  http://zabbixzone.com/zabbix/partitioning-tables/

  Author:  Ricardo Santos (rsantos at gmail.com)
  Version: 20110518
**************************************************************/
DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
BEGIN
	CALL zabbix.create_next_partitions(&quot;zabbix&quot;,&quot;history&quot;);
	CALL zabbix.create_next_partitions(&quot;zabbix&quot;,&quot;history_log&quot;);
	CALL zabbix.create_next_partitions(&quot;zabbix&quot;,&quot;history_str&quot;);
	CALL zabbix.create_next_partitions(&quot;zabbix&quot;,&quot;history_text&quot;);
	CALL zabbix.create_next_partitions(&quot;zabbix&quot;,&quot;history_uint&quot;);
	CALL zabbix.drop_old_partitions(&quot;zabbix&quot;,&quot;history&quot;);
	CALL zabbix.drop_old_partitions(&quot;zabbix&quot;,&quot;history_log&quot;);
	CALL zabbix.drop_old_partitions(&quot;zabbix&quot;,&quot;history_str&quot;);
	CALL zabbix.drop_old_partitions(&quot;zabbix&quot;,&quot;history_text&quot;);
	CALL zabbix.drop_old_partitions(&quot;zabbix&quot;,&quot;history_uint&quot;);
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
	DECLARE NEXTCLOCK timestamp;
	DECLARE PARTITIONNAME varchar(16);
	DECLARE CLOCK int;
	SET @totaldays = 7;
	SET @i = 1;
	createloop: LOOP
		SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
		SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
		SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
		CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
		SET @i=@i+1;
		IF @i &gt; @totaldays THEN
			LEAVE createloop;
		END IF;
	END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
	DECLARE OLDCLOCK timestamp;
	DECLARE PARTITIONNAME varchar(16);
	DECLARE CLOCK int;
	SET @mindays = 3;
	SET @maxdays = @mindays+4;
	SET @i = @maxdays;
	droploop: LOOP
		SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
		SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
		CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
		SET @i=@i-1;
		IF @i &lt;= @mindays THEN
			LEAVE droploop;
		END IF;
	END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGIN
	DECLARE RETROWS int;
	SELECT COUNT(1) INTO RETROWS
		FROM `information_schema`.`partitions`
		WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
	
	IF RETROWS = 0 THEN
		SELECT CONCAT( &quot;create_partition(&quot;, SCHEMANAME, &quot;,&quot;, TABLENAME, &quot;,&quot;, PARTITIONNAME, &quot;,&quot;, CLOCK, &quot;)&quot; ) AS msg;
     		SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
				' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	END IF;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //
CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
BEGIN
	DECLARE RETROWS int;
	SELECT COUNT(1) INTO RETROWS
		FROM `information_schema`.`partitions`
		WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
	
	IF RETROWS = 1 THEN
		SELECT CONCAT( &quot;drop_partition(&quot;, SCHEMANAME, &quot;,&quot;, TABLENAME, &quot;,&quot;, PARTITIONNAME, &quot;)&quot; ) AS msg;
     		SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
				' DROP PARTITION ', PARTITIONNAME, ';' );
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	END IF;
END //
DELIMITER ;</code></pre>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/easy-update-on-custom-scripts/" rel="bookmark" class="crp_title">Easy Update on Custom Scripts</a></li><li><a href="http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/" rel="bookmark" class="crp_title">Backuping only the Zabbix Configuration</a></li><li><a href="http://zabbixzone.com/zabbix/history-and-trends/" rel="bookmark" class="crp_title">History and Trends</a></li><li><a href="http://zabbixzone.com/zabbix/queue-explained/" rel="bookmark" class="crp_title">Queue Explained</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/partitioning-tables/feed/</wfw:commentRss>
		<slash:comments>20</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/partitioning-tables/</feedburner:origLink></item>
		<item>
		<title>History Tables – Housekeeper</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/t0iMI_W6RYw/</link>
		<comments>http://zabbixzone.com/zabbix/history-tables-housekeeper/#comments</comments>
		<pubDate>Fri, 13 May 2011 01:08:54 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[database]]></category>
		<category><![CDATA[mysql]]></category>
		<category><![CDATA[performance]]></category>
		<category><![CDATA[server]]></category>
		<category><![CDATA[zabbix]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=90</guid>
		<description><![CDATA[First, I recommend to you read about the differences between the history and trends table. Each item on Zabbix can have your own housekeeper value. But this feature turns housekeeping process a performance killer, because delete_history() function executes a DELETE per item every turn. If you have 100k items, 100k DELETE queries will be executed. [...]]]></description>
			<content:encoded><![CDATA[<p>First, I recommend to you read about the <a title="History and Trends" href="http://zabbixzone.com/zabbix/history-and-trends/">differences between the history and trends table</a>.</p>
<p>Each item on Zabbix can have your own housekeeper value. But this feature turns housekeeping process a performance killer, because <a href="http://git.zabbixzone.com/zabbix1.8/.git/blob/HEAD:/src/zabbix_server/housekeeper/housekeeper.c#l261" target="_blank">delete_history() function</a> executes a DELETE per item every turn. If you have 100k items, 100k DELETE queries will be executed.</p>
<p>This can be a big problem using InnoDB engine on MySQL, because <a href="http://www.linkedin.com/answers/technology/information-technology/databases/TCH_ITS_DBS/563096-12145587?browseCategory=TCH_ITS" target="_blank">DELETE is extremelly slow on large tables</a> and <a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-file-space.html" target="_blank">deleted rows doesn&#8217;t release space</a> on disk.</p>
<p>My proposed and tested solution is <strong>disable housekeeping</strong> and partitioning some tables.</p>
<p>To know more about it. read my post <a title="Partitioning Tables" href="http://zabbixzone.com/zabbix/partitioning-tables/">Partitioning Tables</a>.</p>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/history-and-trends/" rel="bookmark" class="crp_title">History and Trends</a></li><li><a href="http://zabbixzone.com/zabbix/zabbix-git-repositor/" rel="bookmark" class="crp_title">Zabbix Git Repository</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li><li><a href="http://zabbixzone.com/zabbix/queue-explained/" rel="bookmark" class="crp_title">Queue Explained</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/history-tables-housekeeper/feed/</wfw:commentRss>
		<slash:comments>9</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/history-tables-housekeeper/</feedburner:origLink></item>
		<item>
		<title>Zabbix Git Repository</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/eu9D73OIBIA/</link>
		<comments>http://zabbixzone.com/zabbix/zabbix-git-repositor/#comments</comments>
		<pubDate>Wed, 20 Apr 2011 02:01:49 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[sourcecode]]></category>
		<category><![CDATA[zabbix]]></category>
		<category><![CDATA[zabbix source code git svn subversion repository gitweb]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=77</guid>
		<description><![CDATA[I usually explore the source code of Zabbix to understand some resources, then I&#8217;d like to do it using a browser, without need to download the tar.gz file, uncompress, use a tool like grep to find any content and finally open it with a editor like vim. Moreover, in recent years Git gained much popularity [...]]]></description>
			<content:encoded><![CDATA[<p>I usually explore the source code of Zabbix to understand some resources, then I&#8217;d like to do it using a browser, without need to download the tar.gz file, uncompress, use a tool like <a href="http://en.wikipedia.org/wiki/Grep" target="_blank">grep</a> to find any content and finally open it with a editor like <a href="http://en.wikipedia.org/wiki/Vim_%28text_editor%29" target="_blank">vim</a>.</p>
<p>Moreover, in recent years <a href="http://git-scm.com/" target="_blank">Git</a> gained much popularity among coders against the <a href="http://subversion.tigris.org/" target="_blank">SVN</a> (Subversion).</p>
<p>Gathered these needs, I created a read-only Git repository with a <a href="https://git.wiki.kernel.org/index.php/Gitweb" target="_blank">GitWeb</a> interface:</p>
<p><strong><a href="http://git.zabbixzone.com" target="_blank">http://git.zabbixzone.com</a></strong></p>
<p>It is automatically updated every 4 hours from the official <strong>Zabbix SVN</strong> (svn://svn.zabbix.com) and is only recommended for research purposes.</p>
<p>&nbsp;</p>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/history-tables-housekeeper/" rel="bookmark" class="crp_title">History Tables &#8211; Housekeeper</a></li><li><a href="http://zabbixzone.com/zabbix/making-your-frontend-faster/" rel="bookmark" class="crp_title">Making your Frontend Faster</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li><li><a href="http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/" rel="bookmark" class="crp_title">MySQL performance tips for Zabbix</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/zabbix-git-repositor/feed/</wfw:commentRss>
		<slash:comments>1</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/zabbix-git-repositor/</feedburner:origLink></item>
		<item>
		<title>New Colors on Frontend</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/qV6wkSPxO3c/</link>
		<comments>http://zabbixzone.com/zabbix/new-colors-on-frontend/#comments</comments>
		<pubDate>Thu, 14 Apr 2011 04:44:41 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[frontend]]></category>
		<category><![CDATA[zabbix]]></category>
		<category><![CDATA[colors]]></category>
		<category><![CDATA[images]]></category>
		<category><![CDATA[themes]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=72</guid>
		<description><![CDATA[I&#8217;ve to deal all day with many Zabbix environments and all are blue color, so there is not difficult to make a mistake. Only changing 3 files inside images directory, I created new six colors: cyan, golden, green, grey, red and violet. Installation Instructions (only for Zabbix 1.8.x) Download zabbix1.8-colors.zip (all colors included) and extract [...]]]></description>
			<content:encoded><![CDATA[<p>I&#8217;ve to deal all day with many Zabbix environments and all are blue color, so there is not difficult to make a mistake.</p>
<p>Only changing 3 files inside images directory, I created new six colors: cyan, golden, green, grey, red and violet.</p>
<p><strong>Installation Instructions </strong>(only for Zabbix 1.8.x)<strong><br />
</strong></p>
<ol>
<li>Download <a href="http://zabbixzone.com/wp-content/uploads/2011/04/zabbix1.8-colors.zip">zabbix1.8-colors.zip</a> (all colors included) and extract on your computer</li>
<li>Upload the 3 image files from your favorite color to FTP on directory <strong>/images/gradients/</strong></li>
<li>Reload the Page. Some browsers can need to force the refresh (CTRL+F5 or CTRL+R)</li>
</ol>
<p><strong>Preview</strong></p>
<p><strong>cyan<br />
<img class="size-full wp-image-65 alignnone" title="zabbix1.8-cyan" src="http://zabbixzone.com/wp-content/uploads/2011/04/zabbix1.8-cyan.gif" alt="" width="404" height="207" /></strong></p>
<p><strong>golden<br />
<img class="alignnone size-full wp-image-66" title="zabbix1.8-golden" src="http://zabbixzone.com/wp-content/uploads/2011/04/zabbix1.8-golden.gif" alt="" width="404" height="207" /></strong></p>
<p><strong>green<br />
<img class="alignnone size-full wp-image-67" title="zabbix1.8-green" src="http://zabbixzone.com/wp-content/uploads/2011/04/zabbix1.8-green.gif" alt="" width="404" height="207" /></strong></p>
<p><strong>grey<br />
<img class="alignnone size-full wp-image-68" title="zabbix1.8-grey" src="http://zabbixzone.com/wp-content/uploads/2011/04/zabbix1.8-grey.gif" alt="" width="404" height="207" /></strong></p>
<p><strong>red<br />
<img class="alignnone size-full wp-image-69" title="zabbix1.8-red" src="http://zabbixzone.com/wp-content/uploads/2011/04/zabbix1.8-red.gif" alt="" width="404" height="207" /></strong></p>
<p><strong>violet<br />
<img class="alignnone size-full wp-image-70" title="zabbix1.8-violet" src="http://zabbixzone.com/wp-content/uploads/2011/04/zabbix1.8-violet.gif" alt="" width="404" height="207" /></strong></p>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/queue-explained/" rel="bookmark" class="crp_title">Queue Explained</a></li><li><a href="http://zabbixzone.com/zabbix/history-and-trends/" rel="bookmark" class="crp_title">History and Trends</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li><li><a href="http://zabbixzone.com/zabbix/history-tables-housekeeper/" rel="bookmark" class="crp_title">History Tables &#8211; Housekeeper</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/new-colors-on-frontend/feed/</wfw:commentRss>
		<slash:comments>2</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/new-colors-on-frontend/</feedburner:origLink></item>
		<item>
		<title>Queue Explained</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/qro2R_Z9ews/</link>
		<comments>http://zabbixzone.com/zabbix/queue-explained/#comments</comments>
		<pubDate>Thu, 07 Apr 2011 02:33:20 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[frontend]]></category>
		<category><![CDATA[proxy]]></category>
		<category><![CDATA[zabbix]]></category>
		<category><![CDATA[performance]]></category>
		<category><![CDATA[queue]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=54</guid>
		<description><![CDATA[Queue&#8217;s page is one of essential indicators to measure the performance of Server/Proxy on collect data. But the numbers can be a little hard to understand. Let&#8217;s try make it easy: Imagine that the last check from an item occurred at 11:05:00. It&#8217;s configured to check each 3 minutes (180 seconds delay), then the next [...]]]></description>
			<content:encoded><![CDATA[<p>Queue&#8217;s page is one of <strong>essential</strong> indicators to <strong>measure the performance</strong> of Server/Proxy on collect data.</p>
<p>But the numbers can be a little hard to understand. Let&#8217;s try make it easy:</p>
<p>Imagine that the <strong>last check</strong> from an item occurred at <strong>11:05:00</strong>. It&#8217;s configured to check each <strong>3 minutes</strong> (180 seconds delay), then the <strong>next check</strong> should be made ​​at <strong>11:08:00</strong>.</p>
<p><strong>Something happened</strong> (like Zabbix Proxy was down)  and this <strong>item was not checked</strong> at 11:08:00.</p>
<p>If the clock is now <strong>11:09:10</strong>, this item is <strong>delayed 70 seconds</strong>, then it would appear in the <strong>column &#8220;1 minute&#8221;</strong>.</p>
<p>According to the image below, in the 5th line:</p>
<ul>
<li><strong>457 items</strong> are delayed between <strong>5 and 10 seconds</strong> (x&gt;=5 and x&lt;10)</li>
<li><strong>12 items</strong> are delayed between <strong>10 and 30 seconds</strong> (x&gt;=10 and x&lt;30)</li>
<li><strong>3 items</strong> are delayed between <strong>1 and 5 minutes</strong> (x&gt;=60 and x&lt;300)</li>
<li><strong>1 item</strong> is delayed between<strong> 5 and 10 minutes</strong> (x&gt;=300 and x&lt;600)</li>
</ul>
<p><a href="http://zabbixzone.com/wp-content/uploads/2011/04/queue.png"><img class="alignnone size-full wp-image-56" title="Queue by Proxy" src="http://zabbixzone.com/wp-content/uploads/2011/04/queue.png" alt="" width="619" height="557" /></a></p>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/new-colors-on-frontend/" rel="bookmark" class="crp_title">New Colors on Frontend</a></li><li><a href="http://zabbixzone.com/zabbix/history-and-trends/" rel="bookmark" class="crp_title">History and Trends</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li><li><a href="http://zabbixzone.com/zabbix/history-tables-housekeeper/" rel="bookmark" class="crp_title">History Tables &#8211; Housekeeper</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/queue-explained/feed/</wfw:commentRss>
		<slash:comments>3</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/queue-explained/</feedburner:origLink></item>
		<item>
		<title>History and Trends</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/GDAlhJiU9Ec/</link>
		<comments>http://zabbixzone.com/zabbix/history-and-trends/#comments</comments>
		<pubDate>Sat, 02 Apr 2011 05:18:48 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[database]]></category>
		<category><![CDATA[zabbix]]></category>
		<category><![CDATA[history]]></category>
		<category><![CDATA[housekeeper]]></category>
		<category><![CDATA[trends]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=7</guid>
		<description><![CDATA[These two terms can cause some confusion, but their understanding is crucial for small and medium environments. History All collected data, doesn&#8217;t matter what type, will be stored on history tables. Trends Trends basically are the collected data (only numeric) consolidated by hour. Zabbix Server hourly takes all values from history tables and calculates minimum, [...]]]></description>
			<content:encoded><![CDATA[<p>These two terms can cause some confusion, but their understanding is crucial for small and medium environments.</p>
<p><strong>History</strong></p>
<p><strong>All collected data</strong>, doesn&#8217;t matter what type, will be stored on history tables.</p>
<p><strong>Trends</strong></p>
<p>Trends basically are the collected data (only numeric) <strong>consolidated by hour</strong>. Zabbix Server hourly takes all values from history tables and calculates minimum, average and maximum for one hour period.</p>
<p><strong>Note:</strong> string items, like character, log and text, doesn&#8217;t have trends</p>
<p><strong><span id="more-7"></span>Housekeeper</strong></p>
<p>Both data still remains on database until <strong>Housekeeper</strong> from Zabbix Server deletes it based on <em>Keep history</em> and <em>Keep trends</em> configurations:</p>
<p><a href="http://zabbixzone.com/wp-content/uploads/2011/04/itemhistory.png"><img class="aligncenter" title="Item History Configuration" src="http://zabbixzone.com/wp-content/uploads/2011/04/itemhistory.png" alt="Item History Configuration" width="711" height="178" /></a></p>
<p>So if  you have an item with 60 seconds <em>Update interval</em> and 7 days for <em>Keep  history</em> it will generates (60*24*7) 10080 rows per-item on history table.</p>
<p>For trend table, with 730 days configuration, it will generates (24*7) 17520 rows per-item.</p>
<p><strong>Database tables for history/trends</strong> and theirs item type:</p>
<ul>
<li><strong>history</strong> &#8211; numeric (float)</li>
<li><strong>history_uint</strong> &#8211; numeric (unsigned integers)</li>
<li><strong>history_str</strong> &#8211; character (up to 255 bytes)</li>
<li><strong>history_log</strong> &#8211; log</li>
<li><strong>history_text</strong> &#8211; text</li>
<li><strong>trends</strong> &#8211; numeric (float)</li>
<li><strong>trends_uint</strong> &#8211; numeric (unsigned integers)</li>
</ul>
<p>If you have a large environment, I recommend you to <strong>disable housekeeping</strong>, the explanation in <a title="History Tables – Housekeeper" href="../zabbix/history-tables-housekeeper/">this post</a>.</p>
<p><strong>Recommendations</strong></p>
<ol>
<li>Consider not more than <strong>7 days</strong> for history (<strong>3 days for medium</strong> environments)</li>
<li>Avoid intervals less than <strong>60 seconds</strong></li>
<li>Keep your eyes on <strong>disksize</strong> from history/trend tables</li>
</ol>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/queue-explained/" rel="bookmark" class="crp_title">Queue Explained</a></li><li><a href="http://zabbixzone.com/zabbix/history-tables-housekeeper/" rel="bookmark" class="crp_title">History Tables &#8211; Housekeeper</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li><li><a href="http://zabbixzone.com/zabbix/new-colors-on-frontend/" rel="bookmark" class="crp_title">New Colors on Frontend</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/history-and-trends/feed/</wfw:commentRss>
		<slash:comments>0</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/history-and-trends/</feedburner:origLink></item>
		<item>
		<title>Easy Update on Custom Scripts</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/qYFRpgO2bgU/</link>
		<comments>http://zabbixzone.com/zabbix/easy-update-on-custom-scripts/#comments</comments>
		<pubDate>Tue, 29 Mar 2011 01:32:45 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[agent]]></category>
		<category><![CDATA[scripts]]></category>
		<category><![CDATA[zabbix]]></category>
		<category><![CDATA[custom]]></category>
		<category><![CDATA[userparameter]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=46</guid>
		<description><![CDATA[Every time that you need to add or change an UserParameter on zabbix_agentd.conf you need to restart the agent. It&#8217;s easy if you have less than 10 servers, but could be a trouble if you have more than 30 servers. Automation Tools like CFEngine and Puppet do this work well done, but unfortunately they aren&#8217;t [...]]]></description>
			<content:encoded><![CDATA[<p>Every time that you need to add or change an <strong>UserParameter</strong> on <strong>zabbix_agentd.conf</strong> you need to restart the agent. It&#8217;s easy if you have less than 10 servers, but could be a trouble if you have more than 30 servers.</p>
<p>Automation Tools like <a href="http://www.cfengine.org" target="_blank">CFEngine</a> and <a href="http://projects.puppetlabs.com/projects/puppet/" target="_blank">Puppet</a> do this work well done, but unfortunately they aren&#8217;t used in many companies.</p>
<p>But it&#8217;s possible using a <strong>Dynamic UserParameter</strong>:</p>
<p>1) add two lines on <strong>zabbix_agentd.conf </strong>and <strong>restart the agent</strong>:</p><pre class="crayon-plain-tag"><code>UserParameter=custom.getvalue[*],/etc/zabbix/zabbix_agentd/custom_getvalue $1 $2 $3 $4 $5
UserParameter=custom.updatescript,/etc/zabbix/zabbix_agentd/custom_updatescript</code></pre>
<p>2) create the script file <strong>/etc/zabbix/zabbix_agentd/custom_getvalue</strong></p><pre class="crayon-plain-tag"><code>#!/bin/bash
ACTION=$1
PARAM1=$2
PARAM2=$3
PARAM3=$4
PARAM4=$5

case ${ACTION} in
# key: custom.getvalue[samplescript1,arg]
samplescript1)
  /bin/echo This is a test - ${PARAM1}
  ;;
# key: custom.getvalue[samplescript2,arg1,arg2]
samplescript2)
  /bin/echo This is another test - ${PARAM1} ${PARAM2}
  ;;
*)
  /bin/echo ZBX_NOTSUPPORTED
  ;;
esac</code></pre><p><p>Don&#8217;t forget to set execute permission:</p><pre class="crayon-plain-tag"><code>chmod 0755 /etc/zabbix/zabbix_agentd/custom_getvalue</code></pre><p>Now when you need create a new script, you must update this script and restart is unnecessary.</p>
<p><strong>Remote Updates</strong></p>
<p>Finally, let&#8217;s create a script to update <strong>custom_getvalue</strong> script remotely. Before make sure to publish your <strong>custom_getvalue</strong> script on a WebServer.</p>
<p>Create the file <strong>/etc/zabbix/zabbix_agentd/custom_updatescript</strong>:</p><pre class="crayon-plain-tag"><code>#!/bin/bash
/usr/bin/wget &quot;http://yourdomain.com/zabbix/custom_getvalue&quot; -O /etc/zabbix/zabbix_agentd/custom_getvalue -o /dev/null</code></pre>
<p>Don&#8217;t forget to set execute permission:</p><pre class="crayon-plain-tag"><code>chmod 0755 /etc/zabbix/zabbix_agentd/custom_updatescript</code></pre>
<p>Remote updates can be done from <strong>Zabbix Server/Proxy</strong> using <strong>zabbix_get</strong> utility:</p><pre class="crayon-plain-tag"><code>zabbix_get -s hostserver.yourdomain.com -k custom.updatescript</code></pre>
<p>It&#8217;s a contribution from <strong>laneovcc</strong>:</p>
<blockquote><p>in my way i config zabbix-agent.conf to include a UserParameter.conf then use system.run[wget http://server/UserParameter.conf -O /path/to/UserParameter.conf ] to update the UserParameter.conf and system.run[services zabbix-agentd restart] to restart the agent</p></blockquote>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/making-your-frontend-faster/" rel="bookmark" class="crp_title">Making your Frontend Faster</a></li><li><a href="http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/" rel="bookmark" class="crp_title">Backuping only the Zabbix Configuration</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li><li><a href="http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/" rel="bookmark" class="crp_title">MySQL performance tips for Zabbix</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/easy-update-on-custom-scripts/feed/</wfw:commentRss>
		<slash:comments>4</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/easy-update-on-custom-scripts/</feedburner:origLink></item>
		<item>
		<title>Making your Frontend Faster</title>
		<link>http://feedproxy.google.com/~r/zabbixzone/~3/5_vCfNOUPgk/</link>
		<comments>http://zabbixzone.com/zabbix/making-your-frontend-faster/#comments</comments>
		<pubDate>Sat, 26 Mar 2011 21:59:33 +0000</pubDate>
		<dc:creator>Ricardo Santos</dc:creator>
				<category><![CDATA[frontend]]></category>
		<category><![CDATA[zabbix]]></category>
		<category><![CDATA[eaccelerator]]></category>
		<category><![CDATA[faster]]></category>
		<category><![CDATA[php]]></category>

		<guid isPermaLink="false">http://zabbixzone.com/?p=26</guid>
		<description><![CDATA[A simple way to make your Zabbix Frontend faster is install a PHP cache on WebServer, and to do it I recommend eAccelerator: eAccelerator is a free open-source PHP accelerator &#38; optimizer. It increases the performance of PHP scripts by caching them in their compiled state, so that the overhead of compiling is almost completely [...]]]></description>
			<content:encoded><![CDATA[<p>A simple way to make your <strong>Zabbix Frontend</strong> faster is install a <strong>PHP cache</strong> on WebServer, and to do it I recommend <strong>eAccelerator</strong>:</p>
<blockquote><p>eAccelerator is a free open-source PHP  accelerator &amp; optimizer. It increases the performance  of  PHP   scripts  by caching them in their compiled state, so that the overhead  of  compiling  is  almost completely eliminated. It also optimizes   scripts  to speed  up their execution. eAccelerator typically reduces  server load and increases  the speed of your PHP code by 1-10 times.</p></blockquote>
<p>For complete documentation and information, please visit <a title="PHP eAccelerator" href="http://eaccelerator.net" target="_blank">http://eaccelerator.net/</a></p>
<p><strong>Installation Tips</strong></p>
<ol>
<li>Don&#8217;t forget to <strong>restart</strong> the WebServer daemon (eg. Apache)</li>
<li>Source installation requires <strong>phpize</strong> utility, you can find it on <strong>php-devel</strong> package</li>
<li>Make sure that it&#8217;s all fine looking for eAccelerator information on a <strong>phpinfo()</strong> page</li>
</ol>
<p><strong>GZip Compression</strong></p>
<p>Many pages on Zabbix outputs very large HTML content, using gzip compression (<a href="httpd.apache.org/docs/2.2/mod/mod_deflate.html" target="_blank">mod_deflate</a>) is possible to reduce the network traffic significantly. On a simple test I reduced my Overview from 23k to 4k.</p>
<p>This is a cookbook configuration for Apache 2.2:</p><pre class="crayon-plain-tag"><code>&lt;Location /zabbix&gt;
# Insert filter
SetOutputFilter DEFLATE

# Netscape 4.x has some problems...
BrowserMatch ^Mozilla/4 gzip-only-text/html

# Netscape 4.06-4.08 have some more problems
BrowserMatch ^Mozilla/4\.0[678] no-gzip

# MSIE masquerades as Netscape, but it is fine
BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
# Don't compress images
SetEnvIfNoCase Request_URI \
\.(?:gif|jpe?g|png)$ no-gzip dont-vary

# Make sure proxies don't deliver the wrong content
Header append Vary User-Agent env=!dont-vary
&lt;/Location&gt;</code></pre>
<p><strong>Benchmark Tests using AB (Apache Benchmark)</strong></p>
<ul>
<li><strong>with eAccelerator</strong></li>
</ul>
<pre class="crayon-plain-tag"><code>[root@localhost ~]# ab -c 50 -n 500 http://localhost/zabbix/overview.php
Time taken for tests:   7.363274 seconds</code></pre>
<ul>
<li><strong>without eAccelerator</strong></li>
</ul>
<pre class="crayon-plain-tag"><code>[root@localhost ~]# ab -c 50 -n 500 http://localhost/zabbix/overview.php
Time taken for tests:  15.781082 seconds</code></pre>
<div id="crp_related"><h3>Related Posts:</h3><ul><li><a href="http://zabbixzone.com/zabbix/easy-update-on-custom-scripts/" rel="bookmark" class="crp_title">Easy Update on Custom Scripts</a></li><li><a href="http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/" rel="bookmark" class="crp_title">Backuping only the Zabbix Configuration</a></li><li><a href="http://zabbixzone.com/zabbix/zabbix-git-repositor/" rel="bookmark" class="crp_title">Zabbix Git Repository</a></li><li><a href="http://zabbixzone.com/zabbix/partitioning-tables/" rel="bookmark" class="crp_title">Partitioning Tables</a></li></ul></div>]]></content:encoded>
			<wfw:commentRss>http://zabbixzone.com/zabbix/making-your-frontend-faster/feed/</wfw:commentRss>
		<slash:comments>2</slash:comments>
		<feedburner:origLink>http://zabbixzone.com/zabbix/making-your-frontend-faster/</feedburner:origLink></item>
	</channel>
</rss><!-- Performance optimized by W3 Total Cache. Learn more: http://www.w3-edge.com/wordpress-plugins/

Served from: zabbixzone.com @ 2012-02-05 12:24:41 -->

