…’s n:o < 0 80154573 undo n:o < 0 0 History list length 6 Total number of lock structs in row… processing, if it is active otherwise it will be zero. History list length 6 is number of unpurged transactions in undo…: size 1, free list len 887, seg size 889, is not empty Ibuf for space 0: size 1, free list len 887, seg size…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…_stock_2 | | order_line | fkey_order_line_2 | | history | fkey_history_1 | | history | fkey_history_2 | +————+——————-+ Here we see one of the limitations on… or a testing environment with an similar data estructure and size. Conclusion We’ve learnt how to find unused indexes using…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…,SUBTITLE,ORIG_PUBLISH_DATE,DESCRIPTION,OTHER_INFO,CATEGORY,REMARKS) against (‘+ history’ in boolean mode) as score from bp_search as bp…,SUBTITLE,ORIG_PUBLISH_DATE,DESCRIPTION, OTHER_INFO,CATEGORY,REMARKS) against (‘+ history’ in boolean mode) group by group_id order by score… sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache_size = 80 query_cache_size = 32M…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
….02 sec) Looking at SHOW INNODB STATUS we can see: History list length 5 Total number of lock structs in row… other hand it still consumes exactly same amount of memory: History list length 5 Total number of lock structs in row… 10099, OS thread id 1878960 6093 lock struct(s), heap size 503104 MySQL thread id 7429, query id 24542 localhost root…
Comment: Are you designing IO bound or CPU bound application ?
December 21, 2006 By 10 Comments
… history was originally stored in date of edit order. Showing 50 rows of history starting 1000 versions ago would take 1050 seeks. Histories… very easy to cache effectively, significantly cutting the working set size. Then it was changed to do something better than LIMIT…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… Mutex will still hunt you. If you aware of MySQL history you may think Key Cache scalability was fixed with new… key_cache proportional to their size and load, but no more than the index size (assuming table sizes are relatively static) To get… | +———————————————+ | SET GLOBAL test_a.key_buffer_size=39514112; | | SET GLOBAL test_b.key_buffer_size=28390400; | +———————————————+ 2 rows in set (2 min…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… to all storage engines. Lets start with a bit of history. When MySQL was just written there was not that many… preloading, which works for MYISAM tables only. read_buffer_size, read_rnd_buffer_size – Variables used as read buffer for MyISAM tables… but currently implemented only for Innodb. tmp_table_size This variable specifies maximum size implicit temporary table (created during query execution automatically…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… are inserted in the index can affect performance dramatically. Table Size This is the worst trouble maker. Typically load starts fast…_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool_size and large innodb_log_file_size… the challenges. If you have some particular history about loading similar data of similar size on similar hardware use that as estimate…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… TachIon card, I used different values for innodb_buffer_pool_size, like 13GB, 52GB, and 144GB, for testing the tpcc-mysql…_dirty_pages_pct, innodb_io_capacity, and innodb_log_file_size are important for tuning. Also, upgrading to Percona Server, with… Percona Server, then increasing innodb_log_file_size may help, but check checkpoint age history to see if that is really needed…
Post: Sharding and Time Base Partitioning
March 14, 2008 By 12 Comments
… reason is some sort of logs or history data. Think about Wikipedia page version history for example which accumulates to huge volume… than having table with the mix, even though the total size remains same. It also often makes sense to separate data…
