*boah* do *not* recommend blindly a change which leads in the server no longer starting without refer to the documentation and give a hint what happens if you change the setting
http://dev.mysql.com/doc/refman/5.0/en/innodb-data-log-reconfiguration.html Am 05.07.2013 05:01, schrieb Divesh Kamra: > > 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."
signature.asc
Description: OpenPGP digital signature
