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@ihouseweb.com
(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