MyTop, Top for MySQL

ECPod must have jumped a bit in popularity, as today, while skim-reading the error log I noticed a lot of these:

[exception 'PDOException' with message 'SQLSTATE[00000] [1040] Too many connections' in ../includes/classes/db/connection.php5

My initial reaction was to simply increase the connection limit in /etc/my.cnf. After doing that I also went through parts of our codebase that create new DB connections, and refactored them so that they now reuse an existing connection if possible – should have been that way from the start, but things like that can be missed when under a hectic development schedule.

Damage control over, I ran `top` to check the load of our database master. This made me wonder if there is a similar tool available that allows monitoring of a mysql instance.

There is, it’s called mytop. It’s even in the CentOS repository!

A quick yum install later, and I am now able to monitor mysql’s performance in real-time, just like I can for the whole server with top.

Here’s a screenshot of it in action:

MyTop Screenshot

Great to be able to see queries per second, average queries per second, total queries, mysql uptime etc all in one place, and it’s quite interesting to watch if for a few minutes to see the queries as they’re being made. Also shows key buffer efficiency (how often keys are read from the buffer rather than disk).

In all, very helpful!

No comments | Trackback

MySQL Workbench

I’ve very recently started using MySQL Workbench, and see it as a viable replacement for phpMyAdmin.

Since before I can remember, I’ve used either the CLI or phpMyAdmin to work with MySQL databases. With the project I’m currently working on, we have phpMyAdmin installed on the development server, but not the production server – we have as little as possible installed on those, for obvious reasons.

I stumbled upon MySQL Workbench through my searches for a way for me to create a graphical representation of our tables and the relations between them. Since we use MyISAM for our database, unfortunately all of our table relationships are application-enforced – so really I was looking for a tool that could connect to a database and allowed one to chuck a bunch of tables onto a canvas and somehow create and explain connection between them.

This is an example of what I found I could do with the workbench, using some tables from a bugzilla database:

MySQL Workbench Example

With this example, all relationships were added automagically, as the bugzilla database uses InnoDB.

For a free application, this feature alone is enough to sell me. The more I use it, the close I feel I’m coming to dropping phpMyAdmin altogether. I’ll keep using it for a week or so, but so far it’s not looking good for phpMyAdmin, at least in terms of daily use.

It’s much more than a database visualizer, if you use MySQL frequently and haven’t yet found your ‘dream app’, I recommend you give this a look: MySQL Workbench

No comments | Trackback