O
On 2013-07-02 5:31 PM, "Andy Wallace" <[email protected]> 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.
> [email protected]
> (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
>
>
