The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND STATE='CA' they would create 2 separate indexes on AGE and STATE columns.

The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case.

MySQL indexes are (with few exceptions) BTREE indexes - this index type is very good to be able to quickly lookup the data on any its prefix and traversing ranges between values in sorted order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index in order until it runs into the value of AGE more than 18 when it stops doing so assuming there are no more matching. The …

Just thought i should have a quick guide for configuring an optimized for innodb my.cnfuse innodb_data_home_dir & innodb_data_file_path to specify the location and size of the tablespace. innodb_data_home_dir = /path/to/datainnodb_data_file_path = ibdata1:50M;ibdata2:50M:autoextend:max:10Ginnodb_autoextend_increment = 10This asks innodb to create two files ibdata1 & ibdata2 (as tablespace) and

We often hear from customers and MySQL experts that fragmentation causes problems such as wasting disk space, increasing backup times, and degrading performance. Typical remedies include periodic “optimize table” or dump and re-load (for example, see Project Golden Gate). Unfortunately, these techniques impact database availability and/or require additional administrative cost and complexity. Tokutek’s Fractal Tree algorithms do not not cause fragmentation, and we’re looking for ways to measure the effects of fragmentation to quantify TokuDB’s benefits.

I ran some tests using the iiBench benchmark as an experiment to try and quantify the impact of fragmentation, and observed some …

We often hear from customers and MySQL experts that fragmentation causes problems such as wasting disk space, increasing backup times, and degrading performance. Typical remedies include periodic "optimize table" or dump and re-load (for example, see Project Golden Gate). Unfortunately, these techniques impact database availability and/or require additional administrative cost and complexity. Tokutek's Fractal Tree algorithms do not not cause fragmentation, and we're looking for ways to measure the effects of fragmentation to quantify TokuDB's benefits.

I ran some tests using the iiBench benchmark as an experiment to try and quantify the impact of fragmentation, and observed some interesting …

With a discussion on identifying CPU and Memory bottlenecks achieved, let us now look at how Disk can affect MySQL performance.

One of the first steps when addressing a MySQL performance tuning problem is to perform a system audit of the physical hardware resources (CPU,Memory,Disk,Network), then identify any obvious bottlenecks in these resources.

There are several commands you can use including vmstat, iostat and sar (both part of the sysstat package) to provide information on disk I/O.

vmstat is a good place to start for an overall view for multiple resources, however for disk it only provides a total system overview.

procs -----------memory---------- ---swap-- -----io---- -system-- …

It may seem hard to believe, but I have seen DECIMAL(31,0) in action on a production server. Not just in one column, but in 15 columns just in the largest 4 tables of one schema. The column was being used to represent a integer primary or foreign key column.

In a representative production instance (one of a dozen plus distributed production database servers) the overall database footprint was decreased from ~10 GB to ~2 GB, a 78% saving. In total, 15 columns across just 4 tables were changed from DECIMAL(31,0) to INT UNSIGNED.

One single table > 5GB was reduced to under 1GB (a 81% saving). This being my record for any GB+ tables in my time working with the MySQL database.

Had this server for example had 4GB of RAM, and say 2.5GB allocated to the innodb_buffer_pool_size, this one change moved the system from requiring more consistent disk access (4x data to memory) to being able to store all data in memory. Tests showed …

Some days you learn about MySQL commands even without knowing about them. Today I wanted to check the privileges a user had because they did not have permissions to drop a view. Rather then typing SHOW GRANTS I quite by accident typed SHOW PRIVILEGES only to realize not only was it a valid command, it actually provided information that means I don’t have to go to the Privileges Provided by MySQL documentation page which I was already on.

Old dog, new trick.

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               | …

I have written about a patch to InnoDB that generates a trace of all pages accessed by InnoDB. I used this to collect another trace for 24 hours and the results from it match the previous results. I then modified the simulator to estimate the miss rate when the database is compressed by 2X and the miss rate is significantly smaller. This can be viewed in the results I published where the miss rates for the uncompressed and compressed databases are displayed by the gold and green lines. The other lines are estimates assuming a Zipfian distribution.

The miss rate determines performance when your database server is IO bound and for my workload I have simulation results that estimate the miss rate improves linearly with the …

Topics for this podcast:

*Microsoft founds CodePlex Foundation, losing Sam Ramji
*Software patents at the center of MS, OIN maneuvering
*Eucalyptus Systems releases hybrid cloud product
*Oracle-Sun Microsystems and the potential fate of MySQL

iTunes or direct download (26:40, 6.1 MB)

A new case study has been published that describes how and why Mapion adopted MySQL Cluster as the real-time, mission critical database for their Mapion Mobile applications.

Mapion adopted MySQL Cluster Databse because it allows for the availability of existing applications through its ’shared-nothing’ distributed architecture with no single point of failure, to meet Mapion’s mission-critical application requirements of 99.999% availability. This is coupled with automatic data partitioning with load balancing, which allows almost unlimited database scalability for the company. Mr. Takashi Ando comments, “Some databases supported clusters when we previously reviewed our database technology, but they were hard to operate and would have increased costs substantially. We found ourselves in a completely different situation when planning the introduction of the new system. MySQL Cluster Database had made it possible to construct a highly …