This post is followup to Peter’s recent post, “Investigating MySQL Replication Latency in Percona XtraDB Cluster,” in which a question was raised as to whether we can measure latency to all nodes at the same time. It is an interesting question: If we have N nodes, can we send queries to nodes to be executed in [...]
Accessing Percona XtraDB Cluster nodes in parallel from PHP using MySQL asynchronous queries
March 6, 2013 By 2 Comments
Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
I was curious to check how Percona XtraDB Cluster behaves when it comes to MySQL replication latency — or better yet, call it data propagation latency. It was interesting to see whenever I can get stale data reads from other cluster nodes after write performed to some specific node. To test it I wrote quite a [...]
MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with [...]
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 [...]
SystemTap – DTrace for Linux ?
September 14, 2009 By 17 Comments
Since DTrace was released for Solaris I am missing it on Linux systems… It can’t be included in Linux by the same reason why ZFS can’t be – it’s licensing issue. Both ZFS and DTrace are under CDDL, which is incompatible with GPL. So you can see DTrace and ZFS on Solaris, FreeBSD, MacOS, but [...]
PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
We’re working with web site preparing for massive growth. To make sure it handles large data sets as part of the process we work on generation test database of significant size as testing your application on table with 1000 rows may well give you very dangerous false sense of security. One of the process web [...]
Pitfalls of converting to InnoDB
February 25, 2007 By 16 Comments
We often recommend to our clients to convert their current database from MyISAM tables to InnoDB. The transfer by itself in most cases is almost plain, however the application can be broken by new unexpected errors 1205 (ER_LOCK_WAIT_TIMEOUT) Lock wait timeout expired. Transaction was rolled back. 1213 (ER_LOCK_DEADLOCK) Transaction deadlock. You should rerun the transaction.
Are PHP persistent connections evil ?
November 12, 2006 By 32 Comments
As you probably know PHP “mysql” extension supported persistent connections but they were disabled in new “mysqli” extension, which is probably one of the reasons some people delay migration to this extension. The reason behind using persistent connections is of course reducing number of connects which are rather expensive, even though they are much faster [...]
GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 48 Comments
MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. Where it can be useful?
Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
Article about database design problems is being discussed by Kristian. Both article itself and responce cause mixed feellings so I decided it is worth commenting: 1. Using mysql_* functions directly This is probably bad but I do not like solutions proposed by original article ether. PEAR is slow as well as other complex conectors. I [...]
