List:General Discussion« Previous MessageNext Message »
From:Divesh Kamra Date:July 5 2013 3:01am
Subject:Re: database perfomance worries
View as plain text  
Andy , increase innodb_log_file size to 400mb

DK Sent from Phone

On 03-Jul-2013, at 23:39, Rick James <rjames@stripped> 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:spameden@stripped]
>> 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.
>>>> awallace@stripped
>>>> (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
> 
Thread
database perfomance worriesAndy Wallace2 Jul
  • Re: database perfomance worriesReindl Harald2 Jul
    • Re: database perfomance worriesAndy Wallace2 Jul
      • Re: database perfomance worriesReindl Harald2 Jul
  • Re: database perfomance worriesSinger Wang3 Jul
    • Re: database perfomance worriesspameden3 Jul
      • RE: database perfomance worriesRick James3 Jul
        • Re: database perfomance worriesDivesh Kamra5 Jul
          • Re: database perfomance worriesReindl Harald5 Jul