One of my favorite tools in the Percona Toolkit is pt-query-digest. This tool is indispensable for identifying your top SQL queries, and analyzing which queries are accounting for your database load. But the report you get from pt-query-digest is only as good as the log of queries you give it as input. You need a large [...]
How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
One question I often get is how one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which cause the large portion of the load on the system but how do we know whenever there is any possibility to make this query [...]
DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the [...]
When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB [...]
Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on variables, or look for content in the Percona documentation or forums. So I present to you what is now my newest Firefox toolbar bookmark: an option and [...]
Testing InnoDB “Barracuda” format with compression
April 23, 2008 By 3 Comments
New features of InnoDB – compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | CREATE TABLE `article87` ( `id` bigint(20) unsigned NOT NULL, `ext_key` varchar(32) NOT NULL, `site_id` int(10) unsigned NOT NULL, `forum_id` int(10) unsigned NOT NULL, `thread_id` varchar(255) CHARACTER SET latin1 NOT NULL, `published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `subject` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `url` varchar(255) NOT NULL, `num_links` smallint(6) NOT NULL, `links_in` int(10) unsigned NOT NULL, `cache_author` varchar(255) NOT NULL, `cache_site` varchar(255) DEFAULT NULL, `anchor` varchar(255) NOT NULL, `isthread` tinyint(3) unsigned NOT NULL, `author_id` int(10) unsigned NOT NULL, `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `fromfile` varchar(255) NOT NULL, `language_id` tinyint(3) unsigned NOT NULL, `encoding` varchar(255) NOT NULL, `warning` mediumtext NOT NULL, `is_thread_start` tinyint(3) unsigned NOT NULL, `source` mediumint(8) unsigned NOT NULL, `hash` char(32) NOT NULL, `mod_is` tinyint(3) unsigned NOT NULL DEFAULT '0', `is_adult` tinyint(3) unsigned NOT NULL DEFAULT '0', `bodyuc` mediumtext NOT NULL, PRIMARY KEY (`id`), KEY `ext_key` (`ext_key`), KEY `forum_id` (`forum_id`,`thread_id`,`published`), KEY `site_id` (`site_id`,`published`), KEY `hash` (`hash`), KEY `forum_id_2` (`forum_id`,`is_thread_start`,`published`), KEY `published` (`published`), KEY `inserted` (`inserted`), KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`), KEY `site_id_2` (`site_id`,`author_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
Microslow patch is used by many DBAs and developers to accurately time their queries and to catch those which run less than a second as they can also be a performance killer for a busy application. Recently I have started the development of an updated version of the patch. The basic idea is the same [...]
Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
I’ve already wrote a few times about various projects using Sphinx with MySQL for scalable Full Text Search applications. For example on BoardReader we’re using this combination to build search against over 1 billion of forum posts totaling over 1.5TB of data handling hundreds of thousands of search queries per day. The count of forum [...]
Descending indexing and loose index scan
May 9, 2006 By 6 Comments
Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general – these are features MySQL should get for all storage engines at some [...]
My Innodb Feature wishes
May 4, 2006 By 30 Comments
At Users Conference Heikki did good presentation about Innodb planned features. I did not see some of big and tiny wishes listed so I was making notes. Here is what I’d like to see Packed indexes. In many cases then difference in Performace with MyISAM and Innodb is huge for read only workload it is [...]
