… posts result in 20GB table which may be too much to fit into the buffer pool (or other cache). Now the table which I just mentioned would have approximately 20 byte rows even for Innodb tables which makes it 200MB in total which easily fits in buffer pool. Fitting of the working set in buffer is paramount as…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… InnoDB: specifically, the buffer pool. (There’s also an innodb_additional_mem_pool_size but it’s … doesn’t actually promise to fit within innodb_additional_mem_pool_size, by the way. If …; so do temporary tables. The following query creates at least 2 temporary tables: select * from ( select * from …
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
… called email address ). Seems simple enough right? My two tables each contain 5M rows of data. let’s … For these tests I left Innodb with a 256M buffer pool, or roughly with 9% of the total data … 4GB of memory allocated to memcached (full dataset fits ): Here you can see that our “transactions†…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… couple of important implications: when the data does not fit in the buffer pool, fast index creation provides even better performance as compared… tmp table | 19.805849 | | restoring secondary keys | 53.885502 | . . . +——————————+———–+ 19 rows in set (0.00 sec) So, as expected, a small buffer pool had a huge impact on ALTER TABLE with the optimization disabled, and absolutely no…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
…_tree_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool_size and large innodb_log… speed because we had tables partitioned and so no table was more than 15GB in size (allowing to fit all BTREE indexes in… of data in single table was taking a week due to amount of indexes and the fact they poorly fit in memory.
Comment: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 12, 2013 By 7 Comments
… then, the tests were only done when the data fits in the buffer pool. There are many workloads that are significantly different from… sets larger than the buffer pool. Note: It would be very nice if InnoDB had multiple buffer pools and supported placing tables into named BPs, or if it supported pinning tables into the BP. But…
Comment: InnoDB compression woes
May 20, 2011 By 14 Comments
… table that’s too big to fit in memory (eg. a 30G table and a 20G buffer pool) into one that fits (eg. compress the 30G table into 15G which now fits in the 20G buffer pool). But…
Post: Predicting Performance improvements from memory increase
August 7, 2008 By 6 Comments
…see limited gains – if your working set already fits in 16GB you may not see any …example if your main queries do full table scan of 100GB table it does not matter if you …buffer pool size. Some threads which would be safely sleeping waiting on IO completion are now finding their data in buffer pool…
Comment: Why MySQL could be slow with large tables ?
September 23, 2011 By 141 Comments
…fit your data into memory” in a database context means “have a big enough buffer pool to have the table/db fit completely in RAM”. In InnoDB, have innodb_buffer_pool_size > the size of your database (or at least your table). In…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
… clustered around most recent id values. The table (and index) is much larger than buffer pool size. The first problem was replication lag… times as couple of partitions which were actively used could fit in buffer pool completely so replication became CPU bound (single thread) instead…
