… discussion is brewing. You pointed out an important use case for PARTITIONing (“uneven reads”, Re: #1); let me rephrase the use… that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non-PARTITIONed. When PARTITIONed, first it has to find and…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… Using the –session-init-query command-line option, we set the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the… before applying the captured query traffic. This is an important step because update statements that match no rows or insert statements that have… chunk 3. The result is similar for every single graph, each chunk warmed up the buffer pool for the next one. As an…
Post: Unused indexes by single query
September 12, 2008 By 14 Comments
…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 5 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: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…single table with an auto-incrementing integer PK and a secondary UNIQUE KEY. The queries being run against this table were almost exclusively INSERT…queries per second all trying to lock and update the same row is not going to end well; deadlocks, rollbacks, contention, and…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…single node, a deadlock is fairly easy to see by looking at all transactions waiting for locks and checking for…and insert some data: node2 mysql> create table autoinc ( i int unsigned not null auto_increment primary key, j varchar(32) ); Query…> update autoinc set j=”node1″ where i = 1; Query OK,…
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 8 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…
