We use MySQL on most of our projects. One of these projects has a an access pattern unlike any other I've worked on. Several million records a day need to be written to a table. These records are then read out once at the end of the day, summarised and then very rarely touched again. Each record is about 104 bytes long (thre's one VARCHAR column, everything else is fixed), and that's after squeezing out every byte possible. The average number of records that we write in a day is 40 million, but this could go up.

A little bit about the set up. We have fairly powerful boxes with large disks using RAID1/0 and 16GB RAM, however at the time they only had 4GB. For BCP, we have a multi-master set up in two colos with statement level replication. We used MySQL 5.1.

My initial tests with various parameters that affect writes showed that while MyISAM performed slightly better than InnoDB while the tables were small, it quickly …

Installing Lighttpd With PHP5 And MySQL Support On Mandriva 2009.1

Lighttpd is a secure, fast, standards-compliant web server designed for speed-critical environments. This tutorial shows how you can install Lighttpd on a Mandriva 2009.1 server with PHP5 support (through FastCGI) and MySQL support.

For your convenience, a short phrase book, starting with explanation of process states where MySQL is mostly working to look up data from tables:

• “Sending data” – reading data from tables (or looking it up)
• “Copying to tmp table” – reading data from tables (or looking it up)
• “Copying to tmp table on disk” – query needs a rewrite
• “statistics” – looking up data from tables
• “Sorting result” – reading data from tables (or looking it up)

Locking is fun:

• “update” – There is big chance that it is waiting for a lock, can be updating as well, on busy systems
• “Locked” – Even bigger chance that it is MyISAM table waiting to be converted to InnoDB

Table opening is even funnier:

• “Opening tables” – some other idiot thread is holding the LOCK_open or similar mutex
…

I'm pleased to announce the release of Spider storage engine version 2.4(beta) and Vertical Partitioning storage engine version 0.4(alpha).
Spider is a Storage Engine for database sharding.
http://spiderformysql.com/
Vertical Partitioning is a Storage Engine for vertical partitioning for a table.
http://launchpad.net/vpformysql

The main changes in this version are following.
- "Engine-condition-pushdown" is available on Vertical Partitioning with Spider.

Please see "99_change_logs.txt" in the download documents for checking other changes.
Thanks to Gennady for bug report.

Enjoy!

If you want to get core dumps for intermittent Apache/mod_php crashes in Linux, you will probably need this module (otherwise Linux kernel will refuse to dump core, whatever you put into your OS or Apache configuration):


/*
* Author: Domas Mituzas
* Released to public domain
*
* apxs -c -i mod_dumpcore.c
* and...
* LoadModule dumpcore_module .../path/to/mod_dumpcore.c
* CoreDumpDirectory /tmp/cores/
* and...
* sysctl -w kernel.core_pattern=/tmp/cores/core.%p.%t
*/

#include "httpd.h"
#include "http_config.h"
#include <sys/prctl.h>

static int dumpcore_handler(request_rec *r)
{ prctl(PR_SET_DUMPABLE,1,0,0,0); return DECLINED; }

static void dumpcore_register_hooks(apr_pool_t *p)
{ap_hook_handler(dumpcore_handler, NULL, NULL, APR_HOOK_MIDDLE);}

module …

Just recently I came across the presentation “Bend SQL to your will with EXPLAIN” by Ligaya Turmelle via the Linked In – MySQL Speakers and Presenters group. Slides available at Slideshare. While I know a little about MySQL, I always enjoy spending time reviewing content when possible, perhaps as a good reference when asked, perhaps to glean some small point of knowledge I didn’t know, or didn’t understand completely, or had forgotten about. It’s also fun to randomly find worthwhile information.

EXPLAIN is an essential tool for MySQL developers, if you don’t know what QEP is, the listed google search link …

Image by plαdys via Flickr

The following is a list of interesting DBMS related links for the week:

• Ellison's Impatience Over Sun
• Intel squeezes one million IOPS from desktop
• Is the RDBMS doomed (yada yada yada) ?
• EDS brand is no more (not DBMS but interesting)

…

I need one MyISAM table in my schema, I’m using Symfony 1.2 and Propel 1.3. As flexible as Symfony and it’s YAML files are, Propel currently doesn’t allow you to change the storage engine on the fly. You use the storage engine as specified in propel.ini. As discussed in the Propel FAQ .

So, the next best thing to do now is to have it so that when Symfony runs propel:insert-sql, I can piggyback my own SQL to it. Luckily, this is possible, and it’s explained here

I now have my own SQL file running at the end, where I do an ALTER TABLE. For the lazy web.

1. Create your Alter table:

ALTER TABLE `database`.`footable` ENGINE=`MyISAM`;

2. Save the file in $projectdir/data/sql/piggyback.sql

…

Welcome, readers, to the 163rd edition of Log Buffer, the weekly review of database blogs, your sieve

Oracle

First, the ghastly news—Tom Kyte said “I’m not a DBA anymore.” Say it ain’t so, Tom! “After nine years and nine months of running the database that hosts asktom, I’ve retired . . . not from answering questions, but rather from being the DBA and semi-SA for the machine that was asktom.oracle.com.” Okay, so he said it ain’t so.

Meanwhile, Tom’s Oak Table colleague, Jonathan Lewis, played no head games on us, but he has been at the hash …

Our Santa Clara/San Francisco training went great - 100% of survey respondents said they would recommend the same course to a friend.  I'm pleased to announce that such an opportunity exists - our next training location will be New York City on October 30, 2009.

We've booked a training venue in the financial district of Manhattan (90 Broad Street New York, NY 10004), and it seems like a great opportunity to switch from using hotels to teaching in real classrooms.  This means that every student will have a (Linux) computer provided, and the instructor will have a whiteboard to be able to scribble.

Some other changes:

…