June 20, 2013

Post: Data compression in InnoDB for text and blob fields

May 30, 2012 By 2 Comments

… a KEY_BLOCK_SIZE that is the same value of the InnoDB page size of 16KB?  As the fine MySQL manual states: This setting may… different KEY_BLOCK_SIZE value.  Also note that you need to enable the  dynamic variable innodb_file_format=BARRACUDA (don’t forget to set… null-operation like this: ALTER TABLE `t1` ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; In our case even though the customer…

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

Post: Innodb Performance Optimization Basics

November 1, 2007 By 75 Comments

… applications, though the optimal settings of course depend on the workload. Hardware If you have large Innodb database size Memory is paramount. 16G… memory. MySQL Innodb Settings The most important ones are: innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to… advantage of Innodb properties – clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

….6.10 with no configuration tuning other than to set innodb_ft_min_token_size to 4 (rather than the default of 3… problems for InnoDB, because it’s only a single character and we’ve set innodb_ft_min_token_size to 4. Thus, when InnoDB is… escape hatch from this rabbit hole. When defining a FULLTEXT KEY, you can use the “WITH PARSER” modifier to specify the…

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

Post: To pack or not to pack - MyISAM Key compression

May 13, 2006 By 4 Comments

… MyISAM performing better than Innodb. In this article I’ll get in a bit more details about packed keys and performance implications… point you can’t set compression on per index basics. Neither you can set index block size for the keys. So you can’t… integer keys is the biggest reason joins using string keys is so much slower compared to integer keys. Integer keys still best but for Innodb…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Testing InnoDB "Barracuda" format with compression

April 23, 2008 By 3 Comments

…` int(10) unsigned NOT NULL, `thread_id` varchar(255) CHARACTER SET latin1 NOT NULL, `published` timestamp NOT NULL DEFAULT ’0000-00… dump created with mysqldump, final size 30286M. To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast index creation I create table only with primary key…

Filed Under: Insight for DBAs Tagged With: ,

Post: Innodb Caching (part 2)

May 10, 2011 By 7 Comments

… shows a worse case scenario by picking the random set of primary key values from sysbench table and reading them over and… 20% per step. I’m trying standard Innodb page size, 4KB page size as 16K page size compressed to 4. The data in this… but with large number of keys compressed results were best of all, probably because the data size was smallest. Lets introduce one…

Filed Under: MySQL

Post: Choosing innodb_buffer_pool_size

November 3, 2007 By 39 Comments

…10%) larger than your data (total size of Innodb TableSpaces) because it does not only contain…key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are also things like innodb…On Linux, FreeBSD, Solaris you need to set innodb_flush_method=O_DIRECT. On other…

Filed Under: Insight for DBAs Tagged With:

Post: Innodb row size limitation

April 7, 2011 By 12 Comments

…, fdisclaimer TEXT, fcopyright TEXT, fstylesheet TEXT, fterms TEXT, PRIMARY KEY (id) ) Engine=InnoDB; Now you insert some test data into it: mysql… sets an ~8000 bytes limit on row size (8126 as reported by my test installation). This limit is a consequence of InnoDB storing…-in-innodb/ for further reference). It’s worth mentioning that this limit applies to the byte-size of values, not the character-size…

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

Post: INSERT INTO ... SELECT Performance with Innodb tables.

July 12, 2006 By 22 Comments

… enough to trigger relaxed locks. You have to set innodb_locks_unsafe_for_binlog=1 as well. This …declared inside InnoDB 3 mysql tables in use 1, locked 1 2539 lock struct(s), heap size 224576 MySQL …. Other transaction which happes to be simple primary key update is waiting on sample table for this …

Filed Under: Insight for DBAs Tagged With: ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…innodb_write_io_threadsblogpercona.commanual insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_files_on_createblogpercona.commanual Key…

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