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
Innodb innodb_buffer_pool_size?
Hi All. I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My system is: 2.6.32-279.19.1.el6.centos.plus.x86_64 CentOS release 6.3 (Final) mysql-server-5.0.58 My current setting: innodb_buffer_pool_size = 1024M (my.cnf attached) From show innodb status: -- BUFFER POOL AND MEMORY -- Total memory allocated 1250582306; in additional pool allocated 12842496 Buffer pool size 65536 Free buffers 0 Database pages 61505 Modified db pages 86 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 96616, created 97554, written 126547 0.80 reads/s, 0.20 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Size of innodb tables: du -h /mysql/ibdata1 1.8G/mysql/ibdata1 My question: how to tell if my innodb_buffer_pool_size is ok? Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? Does Free buffers 0 mean that I should make it larger? Eventually what else to check? Best regards, Rafal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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
COM_EXECUTE_STMT fails and I don't know why
Hello everyone, I'm sending a COM_EXECUTE_STMT message and the server returns an: Error 1048 - #23000 - Column 'number_tinyint' cannot be null The query is like this: insert into numbers ( number_tinyint, number_smallint, number_mediumint, number_int, number_bigint, number_decimal, number_float, number_double ) values ( ?, 679, 778, 875468, 17654, 198.657809, 432.8, ?) And what I send in is: 0: 18 00 00 00 17 01 00 00 . . . . . . . . 1: 00 00 01 00 00 00 00 00 . . . . . . . . 2: 01 01 05 0a 29 5c 8f c2 . . . . ) \ . . 3: f5 b0 58 40 . . X @ 18 00 00 00 - size 00 - sequence 17 - type 01 00 00 00 - statement id 00 - flags 01 00 00 00 - iteration-count 00 00 - null bitmap 01 - new params bound flag 01 - byte type 05 - double type 0a - byte value - 10 29 5c 8f c2 f5 b0 58 40 - double value The statement parameters are 10 (for the tinyint column) and 98.765 (for the double column). From what I can see the message is encoded correctly but it always fails for some reason. Am I missing something in here? - Maurício Linhares http://mauricio.github.io/ - http://twitter.com/#!/mauriciojr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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
