Developers guide

Chapter 9

Performance optimization 75

# uptime

12:01:58 up 235 days, 1:46, 1 user, load average: 0.15, 0.39, 0.52

# iostat 5

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn

cciss/c0d0 8.00 0.00 75.20 0 376

avg-cpu: %user %nice %sys %iowait %idle

0.60 0.00 2.99 13.37 83.03

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn

cciss/c0d0 14.97 0.00 432.73 0 2168

avg-cpu: %user %nice %sys %iowait %idle

0.20 0.00 2.60 6.60 90.60

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn

cciss/c0d0 4.80 0.00 48.00 0 240

MySQL optimization tool

We recommend that you run the MySQL Tuner tool on your database setup after a couple of weeks to

help you identify MySQL configuration improvement.

http://blog.mysqltuner.com/download/

Keeping tables small

Over time, some of the tables will grow large and this will drag down performance (this is especially

true on a wireless setup).

One such table is the locationlog table. We recommend that closed entries in this table be moved to

the archive table locationlog_history after some time. A closed record is one where the end_time

field is set to a date (strickly speaking it is when end_time is not null and not equals to 0).

We provide a script called database-backup-and-maintenance.sh located in addons/ that performs

this cleanup in addition to optimize tables on Sunday and daily backups.

Avoid "Too many connections" problems

In a wireless context, there tends to be a lot of connections made to the database by our freeradius

module. The default MySQL value tend to be low (100) so we encourage you to increase that value to at

least 300. See http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html for details.

Avoid "Host <hostname> is blocked" problems

In a wireless context, there tend to be a lot of connections made to the database by our freeradius

module. When the server is loaded, these connection attempts can timeout. If a connection times out

during connection, MySQL will consider this a connection error and after 10 of these (by default) he will

lock the host out with a: