List:General Discussion« Previous MessageNext Message »
From:Kyong Kim Date:January 27 2010 7:06am
Subject:Re: optimization
View as plain text  
On Tue, Jan 26, 2010 at 11:23 AM, Keith Murphy <bmurphy@stripped> wrote:
> You absolutely *should not* convert the mysql database to InnoDB.
>
> Read the above sentence again :)
>
> All others, unless you had a specific reason not to do so, yes, I would
> convert them.
>
> keith
>
> On Tue, Jan 26, 2010 at 2:18 PM, John G. Heim <jheim@stripped> wrote:
>
>> Just to be clear, you're suggesting I convert all of the spamassassin,
>> drupal, and mediawiki tables to innodb too? Or just my own database? What
>> about the mysql database itself? I wouldn't convert those tables, would I?
>>
>> ----- Original Message ----- From: "Keith Murphy" <bmurphy@stripped>
>> To: <mysql@stripped>
>> Sent: Tuesday, January 26, 2010 11:06 AM
>> Subject: Re: optimization
>>
>>
>>
>> ♫
>> I would recommend the same to you about reading High Perf. MySQL as Baron,
>> et al wrote a great book about performance on MySQL. That being said, it
>> has
>> been my experience that in 99% of client cases they don't really need to
>> run
>> two different types of tables. If I were you, I would use InnoDB
>> exclusively
>> unless there is legitimate reason to do otherwise. In an environment that
>> is
>> running 25% writes and a decent query rate you are bound to have contention
>> issues with MyISAM. While there are always going to be edge cases for
>> MyISAM, your default should be innodb and your config should reflect this.
>>
>> Changing your tables to InnoDB is a simple ALTER TABLE which you can script
>> if there are a number of tables to convert.  Allocate as much of your
>> available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of
>> total RAM) and I bet you would see a dramatic difference. That is
>> simplifying things somewhat, but should give an idea.
>>
>>
>> keith
>>
>>
>> On Tue, Jan 26, 2010 at 11:53 AM, mos <mos99@stripped> wrote:
>>
>>  Get yourself a copy of the book High Performance MySQL 2nd Edition
>>> Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English
>>> ISBN-10: 0596101716 ISBN-13: 978-0596101718
>>>
>>> Here is a brief preview of the first edition:
>>>
>>>
> http://books.google.ca/books?id=iaCCQ13_zMIC&printsec=frontcover&dq=high+performance+mysql&cd=1#v=onepage&q=&f=false
>>>
>>> Mike
>>>
>>> At 10:19 AM 1/26/2010, John G. Heim wrote:
>>>
>>>  From: "Jaime Crespo Rincón" <jcrespo@stripped>
>>>>
>>>> Sent: Monday, January 25, 2010 5:30 PM
>>>>
>>>>
>>>> 2010/1/25 John G. Heim <jheim@stripped>:
>>>>
>>>>  I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It
> is
>>>>> running the latest mysql-server from debian lenny (5.0.1). I have
>>>>> databases
>>>>> for drupal, moodle, spamassassin, horde3, and a small database for
>>>>> departmental stuff.
>>>>>
>>>>> The problem is that inserts/updates are sometimes very slow, on the
>>>>> order
>>>>> of
>>>>> a minute. I am hoping somebody can sspot something wrong in my
> config.
>>>>> Here's the optimization settings section (for your convenience). The
>>>>> whole
>>>>> my.cnf is reproduced below that:
>>>>>
>>>>>
>>>> Are your databases using MyISAM or InnoDB?
>>>>
>>>> Both. Maybe that's the problem? I started creating database tables for
> my
>>>> own web apps with the default mysql configuration. I believe the default
>>>> database engine is MyISAM. But then I wanted to use foreign keys and I
>>>> saw
>>>> that it required me to use  InnoDB. So I converted some tables to
> InnoDB
>>>> but
>>>> not all. Maybe it was a mistake not to convert all of them.
>>>>
>>>> After that, I installed drupal, moodle, and mediawiki. I haven't looked
>>>> at
>>>> what kind of tables those packages create. They may not specify it and
>>>> the
>>>> default is still whatever it is when you install mysql, MyISAM I think.
>>>>
>>>>  * If MyISAM, you could be suffering contention problems on writes
>>>>
>>>>>
>>>>>  because of full table locks. No easy solution but engine change
> or
>>>> database sharding. Also key_buffer, (and the other buffers) coud be
>>>> too small for 16GB of RAM. Are you really using more thant 10% of it?
>>>> You could also disable other engines if unused.
>>>> * If InnoDB, you have not set innodb_buffer_pool_size nor log size.
>>>> You could increase the pool to >50% of ram available.
>>>>
>>>> Those are very general suggestions. It depends a lot on your hardware
>>>> (slow storage?), other apps installed on the same machine or the load
>>>> of the server, among others.
>>>>
>>>>
>>>> Well, it could be that the disks aren't real fast. The server is also
>>>> running a print server (lprng). I don't think that's very CPU intensive
>>>> but
>>>> it might be slowing down writes.
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>>
>>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>>
>>
>> --
>> Chief Training Officer
>> Paragon Consulting Services
>> 850-637-3877
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
>
>
> --
> Chief Training Officer
> Paragon Consulting Services
> 850-637-3877
>
Thread
optimizationJohn G. Heim25 Jan
  • Re: optimizationJaime Crespo Rincón26 Jan
  • Re: optimizationJohn G. Heim26 Jan
    • Re: optimizationmos26 Jan
      • Re: optimizationKeith Murphy26 Jan
  • Re: optimizationJohn G. Heim26 Jan
    • Re: optimizationKeith Murphy26 Jan
      • Re: optimizationKyong Kim27 Jan