…Innodb tables would be stalling every so often causing even very simple queries both reads and writes taking long time…insert is taking so long letting hundreds of AUTO-INC insert to pile up. This was also very interesting case about optimal innodb…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… per second, then you write 1000 times per second! A smart wear leveling …innodb_buffer_pool_size Q36: There have been several MySQL bugs opened about multi-core scalability (concurrent queries, autoincrement, concurrent inserts…We can OPTIMIZE the table, but how long will this take? Is there a …
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… tens of thousands tables) Innodb was better choice mainly …time. It can happen concurrently to select statements but it has to be one insert at the time. Happily inserts…queries to get into trouble. Just one is enough. Lock priorities. By default MySQL…which is blocked by long running select will …
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
…Innodb Tablespace. Insert Buffer size is also restricted to half of the buffer pool size (can be changed via innodb…: mysql> select * from innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id …about Long Transactions the time is… long queries which may make long…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog … small bulks, which do not cause long enough locks to cause the problems….MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… covered queries is great optimization for wide range of queries. [+] …implemented outside of MySQL Storage Engine level for MySQL – reading rows …inserted in different times a lot of scattered reads will be needed. Think about typical use of Innodb… no problem with very long rollbacks on recovery but …
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…time (18:37 friday) System copes fine with these figures. Queries…Com_stmt_send_long_data’, ’0′ ‘…inserts‘, ’83902401′ ‘Qcache_lowmem_prunes’, ’4314′ ‘Qcache_not_cached’, ’294363493′ ‘Qcache_queries…innodb_log_group_home_dir=/var/log/mysql innodb_log_arch_dir=/var/log/mysql innodb_table_locks=0 innodb…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
…time. How much overhead does a trivial WHERE clause add to a MySQL query? To find out, I set my InnoDB…) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t(a) values(current_date); insert …MySQL. The table ended up at 237MB. Now let’s see how long a table scan with no WHERE clause takes: mysql…
Comment: MySQL File System Fragmentation Benchmarks
July 9, 2008 By 28 Comments
…inserted) I got 1121, 1133, and 1166 inserts per second into that one table. using autocommit for innodb inserts I got 1125, and 1158 inserts…time. Data was coming in much faster than it was able to insert. The data queue was getting quite long…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… other path, no matter how long. We even store paths of… PRIMARY KEY (ancestor, descendant) ) ENGINE=InnoDB; Because there isn’t …node. When you insert a subtree, you’re inserting multiple nodes, … of the new location times the number of nodes in…in a single query in MySQL. But we can use MySQL‘s multi…
