… of SSDs really a big advantage in practice? I will show the performance of two real customer systems running web applications. Let… so we can see performance throughout the day. If you’re not familiar with the diskstats tool, the columns are the number…. This is causing acute performance problems for the database, manifested as epic stalls and server lockups which show up as sky-high latency…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…MySQL sets the ALGORITHM=UNDEFINED, and will chose to use TEMPTABLE when the view is actually used: mysql> show …c1, performance is not good. MySQL is scanning the entire table to produce the result: mysql> select … the value for the connection_id column. Also notice the restriction that v1.c1 …
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems… sorting is done by leading column(s). Note however columns following column used for order by can… 1 row in set (0.03 sec) See – “rows” is showing us there are estimated 1.6 million…
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
…mysql> show warnings; +———+——+————————————————————–+ | Level | Code | Message | +———+——+————————————————————–+ | Warning | 1264 | Out of range value adjusted for column…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
…from an objective viewpoint. I performed two different types of testing….experience developing such cases for MySQL column-based storage engines in the … problem would be “select avg(char_column) from table” where the column contains…in Infobright that would be “show stoppers” for typical OLAP analysis…
Post: MySQL Blob Compression performance benefits
January 11, 2008 By 17 Comments
…MySQL does not provide compressed BLOB/TEXT columns (I would really love to have COMPRESSED attribute for the BLOB/TEXT columns…generally the problem. Compression … compression showing row …performance gains were even better because much fewer rows required external storage now. The BLOB/TEXT column…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… as well as making foreign keys performed row by row which often can be…problems to Hibernate and other ORM based applications which often like to update all columns whenever they have changed or not: mysql… sec) mysql> drop table parentx; Query OK, 0 rows affected (0.61 sec) mysql> show create table …
Post: Using LoadAvg for Performance Optimization
December 4, 2006 By 4 Comments
…moving average of sum of “r” and “b” columns from VMSTAT. Obviously minimum value for LoadAvg is… are no performance problems, for example if you run single batch job on the server with MySQL, Load Average…reason I would start to worry whatever LoadAvg shows. P.S I acknowledge some of explanations are…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… quite frequently with MySQL 
[+] Compact Indexes Compact Indexes are great. Huge indexes is where large portion of Innodb performance problems come however. We… it requires, not storing column value if it is default etc. This is nice even though may show strange effects if you…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
…do we have gaps on auto-increment columns? Secondly, I will show you a trick to mimic the INSERT … and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method …the same time, so we lose concurrency and performance. The problems are even worse with long running queries like …
