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
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
> been my experience that in 99% of client cases they don't really need to
> two different types of tables. If I were you, I would use InnoDB
> unless there is legitimate reason to do otherwise. In an environment that
> 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.
> 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:
>> 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
>>>> for drupal, moodle, spamassassin, horde3, and a small database for
>>>> departmental stuff.
>>>> The problem is that inserts/updates are sometimes very slow, on the
>>>> a minute. I am hoping somebody can sspot something wrong in my config.
>>>> Here's the optimization settings section (for your convenience). The
>>>> 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
>>> that it required me to use InnoDB. So I converted some tables to InnoDB
>>> 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
>>> what kind of tables those packages create. They may not specify it and
>>> 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
>>> 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:
> Chief Training Officer
> Paragon Consulting Services
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
Chief Training Officer
Paragon Consulting Services