Tomorrow, Thursday the 15th, I'm driving down to Atlanta to give a presentation on Join-fu: The Art of SQL at the Atlanta PHP User Group. If you're interested in how to squeeze the most performance out of your (My)SQL applications, some "join" me. We'll be covering a number of topics, including a brief overview of MySQL internals, optimizing schema and index organization, understanding the many MySQL storage engines, and most of all, how to think in SQL and not in your programming language of choice.

Why is it important to think in terms of SQL and not your programming language of choice when implementing database-centric applications? Well, the short answer is that SQL is a set-based …

Want to know the type of an arbitrary expression in MySQL? Someday in the far far future in version 7.1, you might be able to with the TYPEOF() function.

For now you can try this:

CREATE TEMPORARY TABLE typeof AS SELECT [expression] AS col;

For example, let’s see what the type of CRC32 is.

mysql> CREATE TEMPORARY TABLE typeof AS SELECT CRC32('hello world') AS col;
mysql> DESCRIBE typeof;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col   | int(10) unsigned | NO   |     | 0       |       | 
+-------+------------------+------+-----+---------+-------+

This is one possible way to programmatically determine the type of an expression — even an arbitrarily complex one.

Not beautiful, but …

Last weekend I updated the Debian package of MySQL to 5.0.67, the latest Community Edition release. It was not uploaded into the Debian archive, but is available from my home directory on gluck (amd64 only at the moment). The next Debian release lenny is frozen already, which means no newer upstream releases get accepted into testing before lenny is released, hence lenny definitely gets shipped with MySQL 5.0.51a. We will not see MySQL 5.0.67 or 5.1 in lenny, but we will continue providing backports of newer MySQL releases.

Our current primary goal regarding the MySQL packages in Debian is to get 5.0.51a in shape for lenny, so please do not send us mails asking about MySQL 5.1 …

Last weekend I updated the Debian package of MySQL to 5.0.67, the latest Community Edition release. It was not uploaded into the Debian archive, but is available from my home directory on gluck (amd64 only at the moment). The next Debian release lenny is frozen already, which means no newer upstream releases get accepted into testing before lenny is released, hence lenny definitely gets shipped with MySQL 5.0.51a. We will not see MySQL 5.0.67 or 5.1 in lenny, but we will continue providing backports of newer MySQL releases.

Our current primary goal regarding the MySQL packages in Debian is to get 5.0.51a in shape for lenny, so please do not send us mails asking about MySQL 5.1 or MySQL Cluster. When lenny got released we will upload MySQL 5.0.67 (or any newer community release) to unstable, and …

Ben Kaufman at SQLServerCentral introduces MySQL to the SQL Server DBA crowd. All in all his views seem to be fairly positive, in particular the MySQL Cluster experience:

NDB is the gem of MySQL, originally developed by Ericson to track cell phone calls this is a share nothing cluster engine stored in memory. This is a true cluster that supports both high availability and load balancing. [...]
This engine is similar to synchronous mirroring in SQL Server in that it is a 2-phase commit, the difference being the commit is done in memory at the data layer not the log. Logs are hardened at a later time, with the theory being that since data is committed on multiple nodes the data is safe and doesn't require a log flush as part of the transaction. [...]
For pure performance the cluster is comparable with a single instance of SQL Server. I've …

The Maatkit toolkit is a real blessing for the MySQL DBA. And while its documentation is pretty good, in some cases it’s necessary to read carefully a second and third time to make sure you are not missing an important piece of information. In this article I will comment on mk-table-chksum and mk-table-sync. My comments are mostly aimed at those DBAs who are considering using these utilities with medium or larger-sized databases.

–replicate

This option allows you to store the checksum results on the master, in a table that will get replicated to the slaves. Although it might seem like overhead for a simple check, it really simplifies your life, especially when used in combination with …

Tonight I provided a scope in Best Design Practices for MySQL Applications - Part A of just how much there is to consider with a successful application. I only scratched the surface in some detail, a topic one could easily write a book on, and definitely provide a number more presentations.

At 42SQL we follow the “Scale 2 Success” (tm) approach covering Architecture, Availability, Scalability and Accountability. With this we are able to clearly provide a strategy towards building successful scalable enterprise solutions. For more information on how we may be able to help your organization with any performance and scalability needs, please use the Contact Form.

You can find a copy of my slides for this presentation at …

Relevant Source code directories and Notes

• mysql-test- test suite for the MySQL daemon. Uses Perl script to run tests using a pre-defined framework and test language
  • To run the tests in this test-suite, go to mysql-test and run the 'mysql-test-run.pl' script
  • 'make test' also does the above after running the tests in 'unittest'
    
• tests- C & perl scripts to perform various client -like tasks- uses libmysql (C lib) Perl API respectively (mainly tests features which are not possible via above)
  • No where in the top-level Makefile is the 'tests' directory mentioned
  • what is the 'mysql_client_test.c' doing?
    
  • mysql_client_test- shell script wrapper for .libs/mysql_client_test
    
    • the …

Relevant Source code directories and Notes

• mysql-test- test suite for the MySQL daemon. Uses Perl script to run tests using a pre-defined framework and test language
  • To run the tests in this test-suite, go to mysql-test and run the 'mysql-test-run.pl' script
  • 'make test' also does the above after running the tests in 'unittest'
    
• tests- C & perl scripts to perform various client -like tasks- uses libmysql (C lib) Perl API respectively (mainly tests features which are not possible via above)
  • No where in the top-level Makefile is the 'tests' directory mentioned
  • what is the 'mysql_client_test.c' doing?
    
  • mysql_client_test- shell script wrapper for .libs/mysql_client_test
    
    • the …

Today I was working with the client loading data to MyISAM tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which need to be inserted (in the table with few indexes). It was good not all records had to go to the same table and so in theory using multiple thread to do inserts in multiple tables would not be bound by table locks and would be able to use multiple cores efficiently to get good insert rate... or so it seemed.

In reality inserting in parallel into different tables when indexes fit in memory results in very bad contention causing hundreds of thousands of context switches per second with far less work done than one would hope. As we already discussed
MyISAM key cache has serious mutex contention issue as there is global …