List:General Discussion« Previous MessageNext Message »
From:Keith Murphy Date:January 26 2010 7:23pm
Subject:Re: optimization
View as plain text  
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