Linux System Admins Blog

Here’s an interesting one, what if you have a MySQL replication setup and the slave stops replicating with a syntax error? The slave should be executing the exact same commands as the master, right? Well, as it turns out, yes and no. There is a bug in MySQL that has been fixed in 5.0.56 according [...]

[Read more →]

We recently came across a problem with Cacti and the MySQL counters. For those of you who don’t know how to integrate MySQL statistics into Cacti have a look at this: http://code.google.com/p/mysql-cacti-templates/. These templates are a great way to gain some insight into how your MySQL database servers perform. The templates are actually PHP pages [...]

[Read more →]

MySQL has a number of different caches. Most of those are dependent on the storage engine that is used. The key buffer for example caches the indexes for MyISAM tables while the caching of data is left to the OS. InnoDB has the buffer pool for both data and indexes and so on. The query [...]

[Read more →]

We’ve been investigating the possibilities of using cloud computing for our clients. Especially Amazon EC2 has the potential to be be really effective in offering flexible, pay-as-you-go computing. From my own perspective I have been looking at how to use cloud computing in combination with MySQL and I must say that I’m a bit sceptical [...]

[Read more →]

Andy posted some very good links recently to video’s on how to optimize your web site. Although I spend more time optimizing the database you always have to go where the actual performance is lost. For MySQL the place to check for performance issues is the slow query log which I have mentioned in earlier [...]

[Read more →]

It’s a bit of a long title for a blog post but the point I want to make is that not every query optimization is aimed at making the query faster. As a case in point we have a client that has a web shop and their network traffic between the web servers and the [...]

[Read more →]

If you can’t do it with a shell script it usually ain’t worth doin’, right? Of course the number and quality of monitoring tools available to sys admins has gone up dramatically. Thanks to Nagios and other great tools it’s pretty easy to keep track of what’s going on and where and get notified pretty [...]

[Read more →]

We are using a replication setup for our databases. The master database takes care of all the transactions and the slave database is used for hot standby, making backups and running reports. Running mysqldump for backups locks the main tables one by one (we’re using the MyISAM storage engine) and was causing user interruptions. Hence [...]

[Read more →]

We encountered a situation recently where the number of connections to our MySQL database started creeping up slowly but steadily. Strangely enough all the connections were in sleep mode and the database was not being stressed. The number of connections first reached 30 where it normally stays below 20 and started triggering our monitoring scripts. [...]

[Read more →]

By special request here is a post about the MySQL slow query log. MySQL has a wonderful feature that lets you keep track of all queries that took longer than a certain time to complete. To enable it simply add the following line to your my.cnf file:
log-slow-queries = [path to the log file]
Secondly it is [...]

[Read more →]