'Performance' Tag

• DELETE, don't INSERT

  June 27, 2012

  Have just read INSERT, Don’t DELETE by Aaron Brown, and have some lengthy response, which is why I write this post instead of commenting on said post. I wish to offer my counter thought and suggest that DELETEs are probably the better choice. Aaron suggests that, when one wishes to purge rows from some table, [...]

  posted in MySQL by shlomi

• Self throttling MySQL queries

  November 1, 2011

  Recap on the problem: A query takes a long time to complete. During this time it makes for a lot of I/O. Query's I/O overloads the db, making for other queries run slow. I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the [...]

  posted in MySQL by shlomi

• On generating unique IDs using LAST_INSERT_ID() and other tools

  February 2, 2011

  There's a trick for using LAST_INSERT_ID() to generate sequences in MySQL. Quoting from the Manual: Create a table to hold the sequence counter and initialize it: mysql> CREATE TABLE sequence (id INT NOT NULL); mysql> INSERT INTO sequence VALUES (0); Use the table to generate sequence numbers like this: mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); mysql> [...]

  posted in MySQL by shlomi

• How often should you use OPTIMIZE TABLE? - followup

  October 4, 2010

  This post follows up on Baron's How often should you use OPTIMIZE TABLE?. I had the opportunity of doing some massive purging of data from large tables, and was interested to see the impact of the OPTIMIZE operation on table's indexes. I worked on some production data I was authorized to provide as example. The [...]

  posted in MySQL by shlomi

• Views: better performance with condition pushdown

  May 20, 2010

  Justin's A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer's site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with [...]

  posted in MySQL by shlomi

• Reducing locks by narrowing primary key

  May 4, 2010

  In a period of two weeks, I had two cases with the exact same symptoms. Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that INSERTs were causing the locks. In both cases, tables were InnoDB. In both cases, there was a [...]

  posted in MySQL by shlomi

• Misimproving performance problems with INSERT DELAYED

  January 14, 2010

  INSERT DELAYED may come in handy when using MyISAM tables. It may in particular be useful for log tables, where one is required to issue frequent INSERTs on one hand, but does not usually want or need to wait for DB response on the other hand. It may even offer some performance boost, by aggregating [...]

  posted in MySQL by shlomi

• On restoring a single table from mysqldump

  December 1, 2009

  Following Restore one table from an ALL database dump and Restore a Single Table From mysqldump, I would like to add my own thoughts and comments on the subject. I also wish to note performance issues with the two suggested solutions, and offer improvements. Problem relevance While the problem is interesting, I just want to [...]

  posted in MySQL by shlomi

• Performance analysis with mycheckpoint

  November 12, 2009

  mycheckpoint (see announcement) allows for both graph presentation and quick SQL access to monitored & analyzed data. I'd like to show the power of combining them both. InnoDB performance Taking a look at one of the most important InnoDB metrics: the read hit ratio (we could get the same graph by looking at the HTML [...]

  posted in MySQL by shlomi

• How to calculate a good InnoDB log file size - recap

  October 20, 2009

  Following Baron Schwartz' post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I've written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB [...]

  posted in MySQL by shlomi