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!

Comments (4) | 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

Comments (1) | Trackback

Perl Script to Insert DBpedia Infobox Data into a MySQL Database

This script parses out the Wikipedia page, DBPedia Infobox Predicate and Infobox subject, and inserts them into a MySQL table. I thought I’d share it with The Internet in case someone else wanted to work with DBPedia infobox data in the same way.

Comments (1) | Trackback

Chinese Idiom Database Updates

I hit a brick wall with my plugin development, so I spent some time updating the Chinese Idiom Database.

Comments (3) | Trackback