I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant customer case to illustrate it. First you need to understand how partitions work internally. Partitions are on the low level are separate table. This means when you're doing lookup by partitioned key you will look at one (or some of) partitions, however lookups by other keys will need to perform lookup in all partitions and hence can be a lot slower. The gain from updates typically comes from having smaller BTREE on the active partition(s) which allows for a lot better fit. Having potentially fewer level in BTREE is not that significant issue.

So lets see at example:

PLAIN TEXT SQL:

1. CREATE TABLE `tbl` (
2.   `id` bigint(20) UNSIGNED AUTO_INCREMENT NOT NULL,
3.   `uu` varchar(255) DEFAULT NULL,
…

Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries are IO bound and others not - it may be because the tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL Server does not provide any information of this type, Percona Server however adds number of tables to Information Schema which makes this information possible. It is just few queries away:

PLAIN TEXT SQL:

1. mysql> SELECT `schema` AS table_schema,innodb_sys_tables.name AS table_name,innodb_sys_indexes.name AS index_name,cnt,dirty,hashed,round(cnt*100/index_size,2) fit_pct   FROM (SELECT index_id,count(*) cnt,sum(dirty=1) dirty ,sum(hashed=1) hashed FROM innodb_buffer_pool_pages_index GROUP BY …

Innodb/XtraDB tables do benefit from being reorganized often. You can get data physically laid out in primary key order as well as get better feel for primary key and index pages and so using less space,
it is just OPTIMIZE TABLE might not be best way to do it.

If you're running Innodb Plugin on Percona Server with XtraDB you get benefit of a great new feature - ability to build indexes by sort instead of via insertion. This process can be a lot faster, especially for large indexes which would get inserts in very random order, such as indexes on UUID column or something similar. It also produces a lot better fill factor. The problem is.... OPTIMIZE TABLE for Innodb tables does not get advantage of it for whatever reason.

Lets take a look at little benchmark I done by running OPTIMIZE for a second time on a table which is some 10 times larger than amount of memory I allocated for buffer pool:

…

In my continuing blog series on MySQL 5.5 features (see performance/scale and replication entries) today I covering some of the new InnoDB manageability and efficiency options.  5.5, with the newly re-architected InnoDB, provides better user control over internal InnoDB settings so things like performance, scale and storage can easily be monitored, tuned and optimized for specific use cases and application loads.

Along these lines, some of the key advances and features available in MySQL 5.5 and InnoDB are:
 

• Faster Index Creation - MySQL 5.5 can now add or drop indexes without copying the underlying data of the entire target table.  This improves the …

In my continuing blog series on MySQL 5.5 features (see performance/scale and replication entries) today I covering some of the new InnoDB manageability and efficiency options.  5.5, with the newly re-architected InnoDB, provides better user control over internal InnoDB settings so things like performance, scale and storage can easily be monitored, tuned and optimized for specific use cases and application loads.

Along these lines, some of the key advances and features available in MySQL 5.5 and InnoDB are:
 

• Faster Index Creation - MySQL 5.5 can now add or drop indexes without copying the underlying data of the entire target table.  This improves the efficiency and …

Erin and Mike, organizers of SF Meetup generously invited me to talk on coming SF Meetup on Dec-14 about new features in InnoDB in MySQL 5.1 and 5.5 and, what is pay attention to, when you upgrade from MySQL 5.0.
Although I personally mostly in 5.1->5.5 upgrade area, Erin insured me that upgrade from MySQL 5.0 is still actually question for many users, so I accepted invitation. Event details are on Meetup.com page. So if you are up for free pizza, soda and do not mind to listen to me, welcome to join!

Entry posted by Vadim | No comment

Add to: …

I have recently come across two examples of unintentional FUD. For different reasons, my …

I do not know if it is because we're hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb.

Webinar will cover the process in a lot more details though I would like to go over basics in advance. You can also check my old post on this topic about Moving MyISAM to Innodb as well as searching the blog - We've blogged so much on this topic.

So what are the basics ?

Regression Benchmarks - Make sure to run regression benchmarks in particular in terms of concurrent behavior. You may have hidden …

Percona XtraBackup 1.4 is now available for download.

Version 1.4 fixes problems related to incremental backups. If you do incremental backups, it's strongly recommended that you upgrade to this release.

Functionality Added or Changed

• Incremental backups have changed and now allow the restoration of full backups containing certain rollback transactions that previously caused problems. Please see Preparing the Backups and the --apply-log-only option. (From innobackupex, the --redo-only option should be used). (Yasufumi …

Warning, this may be kernel version specific, albeit this kernel is used by many database systems

Lately I’ve been working on getting more memory used by InnoDB buffer pool – besides obvious things like InnoDB memory tax there were seemingly external factors that were pushing out MySQL into swap (even with swappiness=0). We were working a lot on getting low hanging fruits like scripts that use too much memory, but they seem to be all somewhat gone, but MySQL has way too much memory pressure from outside.

I grabbed my uncache utility to assist with the investigation and started uncaching various bits on two systems, one that had larger buffer pool (60G), which was already being sent to swap, and a conservatively allocated (55G) machine, both 72G boxes. Initial finds were somewhat …