If you’ve ever done any non-blocking programming (usually for socket I/O), you’ve probably had to come up with a non-trivial state machine to handle all the places where everything can pause. Say you’re reading an application level packet from a socket, and half way through the read() system call it screams EAGAIN. You need to stop, save any state, and exit out of whatever chain of functions got you there so the calling application can regain control. I’m going to explain a few techniques I’ve come up with over the years, each with their strengths and weaknesses, and I hope this will spur some conversation of what other folks have done. While I’m fairly happy with how I handle these state machines now, but I’m always looking for a more succinct way of handling things. Please share your thoughts!

Switch Statements

The obvious way to handle non-blocking I/O is with one or more switch statements. Say we need to …

The past week has brought a surge of Gearman related releases. They include:

C Server and Library
Perl Server and Modules
Gearman::XS Perl Module (based on C library)
PHP Extension (based on C library)
PHP Extension Documentation
Drizzle UDFs
PostgreSQL UDFs
MySQL UDFs

Some of …

Answering questions asked on the site.

Princess asks:

Hello, I’ve got a problem with SQL.

I have a table which contains the production details for the factories. They are not factories and items of course but I cannot disclose the project and need to obfuscate so let’s pretend they are :)

I need to select the items for which the first 5 factories have low production rate.

I tried to do the query like this:

SELECT  ProductionItem
FROM    FactoryProductions
WHERE   5 >= FactoryID
        AND 100 >= ProductionAmount

which returns correct results but is slow.

I have an index on (FactoryID, ProductionAmount).

There are 13 Factories and 2,300,000 Items

This is in MySQL

This is a nice illustration of how index range evaluation works …

There are many areas you need to review when addressing MySQL performance such as current database load, executed SQL statements, connections, configuration parameters, memory usage, disk to memory ratio, hardware performance & bottlenecks just to name a few.

If you were to just look at the data that is held in the database, what would you consider?
Here are my tips, when looking just at the data.

1. What is the current database size?
2. What is the growth of data over time, say daily, weekly?
3. Which are the 2 largest tables now?
4. What 2 tables are growing the fastest?
5. What tables have greatest churn, specifically DELETE’s?
6. How often do you optimize your tables?
7. What is your archiving/purging strategy? Do you even have one?
8. Review data types? I average 25% reduction in footprints, just by choosing optimal data types, generally with zero …

If you need an ad-hoc Query Tool for MySQL on Windows, then MyQuery might be what you aere looking for. MyQuery is an Open Source, free Windows tool, supporting colour syntax highlightning, multi-tab editing, several information, status and dictionary features and much more.

The latest release is MyQuery 3.2.1, and if you have followed the development of MyQuery, you know that the 3.x releases are focused on features and usability, and 3.2.1 has advances in both areas:

• Much enhanced keyboard navigation. It is now possible to navigate the main window and most functions and dialogs, using only the keyboard. Tab-ing around in teh main dialog is now supported for example.
• Enhanced Keyboard accelerators, where accelerators for almost all functions has been added, and there is now also a non-modal dynamic help dialog, ie. dynamic in the sense that is shows the current key assignments. This help may also be …

This is a hack, but it is a good one. I was looking at some machines on a new client, and they had the general log turned on. I was surprised, because it was a fairly busy server, and they had had many problems with the server a few months ago. I thought perhaps they had turned on the general log to diagnose a problem and forgotten to turn it off, or something similar.

When I looked at the log on disk, I saw it was a symlink to /dev/null. They were running MySQL 5.0, so I immediately realized that it was a hack to have a general log they could “turn on” without having to restart mysqld.

On a different server, I saw the same link to /dev/null done with a slow query log.

The drawbacks to doing it this way is that MySQL still has the overhead of writing to the log file. The I/O overhead is greatly reduced because the writes are to /dev/null, but there’s still overhead from other resources such as RAM, CPU, etc.

…

More importantly, how often to you confirm access to your server and database with that alert monitoring?

With a client yesterday the primary database server while still usable and serving connections for a while, but was not accessible via SSH to investigate performance issues. It eventually became non responsive and required a physical reboot. With alert monitoring for system availability only recorded every 5 minutes this was simply too long a delay.

This lead to a discussion with more questions then answers including.

• How often should you ping your server(s), both internally and externally?
• How often do you connect physically to your server for confirmation, e.g. a ssh keyed authentication test?
• How often do you perform a physical database connection test?
• How often do you do an end to end test, including http request to database query test?

As with all of …

Sourceforge used to be my preferred way of sharing and finding software. Sourceforge used to be there always, hosting loads of projects and stuff, some of them good, some bad, some dead, some very active. But many fine projects was there, incluing a big bunch of good MySQL projects, and most of my own projects are hosted there.

Now, Sourceforge has, somehow, turned dead slow. The interface is now nice and inviting, sort of, but some of the old interface is still there. But it is so sloooow. Gosh. Is lauchpad the name of the game, I have seen lauchpad more as a tool for codeveloping software and stuff, but I have hardly looked at it, so I don't know. All I want is a means of sharing my projects. They are so small and insignificant that few are likely to want to step in and work on them with me, so although they are Open Source, I think there are few people reading the source (and maybe that is just as well).

Anyway, I …

Recently we have released Workbench 5.2.4 Alpha. This version has some new features, and amongst them there is Workbench Administrator plugin or WBA plugin for short.

The plugin aims to ease the managing process of server instances. What we offer with WBA is a simple way to check status, configure and control one server instance. Some parts will resemble discontinued MySQL Administrator.

And here is a short summary of what we will have in WBA:

• Start/Stop server
• Edit server configuration (my.cnf)
• Manage user accounts
• Monitor user connections
• Status/Server variables browsing
• Log browsing
• Dump/Restore

In this alpha we have implemented a subset, which targets local usage only. Remote management and administration is upcoming. The subset includes:

• Add new Server Profiles
• Start/Stop the Server
…

We’re proud to announce the availability of the next Alpha release of MySQL Workbench 5.2.
This is the first release of WB 5.2 that enables part of the upcoming administrative feature set. The following tasks can be performed in the WB 5.2.4 release:

• Registration of Server Profiles
• Start/Stop of the MySQL Server
• Configuration File Edition (my.cnf / my.ini)
• Show Connections and Server-variables

Further we have added the long awaited SSH Tunnel features that enables MySQL connections to machines where only SSH access is available.
Select the new Connection Method “Standard TCP/IP over SSH” in the Connection Management dialog and fill out the SSH connection information.

More information about the new features is coming up here on our blog pages.

Please fetch your copy, try the new features and tell us if you like it or what we can improve. But …