Andy , increase innodb_log_file size to 400mb DK Sent from Phone
On 03-Jul-2013, at 23:39, Rick James <rja...@yahoo-inc.com> wrote: > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
