In the past I came across a scheduled cronjob to dump and restore a subset of a table that was changed to a MySQL view into the authoritative data. The idea is , though the initial implementation sucked due to a single query taking 300% longer than before. Here is why.

SELECT MAX(Entered) FROM Table1;

So the above query is looking at a timestamp column within a table. It basically was finding the newest entry in the table.


So as a background, the existing dump/restore would dump out the contents of table with SQL:

SELECT * FROM Table1 WHERE (Entered >= unix_timetamp(now() - 32*24*3600) );

The new view that was created was:

CREATE VIEW Table2 SELECT * FROM Table1 WHERE (Entered >= unix_timetamp(now() - 32*24*3600) );


So the issue was that

SELECT MAX(Entered) FROM Table1; took 0.3 seconds though
…

I came across the following issue not too long ago


Here is the error:
Last_Errno: 1206
Last_Error: Error 'The total number of locks exceeds the lock table size' on query. Default database: 'Foobar'. Query: 'REPLACE LOW_PRIOR ITY INTO blah (ID,Modified,Feature,Style, ....


That begs the question - where and what is the 'lock table', how can I look into it to see the space constraints and why does this occur.

A quick google led me to the following post http://mrothouse.wordpress.com/2006/10/20/mysql-error-1206/

“in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set.”


Thus Innodb records row level locking in the buffer pool. So what appears to have happened is that the active set in the …

One aspect of my positions with Calpont, MySQL, and other companies I’ve worked for has been to interact with the various analysts who cover the database scene. It’s definitely an interesting part of the job, especially when you get to query some of the experts who have been around a long time and are good at making solid technology calls on where things are headed.

IDC is one such group of experts and they recently released an interesting set of predictions on where databaRead More...

The headline is flame-bait, don’t take it. I just wanted to point something out about character sets and collations in MySQL. To the uninitiated, it may seem overwhelming. Everything has a character set! Everything has a collation! And they act weirdly! The server has one. The database has one (oh, and it changes magically as I USE different databases.) Every table has one, and columns too. Is that all? NO! My connection has one!

MySQL Cluster is well known as an in-memory database which can be restrictive (a server typically has a RAM limit) so it may surprise some to learn that you can also store MySQL Cluster data on disk.  Disk Data Tables have been in MySQL Cluster for quite a while, the first GA release with them included was 6.2.  They do have caveats which I will describe here, many of which already have plans to be improved in future versions.
How Disk Data Tables work

A set of UNDO logs and data files can be created which are on every data node in the cluster.  The UNDO logs bring the disk data back to a consistent point so that during node restart the REDO logs can be replayed when a node is started.

Not all data in a disk table is actually stored on disk, the main two things that are not are indexed columns (the entire column is in main memory, not just the index) and the first 256 bytes of a …

The mobile world is changing. It's changing faster than the database world did back when MySQL was started and grew to be one of the most widely used database in the world.

Change brings turbulence and it's difficult trying to see the big picture to find the major trends. It also means different philosophies of doing things clash and fight for survival.

There are two large debates at the moment around mobile. One is about open versus closed platforms and the other is around native applications versus web based. One of them is an important philosophical issue, the other one a more technical question of the best way to bring a good user experience to mobile.

The success of the iPhone and the App Store has meant a huge leap for both mobile applications and mobile web. But the iPhone platform is closed. The entire ecosystem is controlled by one company.

On the Internet it's (somewhat) safe to …

I'm pleased to announce the release of Spider storage engine version 2.13(beta).
Spider is a Storage Engine for database sharding.
http://spiderformysql.com/

The main changes in this version are following.
This release is bug fix release.

Please see "99_change_logs.txt" in the download documents for more detail.
Thanks to Mitsuhiro for bug report.

Enjoy!

I am currently working as a Database Administrator with Linux Systems Administrative components for REA-Group. The company specialises in online advertising in the property space - with websites like realestate.com.au and casa.it and my role is the optimistion, up-time and performance of the underlining database infrastructure. Prior to REA, I was working as a Database Administrator for Rackspace Hosting in their managed hosting team, one of the biggest hosting companies in the world.

I specialise in MySQL Server performance as well as in performance of the LAMP stack. realestate.com.au is a 600 webpage hit-per-second website and challenges around performance, redundancy and optimisation is what I love the most.

You can find me on linked in at: …

At my current job, I have seen much over indexing in some of our production schemas.

Here is a quick rule of thumb; if you have an schema such as
CREATE TABLE blah ( col1 int default not null, col2 int default not null, primary key (col1,col2), key col1, key col2);
or
CREATE TABLE blah ( col1 int default not null, col2 int default not null,unique key (col1,col2), key col1, keycol2);


The index ‘key col1’ is redundant to the primary (or unique key) as col1 is the first in the other indexes tuple. Creating the col1 index will do nothing more than cause a big performance hit for updates and reduce the valuable useful space of the innodb buffer pool (if innodb is being used) intern making things slow.

I’m speaking at the O’Reilly MySQL Conference 2010. I hope I don’t lose my voice, because I have four sessions!

• Diagnosing and Fixing MySQL Performance Problems
• EXPLAIN Demystified
• Read-Write Splitting: Techniques, Challenges, and Solutions
• MySQL Graphing and Trending with Cacti

You can click through on the links above to learn …