In a few scenarios, I prefer using the data-type TIMESTAMP of MySQL, with the flag ON UPDATE CURRENT_TIMESTAMP. This is much easier and even faster than using a trigger which sets a specified column to NOW() or CURRENT_DATE(). I love this feature, when it comes to set last modified flags of tables. Now, I encountered a problem with a tagging table by using the flag. Every tag in the table has besides the tag-name, also the aggregated number of relations and a timestamp of last update (or the insert). Everything is okay, until you want to update the aggregated column, when the numbers are wrong for any reason.

Read the rest »

If you have a wedding photography website, more than likely you want to showcase your work, or the work of your colleagues. You want to do this, without putting up low quality pictures, nor do you want to make your visitors wait 20 seconds before the page loads. Here are two plugins I use for my clients to help with this.

• The Smush.it plugin. This plugin compresses your image using the Smush.it API from Yahoo!, I’ve used it to reduce a page size from 3MB to 1.3MB, which meant that visitors were able to see the site faster, and appreciate the photographs rather than their internet connection.
• W3 Total Cache : This is a fairly advanced plugin that makes your site use MySQL less, and also helps you off load things onto a content delivery network with a few clicks. …

We had a one of our slave servers frequently stop replicating with the “Innodb Lock Wait Timeout” error. The slave IO thread would continue to fetch the binlogs while the slave SQL thread kept stopping with  the above mentioned error. The teams initial inclination was to change the innodb lock wait timeout variable from 50 secs to a higher value. It was a read-only slave. Our expectation was there would be no competing writes. Then we started listing what are the next steps possible and what could be wrong.

1. There could be a user with “super” privilege in the system that was running updates directly on the slave
2. A backup script that could be locking the tables out for backup
3. Increase the “innodb lock wait timeout variable“
4. Enable the innodb lock monitor

While we were working on the system, we noticed that there were few select queries that …

This time OSI Days is doing special theme around Open Source Databases. Please see their call for paper below. It will be interesting to see how many MySQL papers are being presented there. I am submitting a couple of them. Please spread the word so that we can see an increased MySQL participation there. I think they are sponsoring quite a bit of speaker travel and accommodation.

____________________________

Open Source Databases | OSI Days 2010 is the premier FOSS Databases conference being organised at Asia's largest Open Source Conference - OSI Days 2010. We invite you to come and lead a tutorial / session or participate in Panel Discussions at OSI Days 2010 on a specific Open Source Database. The last date for submitting a proposal is 25th June 2010.

The conference is scheduled for …

While calculating the storage requirements in NDB, extra consideration is needed when calculating storage requirement for NDB tables. For tables using the NDB cluster storage engine, there is the factor of 4 – byte alignment to be taken into account when calculating storage requirements. This means that all NDB data storage is done in multiples of 4 bytes.

For Example, let’s say if a column takes 14 bytes to store. In NDB it requires 16 bytes to store. 2bytes will be padding. Because of this only in NDB TINYINT, SMALLINT, MEDUMINT, and INT all require 4 bytes storage per record due to the alignment factor. This rule is not applied in case of BIT data type.

BIT(X) – in NDB storage engine this column will take X bite of storage space, if a table definition contains 1 or more BIT column (up to 32 BIT columns) then NDB Cluster reserves 4 Bytes (32 bytes) per row for these . If the table definition contains more than 32 BIT …

While calculating the storage requirements in NDB, extra consideration is needed when calculating storage requirement for NDB tables. For tables using the NDB cluster storage engine, there is the factor of 4 – byte alignment to be taken into account when calculating storage requirements. This means that all NDB data storage is done in multiples of 4 bytes.

For Example, let’s say if a column takes 14 bytes to store. In NDB it requires 16 bytes to store. 2bytes will be padding. Because of this only in NDB TINYINT, SMALLINT, MEDUMINT, and INT all require 4 bytes storage per record due to the alignment factor. This rule is not applied in case of BIT data type.

BIT(X) – in NDB storage engine this column will take X bite of storage space, if a table definition contains 1 or more BIT column (up to 32 BIT columns) then NDB Cluster reserves 4 Bytes (32 bytes) per row for these . If the table definition contains more than 32 BIT …

With the exception of Windows InnoDB has used ’simulated AIO’ on all other platforms to perform certain IO operations. The IO requests that have been performed in a ’simulated AIO’ way are the write requests and the readahead requests for the datafile pages. Let us first look at what does ’simulated AIO’ mean in this context.

We call it ’simulated AIO’ because it appears asynchronous from the

We are pleased to announce the availability of the 1.0.6 release of InfiniDB Community Edition.  This is the fourth of our 1.0 monthly maintenance releases.

This release includes a number of bug fixes that you can see at http://bugs.launchpad.net/infinidb.  You can download the latest InfiniDB binaries, source code, and updated documentation at: http://infinidb.org/downloads.  ...

I was given the task of checking the array accelerator cache ratio and see if it was set to optimal levels. Our ideal preference was a read/write ratio of 0/100.

The machine configuration is HP DL180 G5, 2 x Xeon L5420 2.50GHz, 15.7GB / 16GB 667MHz DDR2, 6 x 300GB-15K SAS.This machine was running mysql 5.1.36 using the innodb plugin.

The command line utility to check the controller configuration is “hpacucli”. Navigating using hpacucli is very straight forward.

“ctrl all show config detail” Will give you the entire controller configuration.

=> ctrl all show config detail

Smart Array P400 in Slot 5
Bus Interface: PCI
Slot: 5
Serial Number: P61630K9SW31NL
Cache Serial Number: PA82C0J9SW02H1
RAID 6 (ADG) Status: Enabled
Controller Status: OK
Chassis Slot:
Hardware Revision: Rev D
Firmware Version: 4.12
Rebuild …

We are pleased to announce the availability of the 1.1.3 Beta release of InfiniDB Community Edition.  This is our initial beta release for 1.1 and it is not recommended for production work.

This release delivers the final content for our 1.1 release, thus we have moved from an Alpha designation to a Beta designation.  We will be putting out weekly releases in June containing bug fixes and performance tweaks as we march towards our 1.1 Final release.&nb...