List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:July 2 2013 9:28pm
Subject:database perfomance worries
View as plain text  
We are on a quest to improve the overall performance of our database. It's generally
working pretty well, but we periodically get big slowdowns for no apparent reason. A
prime example today - in the command line interface to the DB, I tried to update one
record, and got:

     mysql> update agent set number_of_emails = 5 where acnt = 'AR287416';
     Query OK, 1 row affected (36.35 sec)
     Rows matched: 1  Changed: 1  Warnings: 0

36 seconds to update one table? The primary key is `acnt`. If I run the same (basic)
command again a few seconds later, I get:

     mysql> update agent set number_of_emails = 15 where acnt = 'AR287416';
     Query OK, 1 row affected (0.00 sec)
     Rows matched: 1  Changed: 1  Warnings: 0

Why would we be getting such huge variations? We're running Solaris 10 on i386, with
4 processors and 16GB of memory, MySQL 5.1.46-log. We are working out a plan to upgrade
to MySQL 5.6, but I certainly don't want to depend on that upgrade to solve all
performance
problems.

CREATE TABLE `agent`
(
   `acnt` varchar(20) NOT NULL,
   `passwd` varchar(20) NOT NULL,
   `package` char(2) DEFAULT NULL,
   `data_template` varchar(20) DEFAULT 'NULL',
   `default_search_type` enum('1','2','3') NOT NULL DEFAULT '1',
   `status` enum('A','T','P','C','D','X','S') NOT NULL
   `flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags',
   `aliases` varchar(4000) NOT NULL DEFAULT '',
   `offices` varchar(4000) NOT NULL DEFAULT '',
   `license_no` varchar(40) NOT NULL DEFAULT '',
   `agent_code` varchar(20) DEFAULT NULL,
   `office_code` varchar(20) DEFAULT NULL,
   `parent_acnt` varchar(20) DEFAULT NULL,
   `number_of_agentlinks` int(11) DEFAULT NULL,
   `number_of_emails` int(11) DEFAULT NULL,
   `fname` varchar(30) DEFAULT NULL,
   `lname` varchar(30) DEFAULT NULL,

<<whole bunch of other fields>>

   PRIMARY KEY (`acnt`),
   KEY `parent_acnt` (`parent_acnt`),
   KEY `status` (`status`),
   KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


-- 
Andy Wallace
iHOUSEweb, Inc.
awallace@stripped
(866) 645-7700 ext 219
--
"Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week
debugging Monday's code."
- Christopher Thompson
Thread
database perfomance worriesAndy Wallace2 Jul
  • Re: database perfomance worriesReindl Harald2 Jul
    • Re: database perfomance worriesAndy Wallace2 Jul
      • Re: database perfomance worriesReindl Harald2 Jul
  • Re: database perfomance worriesSinger Wang3 Jul
    • Re: database perfomance worriesspameden3 Jul
      • RE: database perfomance worriesRick James3 Jul
        • Re: database perfomance worriesDivesh Kamra5 Jul
          • Re: database perfomance worriesReindl Harald5 Jul