Mysql Database Grow | How Optimize parameters

[Kerrygeek]

MUCH better

Guys,

I've been fighting with Zabbix being very slow, so slow that it might take 30-60 seconds to refresh a screen, for several months now since I added some new switches. My load average was high but the web server was working fine. I don't know beans about Mysql but I suspected that's where the problem was. I found on this thread a posting of a sample Mysql config file and it made all the difference in the world! I had the syntax wrong on a couple of lines like the innoDB_buffer_pool_size so it was using the default which is way too small for my system. I have 4 gigs of ram so I changed it to 2gigs and it made an unbelievable difference! I'll attach a screenshot of my cpu utilization on my Zabbix server, now I can have several Zabbix screens open and it doesn't change the performance at all. Several other people are also using it now that it's not painful to use.

So, when you guys helped Pierre-Hoffman it also benefited me, thanks for your help. Seeing his my.cnf helped a lot.

If anybody else is having the same problem, run the mysqltuner script and tune the my.cnf file, it made all the difference in the world.

Thanks,
Kerry

[coldblooded1]

thanks! I had the same problem. With your help, guys, I was able to fix it. Thanks again!

[ittec]

Confirmed, less space of database used after mysqlcheck

when a full table scan is needed (often if it's done by the webinterface).[*]Dump/restore db
In relation to the previous point, if you are using the 'one-file-for-all-dbs-tables' default setting for InnoDB, you can only clear out all empty records by dumping all databases, remove the innodb data files and restore all databases in a fresh mysql data instance.
You could try this during some maintenance period and see how many gig's this will save, i think lots

Hi all

I'm trying to improve Zabbix Server before to do an upgrade to the lastest version. I did a

mysqlcheck -o

before anything. This is the output of space used by zabbix before to do the mysqlcheck

-bash-3.2# du -hs *
105M ibdata1
65M ib_logfile0
65M ib_logfile1
768K mysql
0 mysql.sock
4.0K test
46G zabbix

after mysqlcheck the spaced used by zabbix database is

-bash-3.2# du -hs *
105M ibdata1
65M ib_logfile0
65M ib_logfile1
768K mysql
0 mysql.sock
4.0K test
37G zabbix

Great! Now Im dumping database to move it to another mysql isolated instance and will see if, after dumping process, more space is liberated.

Thanks people

[nima0102]

Hi
After some investigation on zabbix's database I found out few points that I will argue on this post.
One of the most important parameters in database designing is data type selection.creation fields with size that are bigger than our needs.it's mean wasting disk space,more I/O on read.and lose more resources on our server and finally poor performance!
Monitoring tools are critical component in network,so any false result can result in to make wrong decision.
Unfortunately in zabbix's database almost data type are int(11),bigint(20),varchar(64).
For example I check history table,because this table probably has more record than another table and more query is played on this table.
History table :
itemid | bigint(20) unsigned
clock | int(11)
value | double(16,4)

bigint unsigned fit 0 up to 18446744073709551615 and 8 bytes on storage!!
Does zabbix really want add 18446744073709551615 items?! for itemid can use mediumint unsigned that fit 0 up to 16777215 and use 3 bytes on storage.
So we can calculate wasted space with this data type selection:
If we have 100000000 record in history table, we waste 476MB(100000000*5/1024/1024) on disk space.itemid there are in other tables.Of course this statistic is for only one table.in other tables there are many fields with type bigint or int that do not need such space.
According to mysql recommendation, it's better select type of clock or time in mysql as timestamps that provides better performance on searching based on time.
Another problem is related to some queries.
If you add zabbix[history] item, zabbix generate "select count(*) from history".suppose your history table has 50,000,000 records.how long does this query take?how much load does this query generate on your system?how many I/O?!

Database design in zabbix is not sufficient
Of course I installed zabbix as primary monitoring solution and thank to all developer and contributors on this forum.

[fmrapid]

Database schema

Nema,

Using appropriately size variables is a very good point. A complimentary approach to using correctly sized variables is compression.

Database table compression that is now available in MySQL and Postgres. This may make it easier on the read speed and throughput and storage size of things. See http://www.zabbix.com/forum/showthread.php?t=19600

fmrapid

[pearlcabrezos]

I had the same problem also.

[g.vecchi]

Hi everybody.
I have a question: can I schedule MySQL optimization while Zabbix Server is running?

Thanks!

[MetalER]

Consider using PostgreSQL

I switched from mysql to postgresql some while ago, for the same reason. Using PostgreSQL 8.4 and 9.02 for some time now and didn't run into space problems anymore.

Maybe not the answer you were waiting for, but one to consider using.

[urly]

I had the same issue with the MySql DB size as the OP. I had to export the DB, turn on the innodb_file_per_table, import the DB and then I used the following procedure to clean up the DB:

http://pulex.linuxbabbel.org/2014/cleaning-up-the-zabbix-database.html

I reduced the DB from 30G to 4G.

I guess it would be a good idea at least to do the partitioning of the tables which can grow as fast as history_uint table does.

-urly

[benjamin_regnier]

36 GB is not a huge database.

Currently I have a 250 GB database with no slowquery

[pzabortsev]

Currently I have a 250 GB database with no slowquery

Could you tell us which hardware do you use for mysql?

and which mysql version and configuration parameters do you use?

[Cardplugin]

Finally I could figure out the problem.

Thanks a lot

[vlam]

I have an similar DB issue where I need to backup my environment but cannot have any downtime. My HA is broken so I need the backup to get that sorted also.

My problem is I have an 1.5TB DB running at this stage

Any help in how to do this with minimal (preferable NO) downtime.

[pnenov]

MySQl schema

the main reason for poor Zabbix performance is the table items
that table is used virtually in each and every mysql query

items | CREATE TABLE `items` (
`itemid` bigint(20) unsigned NOT NULL,
`type` int(11) NOT NULL DEFAULT '0',
`snmp_community` varchar(64) NOT NULL DEFAULT '',
`snmp_oid` varchar(255) NOT NULL DEFAULT '',
`hostid` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
`key_` varchar(255) NOT NULL DEFAULT '',
`delay` int(11) NOT NULL DEFAULT '0',
`history` int(11) NOT NULL DEFAULT '90',
`trends` int(11) NOT NULL DEFAULT '365',
`status` int(11) NOT NULL DEFAULT '0',
`value_type` int(11) NOT NULL DEFAULT '0',
`trapper_hosts` varchar(255) NOT NULL DEFAULT '',
`units` varchar(255) NOT NULL DEFAULT '',
`multiplier` int(11) NOT NULL DEFAULT '0',
`delta` int(11) NOT NULL DEFAULT '0',
`snmpv3_securityname` varchar(64) NOT NULL DEFAULT '',
`snmpv3_securitylevel` int(11) NOT NULL DEFAULT '0',
`snmpv3_authpassphrase` varchar(64) NOT NULL DEFAULT '',
`snmpv3_privpassphrase` varchar(64) NOT NULL DEFAULT '',
`formula` varchar(255) NOT NULL DEFAULT '',
`error` varchar(2048) NOT NULL DEFAULT '',
`lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',
`logtimefmt` varchar(64) NOT NULL DEFAULT '',
`templateid` bigint(20) unsigned DEFAULT NULL,
`valuemapid` bigint(20) unsigned DEFAULT NULL,
`delay_flex` varchar(255) NOT NULL DEFAULT '',
`params` text NOT NULL,
`ipmi_sensor` varchar(128) NOT NULL DEFAULT '',
`data_type` int(11) NOT NULL DEFAULT '0',
`authtype` int(11) NOT NULL DEFAULT '0',
`username` varchar(64) NOT NULL DEFAULT '',
`password` varchar(64) NOT NULL DEFAULT '',
`publickey` varchar(64) NOT NULL DEFAULT '',
`privatekey` varchar(64) NOT NULL DEFAULT '',
`mtime` int(11) NOT NULL DEFAULT '0',
`flags` int(11) NOT NULL DEFAULT '0',
`interfaceid` bigint(20) unsigned DEFAULT NULL,
`port` varchar(64) NOT NULL DEFAULT '',
`description` text NOT NULL,
`inventory_link` int(11) NOT NULL DEFAULT '0',
`lifetime` varchar(64) NOT NULL DEFAULT '30',
`snmpv3_authprotocol` int(11) NOT NULL DEFAULT '0',
`snmpv3_privprotocol` int(11) NOT NULL DEFAULT '0',
`state` int(11) NOT NULL DEFAULT '0',
`snmpv3_contextname` varchar(255) NOT NULL DEFAULT '',
`evaltype` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`),
UNIQUE KEY `items_1` (`hostid`,`key_`),
KEY `items_3` (`status`),
KEY `items_4` (`templateid`),
KEY `items_5` (`valuemapid`),
KEY `items_6` (`interfaceid`),
CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,
CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
CONSTRAINT `c_items_3` FOREIGN KEY (`valuemapid`) REFERENCES `valuemaps` (`valuemapid`),
CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


the statement:

UNIQUE KEY `items_1` (`hostid`,`key_`)

makes impossible this table to be partitioned
second the unique index can not be cached
third the key_ column contents strings which in some occasions are might be very large, including such a column in a unique index (or any other index) is not exactly a very good idea

this table is not easy to be redesigned and as long as it stays this way the performance of zabbix will deteriorate severely in exponential progression regarding the number of records in table items

[aaditya]

For reply this

In your role as a database designer, look for the most efficient way to organize your schemas, tables, and columns. As when tuning application code, you minimize I/O, keep related items together, and plan ahead so that performance stays high as the data volume increases. Starting with an efficient database design makes it