performance - Slow MySQL Updates/Inserts/Deletes -


i seem having slow inserts, updates , deletes on tables on specific database mysql. not lot of data in tables (from 2k 20k). small number of columns (5-10), indexes (two of them), , no duplicate index issue. i'm running mysql 5.0.45 myisam.

i run following query , takes 5-7 seconds:

update accounts set updated_at = '2010-10-09 11:22:53' id = 8;

selects seem come right away.

explain gives me following:

+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows |       | +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ |  1 | simple      | accounts | index | null          | primary | 4       | null | 1841 | using index |  +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

the profiler doesn't show significant data other seemingly high number of context switches:

+----------------------+----------+-------------------+---------------------+ | status               | duration | context_voluntary | context_involuntary | +----------------------+----------+-------------------+---------------------+ | (initialization)     | 0.000057 |                 0 |                   0 |  | checking permissions | 0.000008 |                 0 |                   0 |  | opening tables       | 0.000013 |                 0 |                   0 |  | system lock          | 0.000005 |                 0 |                   0 |  | table lock           | 0.000005 |                 0 |                   0 |  | init                 | 0.000061 |                 0 |                   0 |  | updating             | 0.000101 |                 0 |                   0 |  | end                  | 7.957233 |              7951 |                   2 |  | query end            | 0.000008 |                 0 |                   0 |  | freeing items        | 0.000011 |                 0 |                   0 |  | closing tables       | 0.000007 |                 1 |                   0 |  | logging slow query   | 0.000002 |                 0 |                   0 |  +----------------------+----------+-------------------+---------------------+

this might help:

+----------------------+----------+-----------------------+---------------+-------------+ | status               | duration | source_function       | source_file   | source_line | +----------------------+----------+-----------------------+---------------+-------------+ | (initialization)     | 0.000057 | check_access          | sql_parse.cc  |        5306 |  | checking permissions | 0.000008 | open_tables           | sql_base.cc   |        2629 |  | opening tables       | 0.000013 | mysql_lock_tables     | lock.cc       |         153 |  | system lock          | 0.000005 | mysql_lock_tables     | lock.cc       |         162 |  | table lock           | 0.000005 | mysql_update          | sql_update.cc |         167 |  | init                 | 0.000061 | mysql_update          | sql_update.cc |         429 |  | updating             | 0.000101 | mysql_update          | sql_update.cc |         560 |  | end                  | 7.957233 | mysql_execute_command | sql_parse.cc  |        5122 |  | query end            | 0.000008 | mysql_parse           | sql_parse.cc  |        6116 |  | freeing items        | 0.000011 | dispatch_command      | sql_parse.cc  |        2146 |  | closing tables       | 0.000007 | log_slow_statement    | sql_parse.cc  |        2204 |  | logging slow query   | 0.000002 | dispatch_command      | sql_parse.cc  |        2169 |  +----------------------+----------+-----------------------+---------------+-------------+

additional info: it's running on centos-5 vps 4 gigs of ram guaranteed. no index on updated_at column , not triggers anywhere.

[new things tried]

  1. created new table (using like) running innodb , inserted records 1 of affected tables. (same problem)
  2. backed database , restored different database within same server instance. (same problem)
  3. restored same backup local machine , didn't have problem.
  4. tried database within same mysql server instance has problem database , other database (a wordpress db) ran updates/inserts/deletes fine.
  5. restarted mysqld , restarted entire server last night (same problem)
  6. updated mysql version 5.0.77 (same problem)
  7. deleted indexes 1 of affected tables (same problem)

any ideas @ next or might issue? seems more of recent problem though can't when started show exactly.

if have variable length rows, might need run optimize table occasionally.


Comments

Post a Comment

Popular posts from this blog

asp.net - repeatedly call AddImageUrl(url) to assemble pdf document -

i'm using abcpdf , i'm curious if can recursively call addimageurl() function assemble pdf document compile multiple urls? something like: int pagecount = 0; int theid = thedoc.addimageurl("http://stackoverflow.com/search?q=abcpdf+footer+page+x+out+of+", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetochain(theid); } pagecount = thedoc.pagecount; console.writeline("1 document page count:" + pagecount); //flatten document (int = 1; <= pagecount; i++) { thedoc.pagenumber = i; thedoc.flatten(); } //now try again theid = thedoc.addimageurl("http://stackoverflow.com/questions/1980890/pdf-report-generation", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetoc...
Read more

java - Android recognize cell phone with keyboard or not? -

in android app, want detect user's device if device has keyboard(like motorola milestone) , show corresponding user interface. guys know how that? ! regards justicepenny http://developer.android.com/reference/android/content/res/configuration.html public int keyboard the kind of keyboard attached device. 1 of: keyboard_nokeys, keyboard_qwerty, keyboard_12key. that's it!
Read more

iphone - How would you achieve a LED Scrolling effect? -

how achieve led scrolling effect example below? led sign - led ticker emulator iphone , ipad http://img.skitch.com/20101201-rsfh4p1bajb1wp94k466pdpiuj.preview.jpg sometimes remunerates comment in other posts :) have put code you, should implement font '8pin matrix' realistic feeling. uilabel *label; - (void)viewdidload { [super viewdidload]; label = [[uilabel alloc]initwithframe:cgrectmake(10, 50, 300, 20)]; label.font = [uifont fontwithname:@"courier-bold" size:16.0]; label.backgroundcolor = [uicolor blackcolor]; label.textcolor = [uicolor greencolor]; label.linebreakmode = uilinebreakmodecharacterwrap; // otherwise "…" label.text = @"this led text scrolled "; // blanks space between [self.view addsubview:label]; [nstimer scheduledtimerwithtimeinterval:0.12f target:self selector:@selector(scrolllabel) use...
Read more