… to be much larger than table itself) The index build speed is however not only reason I can’t wait for… due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to be sequential…. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… and also very fast to build by insertion as insertions happen in the “end” of index tree… in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row … does the improvement to the index creation speed itself has to be even larger. It…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… and Sphinx, do indexes stay up to date with changes (inserts, updates and deletes)? That’s a good question, because both… individually as you add new data to the MySQL database or else periodically batch-insert data that has changed since last time… an in-memory database, you can get a lot of speed improvement because you’re searching data without touching the disk…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
…Memory ##################################################### Total | 23.59G … Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= … different MySQL configurations, I didn’t try out different MySQL …of data being modified, inserts are done to random …
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
… TEXT, PRIMARY KEY (id) ) Engine=InnoDB; Now you insert some test data into it: mysql> INSERT INTO example -> VALUES ( ->Â Â NULL, ->Â… of values, not the character-size. This means if you insert a 500 character string with all multi-byte characters into… a transaction. It will also impact both read and modification speed, precisely for the added join complexity. However, if you have…
Post: Preprocessing Data
August 8, 2011 By 3 Comments
… to query these new tables. You have now improved the speed of the main page of the website as well as… might want to consider preprocessing your data: On Insert This is when you insert data into the database. Your application or the… TRIGGERs) does additional calculations or data insertions. This adds some overhead when data is being inserted, but according to your business requirements…
Post: How InnoDB handles REDO logging
February 3, 2011 By 19 Comments
…, your transaction will never complete. Both the MySQL configuration and hardware will affect the speed at which InnoDB can log. You can… means you are ensuring that in the event of a MySQL crash the data will still be consistent, however in the… speed at which you can process updates is tightly linked to the speed at which check-pointing can occur. In order to insert…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
…Max checkpoint age 7782360 To speed up flushing we can set dirty pages percentage to zero: mysql> set global innodb_max_dirty…not more than 1(it’s never less than 1): ————————————- INSERT BUFFER AND ADAPTIVE HASH INDEX ————————————- Ibuf: size 1, free …
Post: Is disk Everything for MySQL Performance ?
April 25, 2008 By 7 Comments
…have instead of how many systems they have” on MySQL UC2008 Scalability Panel ? Indeed disks MAY be the …doing all search heavy lifting we only need to insert new data and fetch few rows to show … will unlikely be helpful solving replication delay or speeding up this 3 hours reporting query or 5 …
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
… database dramatically but writes still have to happen at full speed. It also really hurts if you choose replication (rather than… not have to do it for the same table as inserts go to. The trick you can use here is standard “shadow table” trick – use two tables insert into one and process and truncate another. MySQL offers atomic RENAME TABLE call which can…
