Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to the divide and conquer approach that Hive takes in combination with Hadoop. Shard-Query applies a clever approach to parallelism which allows it to [...]
Using Flexviews – part one, introduction to materialized views
March 23, 2011 By 3 Comments
If you know me, then you probably have heard of Flexviews. If not, then it might not be familiar to you. I’m giving a talk on it at the MySQL 2011 CE, and I figured I should blog about it before then. For those unfamiliar, Flexviews enables you to create and maintain incrementally refreshable materialized [...]
How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
Among the many things that can cause a “server stall” is a long-running transaction. If a transaction remains open for a very long time without committing, and has modified data, then other transactions could block and fail with a lock wait timeout. The problem is, it can be very difficult to find the offending code [...]
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 [...]
Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 5 Comments
So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old [...]
Unused indexes by single query
September 12, 2008 By 14 Comments
Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse – it is hard to find them. But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query
1 2 3 4 5 6 | SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND s.TABLE_NAME=INDXS.TABLE_NAME AND s.INDEX_NAME=INDXS.INDEX_NAME) WHERE INDXS.TABLE_SCHEMA IS NULL; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | +--------------+---------------------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | +--------------+---------------------------+-----------------+ | art100 | article100 | ext_key | | art100 | article100 | site_id | | art100 | article100 | hash | | art100 | article100 | forum_id_2 | | art100 | article100 | published | | art100 | article100 | inserted | | art100 | article100 | site_id_2 | | art100 | author100 | PRIMARY | | art100 | author100 | site_id | ... +--------------+---------------------------+-----------------+ 1150 rows in set (1 min 44.23 sec) |
As you see query [...]
New patches, new builds
September 5, 2008 By 24 Comments
We made new patches, improved previous and want to announce new builds for 5.0.62, 5.0.67 and 5.1.26 versions. One of biggest changes we separated releases of 5.0 into two branches. First, just “-percona” release is more stable and contains only stable and proven on many installation patches. Second is “-percona-highperf” release, which contains experimental patches [...]
Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:
