Percona is glad to announce the release of Percona Server 5.5.16-22.0 on October 14, 2011 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.16, including all the bug fixes in it, Percona Server 5.5.16-22.0 is now the current stable release in the 5.5 series. All of Percona’s software is open-source and free, all the [...]
Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 17 Comments
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 [...]
Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
The new release of Maatkit has a useful feature in mk-index-usage to help you determine how indexes are used in more flexible ways. The default report just prints out ALTER statements for removing unused indexes, which is nice, but it’s often helpful to ask more sophisticated questions about index usage. I’ll use this blog’s queries [...]
Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread [...]
Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
One schema optimization we often do is extending index when there are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It turns there are special cases when this is not [...]
Joining on range? Wrong!
May 17, 2010 By 10 Comments
The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may [...]
High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work. The first thing these have in common is that they’re generally some kind of loggable event. The next characteristic of a lot of these systems (real or planned) is [...]
Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
Suppose you want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE huge_table CHANGE id id int(6) NOT NULL and then wait hours for table rebuild to complete. If you’re unlucky i.e. you have a lot of indexes and not too much RAM – you could end [...]
Heikki Tuuri Innodb answers – Part I
October 26, 2007 By 19 Comments
Its almost a month since I promised Heikki Tuuri to answer Innodb Questions. Heikki is a busy man so I got answers to only some of the questions but as people still poking me about this I decided to publish the answers I have so far. Plus we may get some interesting follow up questions [...]
Redundant index is not always bad
August 28, 2007 By 7 Comments
About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I’m speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as for queries like WHERE A=5 the index (A,B) [...]
