… working set outstrips the available buffer pool memory but still fits into the cache device, …group = 2 innodb_purge_threads = 1 innodb_fast_shutdown = 1 #not innodb options (fixed) back_…table_size = 64M serverid = 101 key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert…
Post: Replication of MEMORY (HEAP) Tables
October 15, 2010 By 7 Comments
… what you can do ? Use Innodb Table Instead Innodb is quite fast when it fits in memory so for most applications this performance… specifying replicate-ignore-table=db.memory_table. Note you should not be using STATEMENT level replication with INSERT … SELECT into this memory table for this to…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… means index is very small and also very fast to build by insertion as insertions happen in the “end” of index tree. I… of memory. Blue Columns in this case is the default configuration, second is with expand_fast_index_creation=1 which builds table with… indexing, such as inserting data in random order can become very slow when index does not fit to memory any more. Creating…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… come up with “Persistent DRAM” memory device, which would offer some small amount, say 512M memory which is battery backed up just… OPTIMIZE rebuilds the whole table, it may last way too long for your 150 GB table! If you have inserted in an ascending…. Then a full table scan would be fast. Maybe the table has become fragmented? Or maybe you have not inserted in the ascending order…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… special queries memory usage might be even larger – bulk inserts may allocate bulk_insert_buffer_size bytes of memory if done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE… you have complex queries. Sorting 10 rows will be as fast with 1MB sort buffer as with 16MB (actually 16MB might…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… is a lot faster than building them through insertion, especially for tables much larger than memory and large uncorrelated indexes you might be… in this table is using “fast_index_creation=0″ which allows to disable fast index creation in Percona Server and force complete table to…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… number of benefits – caching data and index in memory -so cached lookups are very fast, clustering by primary key – so for disk… surely makes inserts slower as they are random and page splits have to happen. Full table scan for this table would also be… – it is often many images from one domain/album are inserted at about same time which makes access locality much better…
Post: Optimizing InnoDB for creating 30,000 tables (and nothing else)
December 22, 2011 By 5 Comments
…30,000 tables in InnoDB. That time is now a memory. We have customers with a lot more tables than a…exciting mysqltest language that will create 30,000 identical tables, insert a row into each of them and then drop…initial size. This also did not help. So how fast is InnoDB under all of this? Am I …
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…memory – Queries are constantly being invalidated from query cache by table updates, this means number of queries in cache and memory… inserts so you can see how much of inserted queries are used: Qcache_hits/Qcache_inserts…. It is not that fast Query Cache is fast compared to running the queries …
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… entries are inserted in the index can affect performance dramatically. Table Size This is the worst trouble maker. Typically load starts fast but… Index BTREE has to be built by insertion and data does not fit in memory any more. I’ve seen cases when… we had tables partitioned and so no table was more than 15GB in size (allowing to fit all BTREE indexes in memory) plus…
