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 …

This Thursday (October 8th, 13:00 UTC), Jörg Brühe will give a talk on Building MySQL Releases on Unix. Jörg will describe the tools used by the MySQL Build Team for release builds on Unix-style platforms, and present a script to simulate the essential parts of that process locally on a developer's home machine. He works at the MySQL Build Team, so you can expect in-depth insights and instructions.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides …

The MySQL Certification: Listing of MySQL Certified Users has been a valuable resource for many years. I have a small stack of emails from people who were recruited for jobs after someone spotted their names on the lists. But the lists are going away.

The vast majority of MySQL Certification information is now part of Sun's certification system and has been since the end of July. Prometric exam results feed into that system and not the MySQL system. The lists of certified users pulls from the now out of date MySQL system. That means the data from the listings are not up to date. There is no easy way to cross-tie the information from Sun's systems into the MySQL web system. So please spread the word that the lists are going away, most likely by the end of the calendar year.