…: memory mapping for the datafiles. That can be enabled by –myisam_use_mmap=1 startup option. In this case instead of systems call MyISAM will use memcpy function. There is a memory addressing limit… ro) UPDATE primary key (update-key) UPDATE non key column (updaye-nokey) OLTP rw queries, SELECT/INSERT/DELETE/UPDATE queries (OLTP rw) batch select queries…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… unnoticed with MyISAM storage engine. This hidden corruption may later cause crashes wrong query results and further data corruption. Partial updates. MyISAM is… in batch processing and other means Exported data If you export data from OLTP system for example to perform data analyses – MyISAM…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… for 1000 rows in a batch vs 40 seconds for single row statements. So with single statement MyISAM was about 10% faster, which is quite expected but with 1000 rows in the batch the difference grew… it did not have a bug. INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE all should be affected, though I have not tested…
Post: How many partitions can you have ?
December 5, 2009 By 19 Comments
… KEY UPDATE completes in 22 seconds for 1 partition and 250 for 1000 partitions – which is over 10x difference. Both MyISAM and Innodb are affected by this issue. The Update path for MyISAM without indexes took 10 seconds… the batch. With 100 rows per batch and with 10000 rows per batch performance was not significantly different from 1000 rows per batch so…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
… like to see semi-realtime update for them so we can’t simply update them nightly using batch job. What to do in… simply the database which is not replicated) to log updates in heap/myisam/archive table and aggregate it in the database for… implement all updates from the chunk in single transaction saving on log commits significantly. I mentioned you can use MyISAM or HEAP…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…purge_batch_…myisam_recoverblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_repair_threadsblogpercona.commanual myisam_sort_buffer_sizeblogpercona.commanual myisam_stats_methodblogpercona.commanual myisam…commanual updatable_views…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… read to be Innodb – even if writes are done in MyISAM table. So why was this done, being pretty bad for… are less general purpose. For example if you’re doing batch processing which is well indexed you might chop transactions and… to MyISAM table so we’ll not see any write activity. Other transaction which happes to be simple primary key update is…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… to most storage engines, it certainly can be applied for MyISAM and Innodb. I remember there were plans to implement such… fine but if you have long running transactions, for example batch jobs you may end up in trouble or be forced… updates, are they stored in the same position or in another location ? Does row ever split into multiple parts like with MyISAM…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…LOAD DATA INFILE be optimized same way as for MyISAM tables by separate phase of building Indexes ? Will be…for cases when number of rows in auto-increment batch insert is not known as we do not …where clause (think for example about running not-indexed UPDATE which has to lock all rows in Innodb …
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… to 50% of my RAM, and then when I tested MyISAM FT index, I reallocated that memory to into key_buffer… the MySQL database or else periodically batch-insert data that has changed since last time you updated the index. You can use… type that supports direct row-by-row updates. But you would still have to update the RT index as data changes, using…
