Database Optimisation¶

MySQL performance¶

First, ensure yourself that /var/lib/mysql is a separate partition, mounted with noatime.

On many linux distributions, the default MySQL config is suited for a very low-end machine (32 MB of RAM). You have to configure it in /etc/mysql/my.cnf, for ex:

innodb_data_home_dir=/db/disk2/data
innodb_data_file_path=ibdata1:10240M;ibdata2:10240M;ibdata3:10240M;ibdata4:10240M;ibdata5:10M:autoextend
innodb_log_group_home_dir=/var/log/mysql
innodb_log_arch_dir=/var/log/mysql
innodb_table_locks=0
innodb_buffer_pool_size=1800M # USE ALL MEMORY AVAILABLE
#innodb_log_buffer_size=8M # Lowered from 32M according to [[MySQL]]
innodb_additional_mem_pool_size=20M

Disable binary logs, unless you have a good reason to keep them (like using replication or PITR). Just comment the line in the config file:

log_bin = /var/log/mysql/mysql-bin.log

Important things:

When removing data, remember that MySQL does not delete anything from disk from an InnoDB partition, and that you can't reclaim free space ! Problem is known, but nothing was done to fix it (See comments in http://bugs.mysql.com/bug.php?id=1287 and http://bugs.mysql.com/bug.php?id=1341 )
There is no vacuum in MySQL, but you can use the OPTIMIZE TABLE command to do the same. However, remember this command will lock the table during its execution, and can take some time.

See

PostgreSQL performance¶

First, ensure yourself that /var/lib/postgresql is a separate partition, mounted with noatime.

Here's some tips for performance:

shared_buffers: this is the most important parameter. On a dedicated server, raise it to 75-80% of the total RAM (you may need to change the shmmax sysctl value).
work_mem: this is the amount of memory used for internal operations like sorting and hashing. The default is 1MB, you can raise it a bit (between 32MB and 64MB). Note that each connexion can use this amount of memory, so do not set the value too high.
Raise default_statistics_target to something like 100 (it must be between 1 and 1000, default is 10). This parameter controls how the query planner will decide to choose a plan or another, and can lead to serious performance problems if too low
join_collapse_limit: this parameters controls how deep the query planner will search and try to reorder JOIN instructions. Prelude uses many JOINs, so you should raise this to 12 or 15 (default is 8)
remember to vacuum regularly !
Depending on your data, you may need to change max_fsm_relations and max_fsm_pages

See

Remove heartbeats¶

Heartbeats are logged for every sensor, and there are tons of them. This can completely cripple performances (especially when using Prewikka) of the database, so you should remove them. Writing a simple shell script, executed by a cron job every day, is a trivial task:

#!/bin/sh

set -e

DB_TYPE="pgsql" 
DB_HOST="localhost" 
DB_USER="prelude" 
DB_PASS="xxxxxx" 

KEEP_INTERVAL="2 month" 

DATE=$(date -d "now - $KEEP_INTERVAL" +%Y-%m-%d)

preludedb-admin delete heartbeat --criteria "heartbeat.create_time <= $DATE" "type=$DB_TYPE host=$DB_HOST user=$DB_USER pass=$DB_PASS"

Clean up old alerts¶

Note: Be careful ! Deleting old entries may remove important data.

The preludedb-admin command can be used to suppress alerts, based on some conditions (called criteria).

# preludedb-admin delete --alert-criteria "alert.create_time <= 2007-06-15" "type=pgsql user=prelude pass=<db_password>"

With recent versions, the syntax has changed:

# preludedb-admin delete alert --criteria "alert.create_time <= 2007-08-15" "type=mysql user=prelude pass=<db_password>"

This command will delete all alerts prior to 2007-06-15

If you are using MySQL and got the following error:

retrieving alert ident failed: The total number of locks exceeds the lock table size.

Then edit MySQL configuration file and try increasing innodb_buffer_pool_size.

See http://mrothouse.wordpress.com/2006/10/20/mysql-error-1206/ for some details.

Replicating a database to another¶

The preludedb-admin tool has a copy mode.

# preludedb-admin copy alert "type=pgsql name=prelude user=prelude pass=***** host=192.168.1.101" "type=mysql name=prelude user=prelude pass=****** host=192.168.1.104" 
# preludedb-admin copy heartbeat "type=pgsql name=prelude user=prelude pass=***** host=192.168.1.101" "type=mysql name=prelude user=prelude pass=****** host=192.168.1.104"

Project

General