Question about partitioning

[SacreCoeur]

Hi there,

I'm very new to mysql so please don't be hard on me

Configuration info:

• zabbix_server (Zabbix) 3.0.10; Revision 70208 14 July 2017, compilation time: Jul 17 2017 07:42:06
• MySQL Server version: 5.5.56-MariaDB MariaDB Server
• InnoDB Plugin version: 5.5; Plugin_type_version: 50556.0

I've made partitions for quite similar configuration using this tutorial:

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

Zabbix database tables were plump so the main process (partition_maintenance_all) took me about 5,5 hours.
Now I need to do the same with a four times bigger zabbix database and here comes some pain because I can't afford my prod environment to stay down for such a long period of time.
Is there a way to create new partitions, let my zabbix-server start using only these ones while I create some back-in-time partitions and split main tables into them as a backgroud process? I know it sounds like a crazy moon language. My only purpose is zabbix server up and running as soon as possible after the future partitions are created. I appreciate any idea on this topic and ready to provide more information.

Thanks,
Maria.

[kaspars.mednis]

Hi Maria,

One way of migration to partitioned tables is add slave mysql DB and do on slave all partitioning. When you will have tables partitioned all what must be done is:
- stop zabbix server and web frontend,
- be sure that on master and slave you have the same binary position,
- stop master and start using slave as new master.
- start zabbix server and web frontend,
- then you can do same on the old master

But that requires a second DB server....

Other thing i suggest to check -

Reading what is new in mysql 5.6 http://dev.mysql.com/doc/refman/5.6/...-nutshell.html I found:

"InnoDB enhancements. These InnoDB enhancements were added:
[..]
Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL. See Section 14.10, “InnoDB and Online DDL” for details."

Seems like with 5.6 may be possible to partition table straight on master db.
Not sure what version of mariadb it is, but you can check this

Anyway, those are just sugestions, better test it on some test DB, i have never done it by myself

Regards,
Kaspars

[SacreCoeur]

Dear Kaspars,

thank you very much for your reply.
Gonna check it next week and post back about the results.

Regards,
Maria