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 <jh...@math.wisc.edu> 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" <bmur...@paragon-cs.com> > To: <mysql@lists.mysql.com> > 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 <mo...@fastmail.fm> 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" <jcre...@warp.es> >>> >>> Sent: Monday, January 25, 2010 5:30 PM >>> >>> >>> 2010/1/25 John G. Heim <jh...@math.wisc.edu>: >>> >>> 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=mo...@fastmail.fm >>> >>> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com >> >> >> > > -- > 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=bmur...@paragon-cs.com > > -- Chief Training Officer Paragon Consulting Services 850-637-3877
