List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:July 5 2013 3:43am
Subject:Re: database perfomance worries
View as plain text  
*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 <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."


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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