Vadim Tkachenko, Author at Percona Database Performance Blog

Vadim Tkachenko
Vadim Tkachenko co-founded Percona in 2006 and leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

Unused indexes by single query

Sep 12, 2008 | Percona Software

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

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;

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;

+--------------+---------------------------+-----------------+
| 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)

+--------------+---------------------------+-----------------+

| 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 […]

Google’s user_statistics V2 port and changes

Sep 12, 2008 | Percona Software

Recently Google published V2 release of patches, one of them user_statistics we use in our releases. New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes: New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc) New command SHOW CLIENT_STATISTICS, which shows statistics per client’s hostname, not per user […]

The performance effects of new patches

Sep 09, 2008 | Benchmarks, Percona Software

We are going to show the effects of the new patches applied to Percona HighPerf release. As you see from the following graphs, there is significant difference to normal version when the data bigger than buffer pool (right graph shows CPU usage)

Development plans

Sep 08, 2008 | Percona Software

We gathered together our ideas of MySQL improvements on this page https://www.percona.com/percona-lab/dev-plan.html and we are going to implement some of them. My favorite one is – make InnoDB files .ibd (one created with –innodb-file-per-table=1) movable from one server to another, however it is sort of challenging. Probably next one patch we want to integrate is […]

New patches, new builds

Sep 05, 2008 | Percona Software

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 […]

Percona RPMS for RedHat 5 / CentOS 5 x86_64

Jul 31, 2008 | Percona Software

We prepared RPMs of our release for RedHat 5 / CentOS 5 x86_64 platform. https://www.percona.com/blog/mysql/RPM/RHEL5/5.0.62/ There was question what patcheset includes and if there is manuals. We have: microsecond resolution in slow-log extended query plan in slow-log and InnoDB statistics. You can read more here https://www.percona.com/blog/2008/04/20/updated-msl-microslow-patch-installation-walk-through/ User / Table / Index statistics (Google’s patch). More […]

Percona builds with patches

Jul 24, 2008 | Percona Software

As there were many requests for Debian binary packages in our announcement of Percona builds with custom patchsets, we decided to play with it and built .deb, which you can find there: https://www.percona.com/blog/mysql/debian/ You can also add the following repositories into your sources.list: deb http://percona.com/blog/mysql/debian/5.0/ binary-amd64/ deb http://percona.com/blog/mysql/debian/5.0/ source/ deb http://percona.com/blog/mysql/debian/5.1/ binary-amd64/ deb http://percona.com/blog/mysql/debian/5.1/ source/ […]

Percona builds with Percona patchsets

Jul 16, 2008 | Percona Software

Percona has a strong team of MySQL developers and consultants on board, so we decided to prepare builds with our patches and third-party patches which we think are very useful. We actually use these internally and for our customers. Current releases include: microslow patch (enables microsecond resolution in slow logs) execution plan (show info about […]

Will Falcon fly?

Jun 22, 2008 | Percona Events

Why one may wonder, it’s just Swedish beer (State of Doplhin, MySQL UC 2006). One week ago Jim Starkey sent message http://www.firebirdnews.org/?p=1742 so he will not work for MySQL anymore and starting new project. While that’s fully Jim Starkey’s personal decision, I expected some comments about Falcon future development from MySQL / Sun side. Jim […]

Lighttpd as reverse proxy

Jun 17, 2008 | Insight for Developers

We often recommend to set lighttpd in front of apache to handle http requests (more about https://www.percona.com/blog/speedup-your-lamp-stack-with-lighttpd/ ) , redirect dynamic requests to apache and handle static files by itself. I just gathered step-by-step instruction how to do that in 10 minutes, as it may be not so obvious.

Dangerous command

May 20, 2008 | Insight for DBAs

Remembering that I did RENAME DATABASE in MySQL 5.1.21 and found it useful I tried it with 5.1.24 (I was playing with 20GB InnoDB database, so dumping is not fastest way) and all my tries finished with “Syntax error”. So RTMF and documentation says “This statement was added in MySQL 5.1.7 but was found to […]

Testing InnoDB “Barracuda” format with compression

Apr 23, 2008 | Insight for DBAs

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

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;

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;

MySQL and plugin binaries

Apr 20, 2008 | Insight for DBAs

It seems there is interesting problem with compatibility of MySQL binaries and binaries of third-party plugins. I personally found and there is confirmation from InnoDB team that current InnoDB-plugin binaries do not work with lastest 5.1.24-rc binaries. It was very charming move from MySQL side to release new incompatible binary on the second day after […]

Future InnoDB File Formats

Apr 15, 2008 | Insight for DBAs

Looking into InnoDB docs I found list of names of future InnoDB file formats: Antelope, Barracuda, Cheetah, Dragon, Elk, Fox, Gazelle, Hornet, Impala, Jaguar, Kangaroo, Leopard, Moose, Nautilus, Ocelot, Porpoise, Quail, Rabbit, Shark, Tiger, Urchin, Viper, Whale, X, Y and Zebra. Aren’t there animals starting on X and Y?

Partially OpenSourced

Apr 15, 2008 | Percona Events

Reading evening news and rss feeds I found interesting next sequence: Jeremy’s post “MySQL to launch new features only in MySQL Enterprise” basically about some new features of MySQL will be available only for paid customers Then Kaj’s post Anthropology: Sun studies MySQL with tagline “Sun didnâ€™t acquire MySQL to change it, but to learn […]

MySQL 6.0 vs 5.1 in TPC-H queries

Mar 25, 2008 | Benchmarks

Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from 22) have significantly better execution time (about them in next posts), […]

Slides from Egypt talks

Dec 16, 2007 | Percona Events

I published slides from my two talks from our Egypt company meeting. First one is about Sphinx search engine. The originally presentation was prepared by Andrew Aksenoff, author of Sphinx for HighLoad.ru conference, I translated it on English for our meeting. And second about Mercurial version system, the tool we decided to use for our […]

MyISAM Scalability and Innodb, Falcon Benchmarks

Oct 12, 2007 |

We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that’s why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core […]

InnoDB auto-inc scalability fixed

Sep 26, 2007 | Insight for DBAs

There was long played scalability issue with InnoDB auto-increment field. For details check Bug 16979. In short words the problem is in case of insert into table with auto-increment column the special AUTO_INC table level lock is obtained, instead of usual row-level locks. With many concurrent inserted threads this causes serious scalability problems, and in […]

InnoDB in self-compiled MySQL 5.1

Sep 13, 2007 | Insight for DBAs

If you like to compile MySQL from sources by yourself, for different needs, like debugging, testing etc, you probably can face this issue. What I usually do to fast compile and test is

./configure --prefix=/dir/to/mysql
make
make install

./configure --prefix=/dir/to/mysql

make

make install

and then, for example, load the dump of InnoDB from previous version:

mysql testdatabase < dump.sql

1	mysql testdatabase < dump.sql

I bet you will not notice all your […]

MySQL 5.7
Support

Compare Percona to Leading Database Solutions

Software
Downloads

Valkey Contribution

Product Documentation

Resource Hub

Why Percona for MongoDB?

Why Percona for PostgreSQL?

Percona Blog

Percona Community Hub

Percona Events Hub

About Percona

Percona in the News

Our Customers

Our Partners

Careers

Contact Us

Unused indexes by single query

Google’s user_statistics V2 port and changes

The performance effects of new patches

Development plans

New patches, new builds

Percona RPMS for RedHat 5 / CentOS 5 x86_64

Percona builds with patches

Percona builds with Percona patchsets

Will Falcon fly?

Lighttpd as reverse proxy

Dangerous command

Testing InnoDB “Barracuda” format with compression

MySQL and plugin binaries

Future InnoDB File Formats

Partially OpenSourced

MySQL 6.0 vs 5.1 in TPC-H queries

Slides from Egypt talks

MyISAM Scalability and Innodb, Falcon Benchmarks

InnoDB auto-inc scalability fixed

InnoDB in self-compiled MySQL 5.1

MySQL 5.7 Support

Compare Percona to Leading Database Solutions

Software Downloads

Valkey Contribution

Product Documentation

Resource Hub

Why Percona for MongoDB?

Why Percona for PostgreSQL?

Percona Blog

Percona Community Hub

Percona Events Hub

About Percona

Percona in the News

Our Customers

Our Partners

Careers

Contact Us

MySQL 5.7
Support

Software
Downloads