Table Partitioning

[kayjay]

My 1.8.8 zabbix has reached over 250 nv/ps and the history tables are becoming a bottleneck. It looks like table partitioning is the answer to this problem. Now that V2.xx is out and settled down I am temped to upgrade but am concerned about performance as I understand that I cannot use the same partitioning method on V2.xx. Am I likely to be better off sticking with V1.8.8 and partitioning or move to V2.xx without it?

Hosts = 2561
Triggers = 15036
New values per second = 252.85
MySQL on separate server with 32GB ram & 6 CPU's

Many Thanks

[Colttt]

its reaky the bottleneck?? do you tune the mysql-parameter?

do you use tuning-primer.sh?

how big is you database?

[kayjay]

Yes I have tuned the SQL parameters. It is difficult because every post has a different opinion about the best parameters - do you have a definitive list?. I also use 'mysqltuner.pl' but never heard of tuning-primer.sh - what is it?

The zabbix database is about 60GB. The housekeeper process is at 100% most of the time.

Your English is fine

Thanks

[Colttt]

Your English is fine

really?! THANKS!!!!!!!!!

the tuning-primer.sh-script http://www.day32.com/MySQL/, was written by an mysql-developer.. its more detailed than mysqltuner.pl ..

do you use RAID10 and fast SAS-HDD?! with 15k/rpm? mybe you can use SSD, instead. or try flashcache with 1-2SSDs..

turn the housekeeperprocess off!! and write a little script that every week launch.. (delete all entries older than 200days <- its simplescript)

[kayjay]

Ok I ran the tuning-primer.sh-script and my config looks quite good.

The problem as I see it is that if turn off housekeeping and use a script that deletes old items from history then the dbase will still grow very large over time because InnoDB does not shrink the dbase on delete. In V1.8.8 I can partition the history tables to avoid this but I understand that I cannot do this with V2.xx - is this true? How do others get large new-values-per-second - I am struggling with > 250.

Thanks

[Colttt]

you must first migrate to zabbix 2.0.x and AFTER this you can partitioning you tables!

[kayjay]

OK...

The info about partitioning 1.8.xx states that this does not work in 2.0.xx - is this incorrect? or is there a new method for partitioning on 2.0.xx?

Thanks

[Colttt]

tha partition method is the same (is a mysql-partition) but the zabbix-scripts to transfer from 1.8 to 2.0 dont work with partitions!

[tchjts1]

My understanding is that you cannot partition MySql on 2.x because of foreign keys.

[f.koch]

My understanding is that you cannot partition MySql on 2.x because of foreign keys.

Thats correct

[Jason]

If you cannot partition then what other ways can you clear out old data and tidy the the database without taking them offline with optimize tables?

[f.koch]

If you cannot partition then what other ways can you clear out old data and tidy the the database without taking them offline with optimize tables?

for the cleanup is the zabbix housekeeper , but this works only good for database sizes <= 100 GB (depends on Hardware, and some other things) but for the optimitze tables, i don't know if this work online.

maybe you should look on another databese, with postgresql , partitions are possible.

regards Flo

[Colttt]

If you cannot partition then what other ways can you clear out old data and tidy the the database without taking them offline with optimize tables?

migrate to postgresql!!

[Jason]

How does postgres compare in performance terms with MySQL? I'm looking at postgres for when we move from 1.8 to 2.0 but want to make sure the load levels are the same...

Is the recommendation still to disable housekeeping with postgres (as it is with mysql) and then use sql maintenance jobs to keep the data in check? Also I'm guessing need to be careful with the postgres 'vacuuming' on the large database to make sure performance isn't impacted too much?

[kayjay]

Yes - advice from anyone with experience at high loads would be very welcome. The maintenance issue looks to be by far the biggest challenge. I'm torn between upgrading to V2.0.xx and moving to PerconaDB or Postgres or staying with V1.8.8 and sticking with MySQL and db partitioning. Some examples of optimum maintenance procedures with V2.0.xx would help greatly.