Swapping is really bad. Shrink buffer_pool as needed to avoid swapping. The 70-80% 'rule' works for 'most' machines today, because most machines have a lot more than 2GB 'available' to MySQL. As you observed, 2GB box would probably swap if buffer_pool were 1.4GB, so 800-900M is better. Meanwhile, a 20GB box would be fine with 14GB, even 16GB. The best formula would be something more complex than a simple percent. I recommend "70% of available ram" because it safely covers most machines today. Then I backpeddle like this when I see that the machine is smaller. (Sorry, I was assuming you had a bigger machine, and had not allocated as much as you could.) The old default of 8M is terrible. Even the new default is puny (most of the time).
> -----Original Message----- > From: Igor Shevtsov [mailto:nixofort...@gmail.com] > Sent: Tuesday, April 16, 2013 8:38 AM > To: mysql@lists.mysql.com > Subject: Re: Mesaure query speed and InnoDB pool > > Hi Rick, > I thought you have to dedicate 70-80% of available RAM not a total RAM. > Saying if I have 2 gig of RAM on my exclusively innodb box, and I > dedicate 1.4Gig to innodb pool, my 64-bit linux machine will start > swapping heavily. > If I set it to 800-900M, it just fine and I have like 100M of RAM left > for some occasional process. I did try it. > Thanks, > Igor > > On 16/04/13 16:21, Rick James wrote: > > Run your query twice; take the second time. For most queries the > first run brings everything into cache, then the second gives you a > repeatable, though cached, timing. > > > > Please provide EXPLAIN SELECT, SHOW CREATE TABLE, and we will > critique your indexes and query plan. > > > > Handler* is another way to get consistent values. These numbers are > unaffected by caching. > > > > 1GB buffer_pool? You have only 2GB of available RAM? Normally, if > you are running only InnoDB, the buffer_pool should be set to about 70% > of available RAM. > > > >> -----Original Message----- > >> From: Ananda Kumar [mailto:anan...@gmail.com] > >> Sent: Tuesday, April 16, 2013 2:06 AM > >> To: Ilya Kazakevich > >> Cc: MySQL > >> Subject: Re: Mesaure query speed and InnoDB pool > >> > >> Does your query use proper indexes. > >> Does your query scan less number blocks/rows can you share the > >> explain plan of the sql > >> > >> > >> On Tue, Apr 16, 2013 at 2:23 PM, Ilya Kazakevich < > >> ilya.kazakev...@jetbrains.com> wrote: > >> > >>> Hello, > >>> > >>> I have 12Gb DB and 1Gb InnoDB pool. My query takes 50 seconds when > >>> it reads data from disk and about 2 seconds when data already > exists > >>> in pool. And it may take 10 seconds when _some_ pages are on disk > >>> and > >> some are in pool. > >>> So, what is the best way to test query performance? I have several > >> ideas: > >>> * Count 'Innodb_rows_read' or 'Innodb_pages_read' instead of actual > >>> time > >>> * Set pool as small as possible to reduce its effect on query speed > >>> * Set pool larger than my db and run query to load all data into > >>> pool and measure speed then > >>> > >>> How do you measure your queries' speed? > >>> > >>> Ilya Kazakevich > >>> > >>> > >>> -- > >>> MySQL General Mailing List > >>> For list archives: http://lists.mysql.com/mysql > >>> To unsubscribe: http://lists.mysql.com/mysql > >>> > >>> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
