Re: Innodb buffer pool size
Machiel, I'm going to disagree strongly with the previous advice you got. You should NOT configure the buffer pool to be larger than the amount of RAM you have. If part of the buffer pool is swapped out, then swapping it back in is MUCH worse than re-fetching the page. InnoDB doesn't know the difference between in-memory and swapped out. (That's the point of virtual memory; it's invisible to the program.) It assumes that a memory access is fast. If it turns out not to really be a memory access, but instead is a disk access to swap something in, then everything goes very badly. If you search for buffer pool size on mysqlperformanceblog.com, you will get good advice. You should also get a copy of High Performance MySQL, Second Edition. (I'm the lead author.) In short: ignore advice about ratios, and ignore advice about the size of your data. Configure the buffer pool to use the amount of memory available, subtracting what's required for the OS and other things on the computer to run effectively. - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb buffer pool size
In infinite wisdom Machiel Richards machi...@rdc.co.za wrote: The current Innodb buffer pool size is at 4Gb for instance, and the innodb tables then grow to be about 8Gb in size. InnoDB manages the pool as a list, using a least recently used (LRU) algorithm incorporating a midpoint insertion strategy. When room is needed to add a new block to the pool, InnoDB evicts the least recently used block and adds the new block to the middle of the list. (see http://dev.mysql.com/doc/refman/5.1/en/innodb-buffer-pool.html) What would be the appropriate actions for this to ensure the buffers are set to the size to best suit the database needs? - Try to set your innodb_buffer_pool to be the same size as your data. - have monitoring for the innodb_buffer_pool. show innodb status prints out stats about the buffer pool as well -- BUFFER POOL AND MEMORY -- Total memory allocated 84966343; in additional pool allocated 1402624 Buffer pool size 3200 Free buffers 110 Database pages 3074 Modified db pages 2674 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 171380, created 51968, written 194688 28.72 reads/s, 20.72 creates/s, 47.55 writes/s Buffer pool hit rate 999 / 1000 Monitoring free buffers can help in figuring out if the allocated memory is optimum or not. -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Innodb buffer pool size
Hi Guys I just have a quick question. I have done some research into how to determine the size of your Innodb buffer pool. All of the sources I used, specified that the Innodb buffer pool size should be the same size as your database + 10%. However, as far as I understand it, the buffer size also relies on that amount of memory being available. Thus if you increase the buffer size, the amount of memory used will be increased. My thinking however, is what happens when the database size grows bigger than the amount of memory available to the hardware. Say for instance, a server with MySQL also runs other applications. The amount of memory on the server is 32Gb and about 31Gb is already in use. The current Innodb buffer pool size is at 4Gb for instance, and the innodb tables then grow to be about 8Gb in size. What would be the appropriate actions for this to ensure the buffers are set to the size to best suit the database needs?
Re: Innodb buffer pool size
Hi, First thing that comes to my mind is that it is probably the best time to put your application server and database server on different hosts. Having said that, in this case increasing buffer pool size is still advisable as per my understanding. Your swap consumption will go up in that case which is not very good either. But giving only 4 GB to Innodb is even worse for the performance. It is subjective though. You should first check if MySQL is actually using the allotted 4GB or not. If not, increasing the value will not help. Maybe someone can correct me if I'm wrong. :-) Hope that helps! Regards, Nitin From: Machiel Richards machi...@rdc.co.za To: mysql@lists.mysql.com Sent: Thu, May 20, 2010 1:07:43 PM Subject: Innodb buffer pool size Hi Guys I just have a quick question. I have done some research into how to determine the size of your Innodb buffer pool. All of the sources I used, specified that the Innodb buffer pool size should be the same size as your database + 10%. However, as far as I understand it, the buffer size also relies on that amount of memory being available. Thus if you increase the buffer size, the amount of memory used will be increased. My thinking however, is what happens when the database size grows bigger than the amount of memory available to the hardware. Say for instance, a server with MySQL also runs other applications. The amount of memory on the server is 32Gb and about 31Gb is already in use. The current Innodb buffer pool size is at 4Gb for instance, and the innodb tables then grow to be about 8Gb in size. What would be the appropriate actions for this to ensure the buffers are set to the size to best suit the database needs?
Re: Innodb buffer pool size
Well, my question is specifically related to in the event that the buffer usage reaches 100% Quoting Nitin Mehta ntn...@yahoo.com: Hi, First thing that comes to my mind is that it is probably the best time to put your application server and database server on different hosts. Having said that, in this case increasing buffer pool size is still advisable as per my understanding. Your swap consumption will go up in that case which is not very good either. But giving only 4 GB to Innodb is even worse for the performance. It is subjective though. You should first check if MySQL is actually using the allotted 4GB or not. If not, increasing the value will not help. Maybe someone can correct me if I'm wrong. :-) Hope that helps! Regards, Nitin - FROM: Machiel Richards machi...@rdc.co.za TO: mysql@lists.mysql.com SENT: Thu, May 20, 2010 1:07:43 PM SUBJECT: Innodb buffer pool size Hi Guys I just have a quick question. I have done some research into how to determine the size of your Innodb buffer pool. All of the sources I used, specified that the Innodb buffer pool size should be the same size as your database + 10%. However, as far as I understand it, the buffer size also relies on that amount of memory being available. Thus if you increase the buffer size, the amount of memory used will be increased. My thinking however, is whathappens when the database size grows bigger than the amount of memory available to the hardware. Say for instance, a server with MySQL also runs other applications. The amount of memory on the server is 32Gb and about 31Gb is already in use. The current Innodb buffer pool size is at 4Gb for instance, and the innodb tables then grow to be about 8Gb in size. What would be the appropriate actions for this to ensure the buffers are set to the size to best suit the database needs?
Re: RE: Innodb buffer pool size filling up
Machiel, That is how it is supposed to work. You assign a certain amount of memory(RAM) to it and the database engine then manages it. It is highly desirable that this buffer is fully used, and if the growing curve is slow it is because it is not undersized. If you really need more ram for other uses in your system you can lower it a little, but I think you are using this box only for MySQL and a 'good' practice is to use 70/80% of system Ram ONLY for innodb buffer pool. Cheers Claudio On 18 dec 2009 06:34, machiel.richards machiel.richa...@gmail.com wrote: Good Morning all QUOTE: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up I have monitored this issue for the last couple of weeks and even though the buffer pool usage is increasing slowly, it is still heading to become 100% full. It used to vary in the past where it would increase and decrease each day as required, however this does not seem to be happening anymore. Does anybody know whether there is a process that is supposed to manage this or otherwise clear this occasionally that might not be working? We are getting quite a bit of issues with the client wanting to know why this is happening, however thus far I haven't been able to find any conclusive answers, not even through google. Any assistance would be appreciated. Thanking everyone in advance. Regards Machiel -Original Message- From: Jerry Schwartz [mailto: jschwa...@the-infoshop.com] Sent: 01 Dece... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlTo unsubscribe: http:/...
RE: RE: Innodb buffer pool size filling up
Thank you very much. This now explains a lot. From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: 18 December 2009 10:05 AM To: machiel.richards Cc: mysql@lists.mysql.com Subject: Re: RE: Innodb buffer pool size filling up Machiel, That is how it is supposed to work. You assign a certain amount of memory(RAM) to it and the database engine then manages it. It is highly desirable that this buffer is fully used, and if the growing curve is slow it is because it is not undersized. If you really need more ram for other uses in your system you can lower it a little, but I think you are using this box only for MySQL and a 'good' practice is to use 70/80% of system Ram ONLY for innodb buffer pool. Cheers Claudio On 18 dec 2009 06:34, machiel.richards machiel.richa...@gmail.com wrote: Good Morning all QUOTE: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up I have monitored this issue for the last couple of weeks and even though the buffer pool usage is increasing slowly, it is still heading to become 100% full. It used to vary in the past where it would increase and decrease each day as required, however this does not seem to be happening anymore. Does anybody know whether there is a process that is supposed to manage this or otherwise clear this occasionally that might not be working? We are getting quite a bit of issues with the client wanting to know why this is happening, however thus far I haven't been able to find any conclusive answers, not even through google. Any assistance would be appreciated. Thanking everyone in advance. Regards Machiel -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: 01 Dece... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http:/...
RE: Innodb buffer pool size filling up
-Original Message- From: machiel.richards [mailto:machiel.richa...@gmail.com] Sent: Friday, December 18, 2009 12:33 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Good Morning all QUOTE: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up I have monitored this issue for the last couple of weeks and even though the buffer pool usage is increasing slowly, it is still heading to become 100% full. [JS] A buffer pool should become 100% full. That is part and parcel of its purpose, which is to avoid having to do a physical disk I/O operation. If a buffer pool never fills up, it is too big. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Innodb buffer pool size filling up
Good Morning all QUOTE: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up I have monitored this issue for the last couple of weeks and even though the buffer pool usage is increasing slowly, it is still heading to become 100% full. It used to vary in the past where it would increase and decrease each day as required, however this does not seem to be happening anymore. Does anybody know whether there is a process that is supposed to manage this or otherwise clear this occasionally that might not be working? We are getting quite a bit of issues with the client wanting to know why this is happening, however thus far I haven't been able to find any conclusive answers, not even through google. Any assistance would be appreciated. Thanking everyone in advance. Regards Machiel -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: 01 December 2009 10:04 PM To: 'machiel.richards'; 'Claudio Nanni' Cc: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up -Original Message- From: machiel.richards [mailto:machiel.richa...@gmail.com] Sent: Tuesday, December 01, 2009 6:17 AM To: 'Claudio Nanni' Cc: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. [JS] At the others have said, the whole purpose of a buffer pool is to hold as much frequently used data as possible. That lowers the probability of having to do physical I/O, which is much slower than memory access. The buffer pool should be full. The total database size is about 750Mb. [JS] It does surprise me that the buffer pool fills up, even though it is three times the size of your database. My guess is that whatever mechanism is used to scavenge space in the buffer pool isn't triggered until the buffer pool is full; but that is simply a guess, I really have no idea. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Regards Machiel From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: 01 December 2009 01:12 PM To: machiel.richards Cc: mysql@lists.mysql.com Subject: Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb buffer pool size filling up
That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
RE: Innodb buffer pool size filling up
The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. The total database size is about 750Mb. Regards Machiel From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: 01 December 2009 01:12 PM To: machiel.richards Cc: mysql@lists.mysql.com Subject: Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: Innodb buffer pool size filling up
The Innodb Buffer Pull usually follow a growth over time that resembles an horizontal asintot ( http://www.maecla.it/bibliotecaMatematica/go_file/MONE_BESA/grafico.gif) This to leverage all its size! So should not be a problem! Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. The total database size is about 750Mb. Regards Machiel *From:* Claudio Nanni [mailto:claudio.na...@gmail.com] *Sent:* 01 December 2009 01:12 PM *To:* machiel.richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- Claudio
RE: Innodb buffer pool size filling up
-Original Message- From: machiel.richards [mailto:machiel.richa...@gmail.com] Sent: Tuesday, December 01, 2009 6:17 AM To: 'Claudio Nanni' Cc: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. [JS] At the others have said, the whole purpose of a buffer pool is to hold as much frequently used data as possible. That lowers the probability of having to do physical I/O, which is much slower than memory access. The buffer pool should be full. The total database size is about 750Mb. [JS] It does surprise me that the buffer pool fills up, even though it is three times the size of your database. My guess is that whatever mechanism is used to scavenge space in the buffer pool isn't triggered until the buffer pool is full; but that is simply a guess, I really have no idea. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Regards Machiel From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: 01 December 2009 01:12 PM To: machiel.richards Cc: mysql@lists.mysql.com Subject: Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Innodb buffer pool size filling up
Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Innodb buffer pool size filling up
There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
innodb buffer pool size
Здравствуйте, . Hi all. I try to increaseinnodb_buffer_pool_size There is log file: mysqld_safe[23845]: started mysqld[23848]: 060615 19:14:52 InnoDB: Error: cannot allocate 2147500032 bytes of mysqld[23848]: InnoDB: memory with malloc! Total allocated memory mysqld[23848]: InnoDB: by InnoDB 58027104 bytes. Operating system errno: 12 mysqld[23848]: InnoDB: Check if you should increase the swap file or mysqld[23848]: InnoDB: ulimits of your operating system. mysqld[23848]: InnoDB: On FreeBSD check you have compiled the OS with mysqld[23848]: InnoDB: a big enough maximum process size. mysqld[23848]: InnoDB: Note that in most 32-bit computers the process mysqld[23848]: InnoDB: memory space is limited to 2 GB or 4 GB. mysqld[23848]: InnoDB: We keep retrying the allocation for 60 seconds... core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited pending signals (-i) 1024 max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 53248 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited OS Debian 3.1 About 6 Gb of memory. How i can allocate about 2-3Gb? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb buffer pool size
Look at this previous thread http://forums.mysql.com/read.php?22,42721,42721 Kishore Jalleda http://kjalleda.googlepages.com On 6/15/06, Vitaliy Okulov [EMAIL PROTECTED] wrote: Здравствуйте, . Hi all. I try to increaseinnodb_buffer_pool_size There is log file: mysqld_safe[23845]: started mysqld[23848]: 060615 19:14:52 InnoDB: Error: cannot allocate 2147500032 bytes of mysqld[23848]: InnoDB: memory with malloc! Total allocated memory mysqld[23848]: InnoDB: by InnoDB 58027104 bytes. Operating system errno: 12 mysqld[23848]: InnoDB: Check if you should increase the swap file or mysqld[23848]: InnoDB: ulimits of your operating system. mysqld[23848]: InnoDB: On FreeBSD check you have compiled the OS with mysqld[23848]: InnoDB: a big enough maximum process size. mysqld[23848]: InnoDB: Note that in most 32-bit computers the process mysqld[23848]: InnoDB: memory space is limited to 2 GB or 4 GB. mysqld[23848]: InnoDB: We keep retrying the allocation for 60 seconds... core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited pending signals (-i) 1024 max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 53248 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited OS Debian 3.1 About 6 Gb of memory. How i can allocate about 2-3Gb? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb buffer pool size question
Hi all, I set the innodb_buffer_pool_size=1024M in my.cnf show variables returns . innodb_buffer_pool_size | 1073741824 show innodb status returns -- BUFFER POOL AND MEMORY -- Total memory allocated 1169691576; in additional pool allocated 1041024 Buffer pool size 65536 Free buffers 0 Database pages 65170 Modified db pages 979 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 170494, created 940061, written 2512012 41.96 reads/s, 2.00 creates/s, 0.00 writes/s Buffer pool hit rate 843 / 1000 why is the buffer pool size different?
RE: innodb buffer pool size question
The buffer pool size value is in 16K pages (from SHOW INNODB STATUS output) -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 2:18 PM To: Mysql List (E-mail) Subject: innodb buffer pool size question Hi all, I set the innodb_buffer_pool_size=1024M in my.cnf show variables returns . innodb_buffer_pool_size | 1073741824 show innodb status returns -- BUFFER POOL AND MEMORY -- Total memory allocated 1169691576; in additional pool allocated 1041024 Buffer pool size 65536 Free buffers 0 Database pages 65170 Modified db pages 979 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 170494, created 940061, written 2512012 41.96 reads/s, 2.00 creates/s, 0.00 writes/s Buffer pool hit rate 843 / 1000 why is the buffer pool size different? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb buffer pool size question
thanks -Original Message- From: Partha Dutta [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 11:30 AM To: Sergei Skarupo; 'Mysql List (E-mail)' Subject: RE: innodb buffer pool size question The buffer pool size value is in 16K pages (from SHOW INNODB STATUS output) -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 2:18 PM To: Mysql List (E-mail) Subject: innodb buffer pool size question Hi all, I set the innodb_buffer_pool_size=1024M in my.cnf show variables returns . innodb_buffer_pool_size | 1073741824 show innodb status returns -- BUFFER POOL AND MEMORY -- Total memory allocated 1169691576; in additional pool allocated 1041024 Buffer pool size 65536 Free buffers 0 Database pages 65170 Modified db pages 979 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 170494, created 940061, written 2512012 41.96 reads/s, 2.00 creates/s, 0.00 writes/s Buffer pool hit rate 843 / 1000 why is the buffer pool size different? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb buffer pool size -- why 70%?
I read in a couple of places that on a dedicated server the innodb buffer pool size should not exceed 70% of total physical memory. Where does this number come from? Thanks in advance, Sergei
