From: Keith Murphy Date: January 26 2010 7:23pm Subject: Re: optimization List-Archive: http://lists.mysql.com/mysql/220408 Message-Id: <630828d11001261123ue20a17enb5b6c28aea7d63ee@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=000e0cd23e220812ce047e16373c --000e0cd23e220812ce047e16373c Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 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" > To: > Sent: Tuesday, January 26, 2010 11:06 AM > Subject: Re: optimization > > > > =E2=99=AB > 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 contenti= on > 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 scri= pt > 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% o= f > 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 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=3DiaCCQ13_zMIC&printsec=3Dfrontcover&dq= =3Dhigh+performance+mysql&cd=3D1#v=3Donepage&q=3D&f=3Dfalse >> >> Mike >> >> At 10:19 AM 1/26/2010, John G. Heim wrote: >> >> From: "Jaime Crespo Rinc=C3=83=C2=B3n" >>> >>> Sent: Monday, January 25, 2010 5:30 PM >>> >>> >>> 2010/1/25 John G. Heim : >>> >>> 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 defaul= t >>> 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 InnoD= B >>> 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=3Dmos99@fastmail.= fm >>> >>> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=3Dbmurphy@stripped >> >> >> > > -- > 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=3Dbmurphy@stripped > > --=20 Chief Training Officer Paragon Consulting Services 850-637-3877 --000e0cd23e220812ce047e16373c--