… of defaults changes in MySQL 5.6 compared to MySQL 5.5 In general there are a lot of…file_per_table =1 Great! We found it is better choice in 95%+ of installations innodb_log_file_size = 48M…is it just looking to reduce number of different buffer sizes used. sync_master_info = 10000 Quite useless …
Post: Getting History of Table Sizes in MySQL
December 8, 2010 By 13 Comments
… the history of the table sizes. Projection of data growth is very important component for capacity planning and simply watching the growth of space used on… keep it completely inside MySQL you can create appropriate event in MySQL 5.1+ Unless you’re having millions of tables this is something…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… the field that is used to join the two tables? A: The question in this case is how MySQL will execute the join… of them which are accessed frequently. The size of this “working set” can greatly depend on application and can range from 5% of total size…. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…, ACTIVE 9 sec mysql tables in use 2, locked 2 8316 lock struct(s), heap size 1948088, 10008317 row lock(s) MySQL thread id 53173… index `k` of table `sbtest`.`sbtest` trx id 12305 lock mode S Aha! Now we have 2 tables in use and 2 tables locked reported… sec fetching rows mysql tables in use 1, locked 1 9417 lock struct(s), heap size 915896, 696679 row lock(s) MySQL thread id 53173…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… I’ve decided to use the words of the Constitution of the United States of America as an …size of the words table and I create words2. This is the data upon which we will operate: create table… as run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
…mySQL teach you ? Of course you have to learn a lot about query optimization, art of building summary tables and tricks of…If you have tiny 1GB table you pretty much can use trial and error approach even… size of table to fit in memory for decent performance. Typically it would be some portion of…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO…size including indexes which is way more than buffer pool size and OS cache which is available. I run CHECK TABLE on this table… is even though performance advantages of buffered mode can be dramatic…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
…of software was not available for MySQL. Change Data Capture can be used to: Monitor a database table, or tables…size: 214652 exec_master_log_pos: 214652 If you select from that table you…table. This is generally only useful if you have a small number of tables, and you want to track changes on all of…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…data size of any set, possibly by orders of …table of numbers, and I want to sum them all up: mysql> show create table ex1; +——-+—————————————————————————————————–+ | Table | Create Table…do it without using the “search set”…
Post: Reality of Innodb Caching
April 21, 2011 By 8 Comments
… file of 2247098368 which gives us 224 bytes of gross storage per row, including all overhead etc. Actual row size in this table is smaller but lets use this number for our math. For benchmark I’m using set number of random IDs…
