Introduction

Over a period of time, FrontAccounting's MySQL tables accumulate a lot of deleted records. Purging the "marked for deletion" records will shrink the table space, re-index / defragment it and make it faster in operation.

Monitor the MySQL Server errors

When a large number of and/or long running queries are being executed, it is important to monitor whether resource provided are being reached / exceeded possibly resulting in "MySQL Server has gone away" messages.

Monitor the MySQL server with (change path to suit your install):

tail -fn0 /var/log/mysql.err

Wait state %age can be ascertained with:

vmstat 5 5

which will result in output like: 

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  0  64652 204076      0 817012    0    0     1     9    0   22  0  0 100  0

Reindex tables

  • ALTER TABLE mytable FORCE;
  • ALTER TABLE mytable ENGINE=INNODB;

Both the above commands will result in defragmenting the specified table. As of MySQL 5.7.4, both commands use online DDL (ALGORITHM=COPY).

Optimize all tables

To optimize all tables in a MySQL database, the following code can be copy/pasted and executed in any MySQL client but not in phpMyAdmin. 

USE mydb;

SET @a=null,@c=null,@b=concat("show tables where",ifnull(concat(" `Tables_in_",database(),"` like '",@c,"' and"),'')," (@a:=concat_ws(',',@a,`Tables_in_",database(),"`))");

Prepare `bd` from @b;
EXECUTE `bd`;
DEALLOCATE PREPARE `bd`;

set @a:=concat('optimize table ',@a);
PREPARE `sql` FROM @a;
EXECUTE `sql`;
DEALLOCATE PREPARE `sql`;

set @a=null,@b=null,@c=null;

Control which tables to optimize by setting a different value in the var @c which holds the table name pattern.

MySQL Tuning

Check the current status of the tables with

mysqlcheck -o mydbname

mysqltuner can be used to obtain tentative parameter values to use in the my.cnf file. Install in Debian with apt-get install mysqltuner . Windows users take the binaries from GitHub.

At the end of the output, recommendations will be provided like: 

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    query_cache_size (> 16M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 64)