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. awall...@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