Sometimes I see people thinking about buffers as "larger is always better" so if "large" MySQL sample configuration is designed for 2GB and they happen to have 16, they would simply multiply all/most values by 10 and hope it will work well.

Obviously it does not. The least problem would be wasting memory, allocating a lot of memory for operations which do not need it. Allocating 10MB to sort 10 rows will not make it faster than allocating 1MB, both are more than enough.

However not only it may cause memory being wasted but you may see some of queries actually performing slower, and not because the system starts to swap. Generally you want buffers and other values to be sized "just right" - working with smaller data structures would improve cache locality, will make it easier for OS to manage memory as well as cold provide quite unexpected improvements.

sort_buffer_size - recently I worked with case which …

I meant to post something about this months ago, but I came across it again in back issue of SD Times I was reading while travelling to Europe.  Coverity did a study of open source technology using their code analysis tools and demonstrated that the LAMP stack has fewer software defects than open source in general.  On average, the LAMP stack had 0.290 defects per thousand of lines of code, compared to nearly twice that across 32 open source projects.  MySQL and Perl had the lowest defect rates in the LAMP stack, whereas Python and PHP had higher rates of defects.  Last year, Coverity did an analysis of MySQL showing that its defect rate was …

I'm very excited that MySQL implements this feature in version 5.1.x, all the new features are bridging the gap between open source and propietary RDMS like Oracle, this feature particularly makes MySQL suitable for Data warehouse systems, not just OLTP systems as it's well known.

There are a lot of benefits in the use of table partitioning like:

- Eases the administration of the tables
- Reduces time taken in large amount of DML
- Improves the response of some queries

Table partitioning allows the DBA to define how the table and the data cointained could be stored phisically, while remaining the logical structure the same. For example a table with data of 5 years (theorically a large table) could be splitted (depending on the needs) in 5 partitions, reducing the time taken by some queries that may fit into a particular year, easing the administration of the table when the data from a year …

My book No Lobbyists As Such - The War over Software Patents in the European Union is now available for download: www.no-lobbyists-as-such.com/NoLobbyistsAsSuch.pdf

The PDF file has a size of approximately 2 megabytes. In order to read the document, you need Adobe Acrobat Reader. By the way, I have also published a German edition of my book on www.softwarepatente-buch.de.

Originally I had planned to self-publish my book in print. After the official announcement of my book in late …

Call for Comments on MySQL Online Backup API - Jay Pipes

It’s been interesting watching the ideas develop for online, consistent Backup for MySQL.

I should expand that… consistent across storage engines. Other RDBMS vendors get it easy - they just have one storage engine to back up. We have several - and people want them done consistently (especially when you have multiple transactional ones).

For Cluster (NDB) we have added complications because you’re then wanting a “cross storage engine consistent backup” and there’s synchronisation down inside the cluster to make that happen. Greg and I have been discussing possible ways we could make this work. Not sure if it’ll be in the first version though :)

This is really interesting because AFAIK this is the first time …

Today I have downloaded the brand new Knoppix 5.0.1 DVD and played a bit around with it.

It looks quite nice, although some of the packages are very up to date and others are quite old. MySQL comes with 5.0.21, so there's probably no distribution with a more recent MySQL version at the moment.

PostgreSQL is included with version 8.0.4, while the current version is 8.1.4 (and the latest version of the 8.0 tree is 8.0.8). Apache comes with versions 1.3.34 and 2.0.55, but only Apache 1.3.34 is configured to start with PHP and that only with PHP 4.4.2, which is the most disappointing aspect that I found.

Spent today catching up with the lost data from yesterday. Somehow it's difficult to maintain the same enthusiasm when you're repeating work you've already done, and at the end of the day I wasn't much further than yesterday. Well, not as much further as I had hoped.

I'm still considering how to make the “low-level design” useful. Another problem is that the real issues with most algorithms in complicated products like the MySQL server is not the algorithm itself, but how it interfaces with the rest of the system. Consider a low-level design snippet like this:

  USE dbname;

  /* Stop DDL operations */
  stop_all_ddl ();

  write_backup_header ();

  /* Start the backup threads */
  for each atend engine
    spawn_thread (backup_worker, engine);

This nicely captures what I want to do; we select a database, stop DDL operations, write a header to the output stream, and spawn one worker thread for each storage engine.

That's just funny...
I just had a discussion with Beat, Giuseppe and Markus about the most preferable to write assignments in MySQL.

MySQL has a couple of ways to write an assignment:

inside the SET clause of an UPDATE statement to assign values to database columns

UPDATE sakila.film SET original_language_id := 1

inside a SET statement in to assign values to user defined variables or (in a stored routine context) local variables, parameters and trigger NEW.% values.

SET v_done := TRUE;

inside the SELECT list, where you can assign expressions to …