RE: Innodb innodb_buffer_pool_size?
Hi claudio, The best value for innodb buffer pool size is considered to be 60 to 70 percent of ram. So in your case, this must be around 4 gb. That would work better. Regards. Vikas -Original Message- From: Claudio Nanni claudio.na...@gmail.com Sent: 16-05-2013 14:12 To: Rafał Radecki radecki.ra...@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio
Re: Innodb innodb_buffer_pool_size?
Am 16.05.2013 11:01, schrieb Vikas Shukla: The best value for innodb buffer pool size is considered to be 60 to 70 percent of ram. So in your case, this must be around 4 gb. That would work better. hwo would a innodb_buffer_pool larger than the whole database make anything better? the best value is as large as the expected dataset, more is wasting system ressources -Original Message- From: Claudio Nanni claudio.na...@gmail.com Sent: 16-05-2013 14:12 To: Rafał Radecki radecki.ra...@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
RE: Innodb innodb_buffer_pool_size?
Sorry I failed to specify ghatna is only applicable when the database is quiet large. Vikas -Original Message- From: Reindl Harald h.rei...@thelounge.net Sent: 16-05-2013 14:43 To: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Am 16.05.2013 11:01, schrieb Vikas Shukla: The best value for innodb buffer pool size is considered to be 60 to 70 percent of ram. So in your case, this must be around 4 gb. That would work better. hwo would a innodb_buffer_pool larger than the whole database make anything better? the best value is as large as the expected dataset, more is wasting system ressources -Original Message- From: Claudio Nanni claudio.na...@gmail.com Sent: 16-05-2013 14:12 To: Rafał Radecki radecki.ra...@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Re: Innodb innodb_buffer_pool_size?
Am 16.05.2013 10:11, schrieb Rafał Radecki: I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. innodb_buffer_pool_size = 1024M Size of innodb tables: du -h /mysql/ibdata1 1.8G/mysql/ibdata1 in the best case innodb_buffer_pool_size as big as innodb-data signature.asc Description: OpenPGP digital signature
Re: Innodb innodb_buffer_pool_size?
Am 16.05.2013 13:31, schrieb Ilya Kazakevich: hwo would a innodb_buffer_pool larger than the whole database make anything better? the best value is as large as the expected dataset, more is wasting system ressources You also may need space for adaptive indexes, locks etc so it is recommended to have pool size about 110% of your data. yes but that is far away from 4 GB as a reply to du -h /mysql/ibdata1 1.8G /mysql/ibdata1 which never got shrinked even if your real data are only half of it after deletions 60-70% of RAM is a naive not to say dumb reccomendation, especially in times where a ordinary workstation has 16 GB memory and it will bring you no benefit while it may lead in troubles if whatever process allocates a huge amount of memory sooner or later and the system starts to swap or OOM killer get in action and is killing your mysqld as first process signature.asc Description: OpenPGP digital signature
Re: Innodb innodb_buffer_pool_size?
Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio
