MySQL Forums
Forum List  »  Newbie

CPU Usage during DELETE and OPTIMIZE
Posted by: Mark Welch
Date: May 04, 2009 06:02PM

I'm running MySQL on a VPS (Virtual Private Server), and I've been experiencing some serious problems with CPU over-usage (the VPS doesn't enforce CPU limits).

For my imports, I've solved the problem by simply inserting a sleep(1) command after every NN records during the import process. This keeps CPU usage under 100%.

However, I'm still seeing huge CPU over-usage during DELETE and OPTIMIZE functions.

When importing an update, I delete all existing records that are going to be replaced by the new data, then import the new ones; this might mean deleting 50 or 1,000 records, or 100,000 or 250,000 records (out of 2.5 million records, currently). This simply seems to seize up the server.

I assume that my best option here is to slice up the DELETE just as I've sliced up the INSERTs -- by using a LIMIT option (such as LIMIT 100) and then using sleep(1) before looping. It seems kludgy, but I don't see any alternative.

Likewise, OPTIMIZE seizes up the server -- but here I can't think of any way to prevent this, other than not running the OPTIMIZE command (ever).

I would appreciate any suggestions. Thanks!

Edited 1 time(s). Last edit at 05/04/2009 06:04PM by Mark Welch.

Options: ReplyQuote

Written By
CPU Usage during DELETE and OPTIMIZE
May 04, 2009 06:02PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.