On Tue, Jan 26, 2010 at 11:23 AM, Keith Murphy <bmur...@paragon-cs.com> 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 <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 >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
