June 20, 2013

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…

Filed Under: Insight for DBAs Tagged With:

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…

Filed Under: MySQL

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…

Filed Under: Insight for DBAs Tagged With:

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 …

Filed Under: Insight for DBAs, MySQL

Post: Using LoadAvg for Performance Optimization

December 4, 2006 By 5 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…

Filed Under: Insight for DBAs Tagged With: ,

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

… superb Sphinx performance. The Problem While Sphinx…columns are basically one of two kinds: a) full text b) attributes Speaking in MySQL terms, Full text columns…set (0.70 sec) sphinx> show meta; +—————+———-+ | Variable_name | Value | +—————+———-+ | total | 1000 | …

Filed Under: Insight for Developers, MySQL Tagged With: , ,

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 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 …

Filed Under: MySQL

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…

Filed Under: Insight for DBAs Tagged With:

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

…given SELECT would show N possible index choices but only one index actually used. To some extent, MySQL 5.0…each of the columns in the WHERE clause that are included in this merge operation and then perform a set… to find out if index merge is actually a problem) but probably not the best way to go …

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

…were observing database performance that dropped through …defined first. So, MySQL checks our INSERT,…key ‘PRIMARY’ (root@localhost) [test]> show status like ‘handler%’; *** some rows omitted *** +—————————-+——-+ | Variable…AUTO-INCREMENT column to use a BIGINT, and problem solved…

Filed Under: Insight for DBAs, Insight for Developers, MySQL
« Older Posts
Newer Posts »