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
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 [...]
Cache Performance Comparison
August 9, 2006 By 74 Comments
Jay Pipes continues cache experiements and has compared performance of MySQL Query Cache and File Cache. Jay uses Apache Benchmark to compare full full stack, cached or not which is realistic but could draw missleading picture as contribution of different components may be different depending on your unique applications. For example for application containing a [...]
Handling big result sets
June 26, 2006 By 13 Comments
Sometime it is needed to handle a lot of rows on client side. Usual way is send query via mysql_query and than handle the result in loop mysql_fetch_array (here I use PHP functions but they are common or similar for all APIs, including C). Consider table:
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 41 42 43 44 45 | CREATE TABLE `longf` ( `f1` int(11) NOT NULL auto_increment, `f2` date default NULL, `f3` date default NULL, `f4` varchar(14) default NULL, `f5` varchar(6) default NULL, `f6` date default NULL, `f7` smallint(6) default NULL, `f8` smallint(6) default NULL, `f9` varchar(13) default NULL, `f10` varchar(39) default NULL, `f11` int(11) default NULL, `f12` float default NULL, `f13` int(11) default NULL, `f14` smallint(6) default NULL, `f15` varchar(39) default NULL, `f16` date default NULL, `f17` smallint(6) default NULL, `f18` int(11) NOT NULL, `f19` date default NULL, `f20` date default NULL, `f21` varchar(14) default NULL, `f22` varchar(6) default NULL, `f23` date default NULL, `f24` smallint(6) default NULL, `f25` smallint(6) default NULL, `f26` varchar(13) default NULL, `f27` varchar(39) default NULL, `f28` int(11) default NULL, `f29` float default NULL, `f30` int(11) default NULL, `f31` smallint(6) default NULL, `f32` varchar(39) default NULL, `f33` date default NULL, `f34` smallint(6) default NULL, `f35` int(11) NOT NULL, PRIMARY KEY (`f1`) ); select count(*) from longf; +----------+ | count(*) | +----------+ | 5242880 | +----------+ |
