…they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse – it is … indexes (since last restart of mysqld) by single query SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME…. For some versions of the patch, where the table has only the INDEX_NAME and the …
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…query cache is “coarse invalidation”. That is, as soon as you change a single row in any table, the query cache entries for every query…and should be fast to access, since it can be indexed appropriately for your queries. Using INSERT .. SELECT for summary tables The INSERT…must be updated. Updating …
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
…for a way to improve update performance for the application and I was replacing single value UPDATE…INSERT, and it does not use this function so it did not have a bug. INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE…
Post: How multiple disks can benefit for single client workload ?
April 26, 2008 By 4 Comments
… look how single query is executed for Innodb storage engine to be more specific. Lets look at update queries (from replication thread for example). When update is… containing old row version (and possibly index pages you’re to modify) – even if you’re doing INSERT you will need to…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… value, a simple insert query like a single-row or multi-row INSERT/REPLACE uses a…and the performance but with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE …and concurrency. How can I solve this problem for INSERT IGNORE? As I informed you before, it is not documented that INSERT…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…Query execution So I really have data for InfoBright and MonetDB, let see how fast they are in different queries. First favorite query for…INSERT / UPDATE / DELETE statements (and space is price for…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… mixed read / write queries, InnoDB locks index for write operation and thus keeps all selects waiting when update/insert is done. This is…queries and write queries at the same time. InnoDB again uses single global mutex for adaptive_search (single mutex for ALL table and ALL indexes), so write query…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…00 sec) For…and their relative use activity. I just summed rows modified and updated…query hacking and we get a query which will return statements to initialize key buffers according to table sizes and…Inserting data to about 20 tables I’ve seen performance gains as much as 10x compared to using single…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…for insert buffer merges, asynchronous log flushes, read-ahead and flushing of dirty buffers. Normal reads originated from query…for server activity Number of rows inserted 143, updated 3000041, deleted 0, read 24865563 0.00 inserts/s, 0.00 updates…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 6 Comments
…insert statement or Fetching data you’re planning to update/delete and having multiple queries…for modification – spread it out and have separate select and update queries. In such case slave will only need to run UPDATE Example: UPDATE…
