Galera Innoptimizer: optimize tables without stress

| 4 Comments

0.00 avg. rating (0% score) - 0 votes

Galera-Cluster-logo

I recently been faced on a classical problem on InnoDB which is the fragmentation, but on Galera. InnoDB engine doesn’t defragment on the fly and requires optimize maintenance sometimes to free disk space. But on Galera, which is a fault tolerance and high availability solution, it’s a problem having tables locked by an optimize procedure. Until Galera doesn’t support TokuDB and only fully support InnoDB, we had (with a colleague (Kevin aka Vinek)) to find a solution. We found that changing RSU mode is the way to achieve it.

That’s why I made a tool to help you to perform all the necessary actions to optimize tables in the most easy and secure way. Running an optimize on InnoDB tables in a Galera cluster will launch an optimize on every nodes. This is usually a big problem when using huge tables. This tool makes online InnoDB optimize on a Galera single node without tables lock feeling on the user side. But do not think this is magical as there is a counterpart: you have to reduce the write queries during that time or the optimize will fail with a “dead lock” error message.

Galera has an OSU mode called RSU which permit to perform online schema upgrades. This mode is used by Galera Innoptimizer to perform optimize on tables. The advantage of that solution is you can still perform write (and read of course) updates while running . It won’t be replicated on any nodes, simply running locally.

Here is how it works:

To know more about the solution and to get it, please follow the link of the project.

Of course this is the first version, I’d like to add more things like database exclude, table include/exclude, percentage of progression etc…Do not hesitate to give feedbacks on it 🙂

Author: Deimos

I'm a passionate DevOps. I love transmit my skills and I love working on high availability infrastructures/technologies.

4 Comments

  1. thank you for this tool. unfortunately i get an error while running under 5.5.40-MariaDB-1~wheezy-wsrep-log mariadb.org binary distribution, wsrep_25.11.r4026

    the error is:
    [+] Trying to connect to MySQL/MariaDB instance…OK
    [+] Checking current Galera state…
    -> wsrep_ready status…OK
    -> wsrep_cluster status…OK
    -> wsrep_connected status…OK
    -> wsrep_desync status…OK
    -> wsrep_OSU_method status…OK
    [+] Getting list of all tables in XXXXX database…OK
    [+] Enabling RSU mode…OK
    [+] Starting optimization on XXXXX database…
    -> wsrep_flow_control_paused status > 0.3…OK
    Traceback (most recent call last):
    File “./ginnoptimizer.py”, line 462, in
    main()
    File “./ginnoptimizer.py”, line 459, in main
    args()
    File “./ginnoptimizer.py”, line 450, in args
    optimize_rsu(database, tables_list, fcpmax)
    File “./ginnoptimizer.py”, line 203, in optimize_rsu
    launch_sql_queries(table)
    File “./ginnoptimizer.py”, line 174, in launch_sql_queries
    partitions = ptables[0][3].split(‘,’)
    AttributeError: ‘NoneType’ object has no attribute ‘split’

Laisser une réponse