Set innodb_buffer_pool_size to 70% of _available_ ram. That may be 11G on your 16GB machine, unless you have a lot of other bulky stuff there. Do _not_ make it so large that it leads to swapping. Swapping is much worse on performance than shrinking the buffer_pool.
36 seconds for a single-row UPDATE using the PRIMARY KEY -- Something else _must_ have been interfering. DELETE was suggested; ALTER is another possibility. Even with a totally cold cache, that UPDATE should have taken much less than one second. I suspect the problem will not recur. > KEY `status` (`status`), That index will probably never be used, due to low cardinality. Either DROP it, or make it 'compound'. > `flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags', Consider the SET datatype. 5.6 has some performance improvements, but not related to this query. Please have the slowlog turned on. There could be extra, useful, info in it. > -----Original Message----- > From: spameden [mailto:spame...@gmail.com] > Sent: Tuesday, July 02, 2013 7:28 PM > To: Singer Wang > Cc: Andy Wallace; mysql list > Subject: Re: database perfomance worries > > > > > > 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) > > > > Check if there is some DELETE running for the selected table. > > If there is a DELETE involving whole table it might be locking up > database. > > Look into mysql-slow.log > > Try to optimize your application queries with EXPLAIN. > > > >[!!] InnoDB data size / buffer pool: 7.8G/5.5G Variables to adjust: > >innodb_buffer_pool_size (>= 7G) > > >2 GB innodb_buffer_pool is a joke for a dataset of 33 GB > > >that leads in permanently I/O on mixed load at the chances are high > >that there are times where nothing needed to operate is in the > >buffer_pool and on concurrent load mysqld ends in repeatly swap data in > >and out of the pool > > >at least all repeatly accessed tables should fit permanently in the > >buffer > > it depends on the load and how much data you're acquiring. > > if you have 33GB in total, but only using few same tables in total size of > less than 2GB at the same time it would work just fine. > > for example I have 136GB of data, but my buffer is only about 10Gb, but > most of the queries work just fine (I'm using it for mostly read-only > things). > > but ofc, you need to check your system usage, if mysqld swaps its a bad > thing and most likely you need to either upgrade your hardware or consider > checking your data architecture (i.e. use LIMIT for quieries, add more > indexes, split large tables for a smaller ones which you really update or > store large data in mongodb etc). > > > > > > 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 > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
