I was having problems recently with a dedicated production server, that runs my MySQL Server and a number of websites. It’s most annoying when your system crashes without any reporting in /var/log/messages

The tool of choice from the host provider SoftLayer was PassMark BurnInTest Linux which is installed with every dedicated server.

I will need to investigate open source alternatives, as this is a commercial product, but for the purposes of my pain, this included tool was well worth the investment.

**************
RESULT SUMMARY
**************
Test Start time: Sun Feb 22 16:02:48 2009
Test Stop time: Sun Feb 22 16:07:49 2009
Test Duration: 000h 05m 01s

Test Name Cycles Operations Result Errors Last Error
CPU - Maths 261 488 Billion PASS 0 No errors
Memory (RAM) 2 3.081 Billion FAIL 1 Error verifying …

Monitoring MySQL Memory is a rather critical task because you can’t limit MySQL’s usage of physical memory resources. Improperly configured servers running MySQL can crash because you don’t understand memory usage.

MySQL uses memory in a number of different ways. Using the Oracle analogy, you can divide the mysqld memory usage into main areas of:

• SGA - System Global Area
• PGA - Process Global Area

The SGA is the footprint that MySQL uses for startup. This is attributed to the base footprint of the mysqld process and a number of buffers including:

• key_buffer_size - For MyISAM Indexes (note you can define multiple key_buffer’s The MyISAM Key Cache) …

I have raised this specific topic 3 times this week alone, twice in a MySQL setting.

The fundamental philosophy of testing is NOT to verify features of your product that work, it is to BREAK your system.

One such discussion this week was with a service provider that deployed a new system into an existing ecosystem. The release has been delayed due to development issue, and credibility with customers is now being further damaged because the system is reaching physical hardware limitations after just one month.

With this was described to me, my simple response was. You did not test you system to stress the system to breaking point. To know the limit of your capacity ahead of time is a proactive analysis, not a reactive one.

It’s not that complicated to do, easier in early stage before you have a 50-100-1000 server total environment, but it’s a best practice not see often enough.

InitMarketing has made its calendar of world-wide conferences and trade fairs related to Free and Open Source Software, IT and specific industries available to the public.

It currently includes 122 events in 17 countries taking place in 2009. 43 of them in Germany, 69 in USA. We use this calendar when planing events for our customers, thus we’ll regularly update it. Please let us know of any events which are not on our radar yet by commenting to my blog or commenting at the bottom of the events page.

Correctly managing your MySQL database objects such as schemas, tables, indexes, base data etc, is critical to the success of a 24×7 online website. I rarely encounter a robust working solution as part of my consulting so I would like to share my experience in identifying the best practices you should be adopting whether your an existing organization or just an individual with a simple website.

Much of the following concept actually pre dates my involvement in MySQL (since 1999), so this is not just applicable for a MySQL RDBMS. For the purposes of this discussion I’d like to focus on the theory successfully used with clients.

Under version control I have the following directory structure:

NOTE If your first observation was “Arrh, Version Control?”, you are in more trouble then you want to be right from day one. You need Version Control such as svn, cvs, bzr, git etc for any website no matter how small.

Merlin is MySQL’s Enterprise Monitor, a system designed to monitor a number of MySQL database servers and provide useful feedback to the DBAs as to how they are running and what things may require attention.

It provides various things:

• graphs of key database and server performance indicators: load average, cpu usage and mysql specific information such as number of database connections, replication delays, buffer usage statistics, etc…
• a set of advisors which are intended to tell you if things are not configured properly and what to do to correct the problem.
• monitoring of servers which are linked together through replication
• the latest version of merlin (v2.0, which has been available since the beginning of the year) additionally adds via mysql proxy the ability to see all the queries which are …

I’m told WordWeb, a dictionary application for Windows, allows you to find words, quite unlike the MacOSX dictionary. I’ve never even found a usable dictionary when I use Linux, that doesn’t require me to go online.

The whole idea is that if I search for the phrase cure all, I get back a thesaurus entry that suggests the word panacea. OK, that example alone wasn’t so great, because cure-all tends to be a word in these dictionaries. WordWeb Online allows me to have a “reverse dictionary” result, which I think is just fabulous (as long as I have access to an Internet connection).

Does such a Mac app exist? Seems WordWeb exists for the iPhone, but their Java app, requires an Internet connection.

What’s interesting is that they sell the dictionary data. They are powered by MySQL, and if you’re wanting to grab their …

Did anybody notice that http://planetmysql.org now redirects to http://planet.mysql.com?

Curious to know the reason why, perhaps an official MySQL person can give us some details.
Also it’s a 302 redirect, not a 301 redirect, interesting?

 wget http://planetmysql.org
--2009-02-26 14:40:09--  http://planetmysql.org/
Resolving planetmysql.org... 213.136.52.29
Connecting to planetmysql.org|213.136.52.29|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: http://www.planetmysql.org/ [following]
--2009-02-26 14:40:10--  http://www.planetmysql.org/
Resolving www.planetmysql.org... 213.136.52.29
Connecting to www.planetmysql.org|213.136.52.29|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://planet.mysql.com/ [following]
--2009-02-26 14:40:10-- …

I can’t stress enough that people look at SQL statements that are being executed against your production MySQL database, and you optimize queries when you can.

Often it’s the improvement to the large number of similar queries executed that can optimize resources. In this example, we take a very simple query, and by removing an unnecessary order by, we eliminate MySQL internally creating a temporary (in memory) table.

So what’s the big deal.

• The query is simpler to read and understand
• Memory required for the connection is not assigned
• A number of internal steps are no longer required (4 of 21 logging messages, not an ideal measurement, but an indication). In this case, it was easily a 10% performance improvement for each query.

This query is executed 10-100 times per second, so the improvement in performance is significant.

mysql> explain select max(mdate) …

Are you having problems getting up to speed on MySQL? Are you asking yourself “Is there a hands-on training course we can send a developer/system admin to learn MySQL?”. In response, at 42SQL we have put together two new training courses, MySQL Essentials and MySQL Operations.

MySQL Essentials Training Details

With MySQL Essentials we tackle the core essentials that a developer/system admin/junior DBA would require in order to support an initial development environment that uses MySQL. Essentials training teaches the following skills:

• Which version of MySQL to use (including the various different variants and patches available)
• Backup, retention, and recovery strategies
• Configuration and Monitoring of MySQL
• Optimal schema and data objects configuration management
• …