MySQL Tuning: Is Defragmenting Tables safe?

Question

I like using MySQLTuner to check on things from time to time. It's as easy as:

$: sudo apt-get install mysqltuner (CentOS: sudo yum install mysqltuner)

$: sudo mysqltuner

Also, besides 'top' which is the default monitor for the server's processes and hardware, install 'mytop' exclusively for monitoring MySQL. Excellent tool to watch all your databases at once.

I have never performed OPTIMIZE_TABLES to defragment for a CiviCRM installation before. Is it safe? Of course, do a robust backup first, but want to know before to expect if there is likelihood of failure. No one wants more work or downtime than necessary. Also, please advise on the other two suggestions. All noted with arrows. Thanks in advance. Here are the results:

General recommendations:

    =>Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    =>Adjust your join queries to always utilize indexes
Variables to adjust:

    =>join_buffer_size (> 128.0K, or always use indexes with joins)

Full Report:

 >>  MySQLTuner 1.0.1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.38-1~dotdeb.0
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 17M (Tables: 203)
[--] Data in InnoDB tables: 53M (Tables: 841)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 10)
[!!] Total fragmented tables: 861

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 25m 6s (7K q [1.402 qps], 270 conn, TX: 11M, RX: 1M)
[--] Reads / Writes: 64% / 36%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (29% of installed RAM)
[OK] Slow queries: 0% (21/7K)
[OK] Highest usage of available connections: 5% (8/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.9M
[OK] Key buffer hit rate: 98.8% (10K cached / 121 reads)
[OK] Query cache efficiency: 51.1% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 417 sorts)
[!!] Joins performed without indexes: 151
[OK] Temporary tables created on disk: 16% (127 on disk / 788 total)
[OK] Thread cache hit rate: 97% (8 created / 270 connections)
[OK] Table cache hit rate: 96% (234 open / 242 opened)
[OK] Open file limit used: 9% (102/1K)
[OK] Table locks acquired immediately: 99% (3K immediate / 3K locks)
[OK] InnoDB data size / buffer pool: 53.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)

Answer 1

The suggestion to increase the join_buffer_size is a good one if your server can afford the 128K. The number of queries with joins on unindexed fields, which need more cache, is not insignificant.

(Background: There are write performance downsides to putting an index on every field that might have a join put on it. I believe in a few places in CiviCRM there have been some deliberate decisions to not put indexes on fields, I bet where the record count on the join is small so the performance hit on reads wouldn't be significant. However, adding and updating indexes is also an area where there may have been some oversights over the years, especially in the code that updates the schema from version to version.)

If you follow the suggestion to turn on the slow query log make sure you:

1. logrotate it,
2. don't set the threshold too low (maybe 2 or 5 seconds), and
3. monitor it.

If you do find there are some queries that would benefit from an additional index being added to a table, please open an issue in CiviCRM. It's often helpful to know what the original version of the database was so we can track down if the error was in the original tarball or in an upgrade script.

If you are deleting a fair bit regularly from your CiviCRM database it's a good idea to run OPTIMIZE_TABLES regularly. But since most CiviCRM installations don't delete much, you won't likely see significant benefit from doing it more than every several months or year or two. Sometimes the full workflow of migrating a database may include mass deletes, so that can juatify it too. Larger databases with hundreds of thousands or more of contacts and transactions will see the biggest benefits.

If you do want to do this, then make sure to take CiviCRM offline beforehand in order to avoid deadlocking your DB.

Running OPTIMIZE_TABLES will result in each table having a write lock put on it, then it will be recreated and reindexed. After the reindexing any changes to its records that have been queued will be applied, before the next table is optimized. It is possible for a database deadlock to occur (for example, if a row in a table is locked by another process while it waits to acquire a lock on a second row). Normally InnoDB is good at automatically detecting deadlocks and dealing with them with row level locking. However, InnoDB is not able to automatically detect deadlocks in the case of a table level lock. I don't foresee issues otherwise with the database defragmentation of table rows and index entries, especially if you've done your backups first.

Answer 2

Yes, running OPTIMIZE TABLES on a regular basis is beneficial. I don't think there is significant risk with it. I'd run it on a copy of the DB first.

If you have the memory on your database server increasing the cache and buffer sizes of MySQL is a good thing, IMO.

In the past, I've found the Percona blog quite a useful resource. Also some folks (NYSS) have been using the Percona distribution for improved performance.