List:General Discussion« Previous MessageNext Message »
From:Singer Wang Date:July 3 2013 12:19am
Subject:Re: database perfomance worries
View as plain text  
O
On 2013-07-02 5:31 PM, "Andy Wallace" <awallace@stripped> wrote:

> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

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