Strategies to improve database performance

[programarivm]

The chess database is running on an Ubuntu Server that only has 1 GB of RAM and 8 GB of disk space.

While it may be too early to draw any conclusions, it seems that the games table is now more efficient with a MyISAM engine than with InnoDB. No inserts or updates of any kind are performed on this table. Since it is only used to query the data, the primary key has been removed along with the id column. For further details please check out the cli/db-create.php script in the Chess Data repo which is responsible for creating the database.

At this moment, the games table contains 259734 records.

mysql> select count(*) from games;
+----------+
| count(*) |
+----------+
|   259734 |
+----------+
1 row in set (0.00 sec)

And it is taking about 66 MB of disk space.

mysql> SELECT (data_length+index_length)/power(1024,2) tablesize_mb
    -> FROM information_schema.tables
    -> WHERE table_schema='chess' and table_name='games';
+-------------------+
| tablesize_mb      |
+-------------------+
| 66.73174667358398 |
+-------------------+
1 row in set (0.14 sec)

The df command shows the disk space usage.

df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       6.8G  5.1G  1.7G  75% /
tmpfs           479M     0  479M   0% /dev/shm
tmpfs           192M  1.1M  191M   1% /run
tmpfs           5.0M     0  5.0M   0% /run/lock
/dev/xvda16     881M  133M  687M  17% /boot
/dev/xvda15     105M  6.1M   99M   6% /boot/efi
tmpfs            96M   12K   96M   1% /run/user/1000

Hopefully with the new set up in docker/mysql/conf.d/custom.cnf, the MySQL server shouldn't go away because of 75% disk space usage in /dev/root.

See:

• SQLSTATE[HY000]: General error: 2006 MySQL server has gone away chess-server#377

Keep it up,