We had some technical difficulties with Webex today which prevented from all registered attendees to be able to join webinar. I personally would be very upset having booked my time to attend event, pre-registered to ensure there is a space available and when unable to join at the time of event (which is frankly exactly [...]
Webinar: Introduction to Percona Server, XtraDB and Xtrabackup
November 26, 2010 By 2 Comments
We’ve had such a large number of signups to our Migrating MyISAM to InnoDB webinar, that we decided to hold one more before the year is out: December, 8th 9AM PST: Introduction to Percona Server, XtraDB and Xtrabackup.
Two Types of MySQL Users
May 10, 2010 By 5 Comments
In comments to my previous post I got number number of comments saying if MySQL would not have multiple storage engine interface it would not allow people to do various very cool stuff. And I agree with this. The question is how cool you want your database operation to be ? Visiting customers I see [...]
Why you should ignore MySQL’s key cache hit ratio
February 28, 2010 By 28 Comments
I have not caused a fist fight in a while, so it’s time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL’s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and [...]
How (not) to find unused indexes
October 16, 2009 By 2 Comments
I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed. This method is flawed – here’s the first reason why:
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 | CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) DEFAULT NULL, `status` enum('archived','active') DEFAULT NULL, PRIMARY KEY (`id`), KEY `status` (`status`) ) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1; mysql> SELECT count(*), status FROM sales GROUP by status; +----------+---------+ | count(*) | status | +----------+---------+ |   65536 | archived | |     154 | active | +----------+---------+ 2 rows in set (0.17 sec) mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1 +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra      | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE     | sales | ref | status       | status | 2      | const | 196 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in set (0.06 sec) mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2 +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra      | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE     | sales | ALL | status       | NULL | NULL   | NULL | 65690 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.01 sec) |
The cardinality of status index is woeful, but provided that the application [...]
Impossible – possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
This is probably the feature I missed most from early days when I started to use InnoDB instead of MyISAM. Since that I figured out how to survive without it, but this is first question I hear from customers who migrated from MyISAM to InnoDB – can I just copy .ibd files from one server [...]
Converting Character Sets
March 17, 2009 By 21 Comments
The web is going the way of utf8. Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8. Googling for “mysql convert charset to utf8″ results in a plethora of sites, [...]
Linux schedulers in tpcc like benchmark
January 30, 2009 By 19 Comments
I mentioned earlier that IO scheduler CFQ coming by default in RedHat / CentOS 5.x may be not so good for MySQL. And yesterday one customer reported that just changing cfq to noop solved their InnoDB IO problems. I ran tpcc scripts against XtraDB on our Dell PowerEdge R900 server (16 cores, 8 disks in [...]
XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
One of our customers gave me a chance to run some benchmarks on 24-core (intel cpu based) server, and I could not miss it and ran few CPU-bound tasks there. The goal of benchmarks was investigation of InnoDB-plugin and XtraDB scalability in CPU-bound load.
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 [...]
