Thread: Memory usage of auto-vacuum
Hello, We are running a PostgreSQL 8.4 database, with two tables containing a lot (> 1 million) moderatly small rows. It contains some btree indexes, and one of the two tables contains a gin full-text index. We noticed that the autovacuum process tend to use a lot of memory, bumping the postgres process near 1Gb while it's running. I looked in the documentations, but I didn't find the information : do you know how to estimate the memory required for the autovacuum if we increase the number of rows ? Is it linear ? Logarithmic ? Also, is there a way to reduce that memory usage ? Would running the autovacuum more frequently lower its memory usage ? Regards, -- Gaël Le Mignot - gael@pilotsystems.net Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com
On 9/07/2011 3:25 PM, Gael Le Mignot wrote: > > Hello, > > We are running a PostgreSQL 8.4 database, with two tables containing a > lot (> 1 million) moderatly small rows. It contains some btree indexes, > and one of the two tables contains a gin full-text index. > > We noticed that the autovacuum process tend to use a lot of memory, > bumping the postgres process near 1Gb while it's running. What is maintenance_work_mem set to in postgresql.conf? -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
Hi, On Sat, 2011-07-09 at 09:25 +0200, Gael Le Mignot wrote: > [...] > We are running a PostgreSQL 8.4 database, with two tables containing a > lot (> 1 million) moderatly small rows. It contains some btree indexes, > and one of the two tables contains a gin full-text index. > > We noticed that the autovacuum process tend to use a lot of memory, > bumping the postgres process near 1Gb while it's running. > Well, it could be its own memory (see maintenance_work_mem), or shared memory. So, it's hard to say if it's really an issue or not. BTW, how much memory do you have on this server? what values are used for shared_buffers and maintenance_work_mem? > I looked in the documentations, but I didn't find the information : do > you know how to estimate the memory required for the autovacuum if we > increase the number of rows ? Is it linear ? Logarithmic ? > It should use up to maintenance_work_mem. Depends on how much memory you set on this parameter. > Also, is there a way to reduce that memory usage ? Reduce maintenance_work_mem. Of course, if you do that, VACUUM could take a lot longer to execute. > Would running the > autovacuum more frequently lower its memory usage ? > Yes. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Hello Craig! Sat, 09 Jul 2011 16:31:47 +0800, you wrote: > On 9/07/2011 3:25 PM, Gael Le Mignot wrote: >> >> Hello, >> >> We are running a PostgreSQL 8.4 database, with two tables containing a >> lot (> 1 million) moderatly small rows. It contains some btree indexes, >> and one of the two tables contains a gin full-text index. >> >> We noticed that the autovacuum process tend to use a lot of memory, >> bumping the postgres process near 1Gb while it's running. > What is maintenance_work_mem set to in postgresql.conf? It's the debian default, which is 16Mb. Do you think we should reduce it ? I also forgot to add something which may be important : there are a lot of INSERT (and SELECT) in those tables, but very few UPDATE/DELETE. Regards, -- Gaël Le Mignot - gael@pilotsystems.net Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com
Hello Guillaume! Sat, 09 Jul 2011 10:33:03 +0200, you wrote: > Hi, > On Sat, 2011-07-09 at 09:25 +0200, Gael Le Mignot wrote: >> [...] >> We are running a PostgreSQL 8.4 database, with two tables containing a >> lot (> 1 million) moderatly small rows. It contains some btree indexes, >> and one of the two tables contains a gin full-text index. >> >> We noticed that the autovacuum process tend to use a lot of memory, >> bumping the postgres process near 1Gb while it's running. >> > Well, it could be its own memory (see maintenance_work_mem), or shared > memory. So, it's hard to say if it's really an issue or not. > BTW, how much memory do you have on this server? what values are used > for shared_buffers and maintenance_work_mem? maintenance_work_mem is at 16Mb, shared_buffers at 24Mb. The server currently has 2Gb, we'll add more to it (it's a VM), but we would like to be able to make an estimate on how much memory it'll need for a given rate of INSERT into the table, so we can estimate future costs. >> I looked in the documentations, but I didn't find the information : do >> you know how to estimate the memory required for the autovacuum if we >> increase the number of rows ? Is it linear ? Logarithmic ? >> > It should use up to maintenance_work_mem. Depends on how much memory you > set on this parameter. So, it shouldn't depend on data size ? Is there a fixed multiplicative factor between maintenance_work_mem and the memory actually used ? >> Also, is there a way to reduce that memory usage ? > Reduce maintenance_work_mem. Of course, if you do that, VACUUM could > take a lot longer to execute. >> Would running the autovacuum more frequently lower its memory usage ? >> > Yes. Thanks, we'll try that. Regards, -- Gaël Le Mignot - gael@pilotsystems.net Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com
On Sat, 2011-07-09 at 10:43 +0200, Gael Le Mignot wrote: > Hello Guillaume! > > Sat, 09 Jul 2011 10:33:03 +0200, you wrote: > > > Hi, > > On Sat, 2011-07-09 at 09:25 +0200, Gael Le Mignot wrote: > >> [...] > >> We are running a PostgreSQL 8.4 database, with two tables containing a > >> lot (> 1 million) moderatly small rows. It contains some btree indexes, > >> and one of the two tables contains a gin full-text index. > >> > >> We noticed that the autovacuum process tend to use a lot of memory, > >> bumping the postgres process near 1Gb while it's running. > >> > > > Well, it could be its own memory (see maintenance_work_mem), or shared > > memory. So, it's hard to say if it's really an issue or not. > > > BTW, how much memory do you have on this server? what values are used > > for shared_buffers and maintenance_work_mem? > > maintenance_work_mem is at 16Mb, shared_buffers at 24Mb. > IOW, default values. > The server currently has 2Gb, we'll add more to it (it's a VM), but we > would like to be able to make an estimate on how much memory it'll need > for a given rate of INSERT into the table, so we can estimate future > costs. > > >> I looked in the documentations, but I didn't find the information : do > >> you know how to estimate the memory required for the autovacuum if we > >> increase the number of rows ? Is it linear ? Logarithmic ? > >> > > > It should use up to maintenance_work_mem. Depends on how much memory you > > set on this parameter. > > So, it shouldn't depend on data size ? Nope, it shouldn't. > Is there a fixed multiplicative > factor between maintenance_work_mem and the memory actually used ? > 1 :) > >> Also, is there a way to reduce that memory usage ? > > > Reduce maintenance_work_mem. Of course, if you do that, VACUUM could > > take a lot longer to execute. > > >> Would running the autovacuum more frequently lower its memory usage ? > >> > > > Yes. > > Thanks, we'll try that. > I don't quite understand how you can get up to 1GB used by your process. According to your configuration, and unless I'm wrong, it shouldn't take more than 40MB. Perhaps a bit more, but not 1GB. So, how did you find this number? -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Hello Guillaume! Sat, 09 Jul 2011 10:53:14 +0200, you wrote: > I don't quite understand how you can get up to 1GB used by your process. > According to your configuration, and unless I'm wrong, it shouldn't take > more than 40MB. Perhaps a bit more, but not 1GB. So, how did you find > this number? Looking at "top" we saw the postgres process growing and growing and then shrinking back, and doing a "select * from pg_stat_activity;" in parallel of the growing we found only the "vacuum analyze" query running. But maybe we drawn the conclusion too quickly, I'll try disabling the auto vacuum to see if we really get rid of the problem doing it. Thanks for your answers. Regards, -- Gaël Le Mignot - gael@pilotsystems.net Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com
On Sat, 2011-07-09 at 11:00 +0200, Gael Le Mignot wrote: > Hello Guillaume! > > Sat, 09 Jul 2011 10:53:14 +0200, you wrote: > > > I don't quite understand how you can get up to 1GB used by your process. > > According to your configuration, and unless I'm wrong, it shouldn't take > > more than 40MB. Perhaps a bit more, but not 1GB. So, how did you find > > this number? > > Looking at "top" we saw the postgres process growing and growing and > then shrinking back, and doing a "select * from pg_stat_activity;" in > parallel of the growing we found only the "vacuum analyze" query running. > There is not only one postgres process. So you first need to be sure that it's the one that executes the autovacuum. > But maybe we drawn the conclusion too quickly, I'll try disabling the > auto vacuum to see if we really get rid of the problem doing it. > Disabling the autovacuum is usually a bad idea. You'll have to execute VACUUM/ANALYZE via cron, which could get hard to configure. BTW, what's your PostgreSQL release? I assume at least 8.3 since you're using FTS? -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Hello Guillaume! Sat, 09 Jul 2011 11:06:16 +0200, you wrote: > On Sat, 2011-07-09 at 11:00 +0200, Gael Le Mignot wrote: >> Hello Guillaume! >> >> Sat, 09 Jul 2011 10:53:14 +0200, you wrote: >> >> > I don't quite understand how you can get up to 1GB used by your process. >> > According to your configuration, and unless I'm wrong, it shouldn't take >> > more than 40MB. Perhaps a bit more, but not 1GB. So, how did you find >> > this number? >> >> Looking at "top" we saw the postgres process growing and growing and >> then shrinking back, and doing a "select * from pg_stat_activity;" in >> parallel of the growing we found only the "vacuum analyze" query running. >> > There is not only one postgres process. So you first need to be sure > that it's the one that executes the autovacuum. Shouldn't "pg_stat_activity" contain the current jobs of all the processes ? >> But maybe we drawn the conclusion too quickly, I'll try disabling the >> auto vacuum to see if we really get rid of the problem doing it. >> > Disabling the autovacuum is usually a bad idea. You'll have to execute > VACUUM/ANALYZE via cron, which could get hard to configure. Oh, yes, sure, I meant as a test to know if it's the vacuum or not, not to definitely disable it. > BTW, what's your PostgreSQL release? I assume at least 8.3 since you're > using FTS? It's 8.4 from Debian Squeeze. -- Gaël Le Mignot - gael@pilotsystems.net Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com
On 9/07/2011 4:43 PM, Gael Le Mignot wrote: > maintenance_work_mem is at 16Mb, shared_buffers at 24Mb. Woah, what? And you're hitting a gigabyte for autovacuum? Yikes. That just doesn't sound right. Are you using any contrib modules? If so, which ones? Are you able to post your DDL? How big is the database? (Not that it should matter). -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
Gael Le Mignot <gael@pilotsystems.net> writes: > Sat, 09 Jul 2011 11:06:16 +0200, you wrote: >>> BTW, what's your PostgreSQL release? I assume at least 8.3 since you're >>> using FTS? > It's 8.4 from Debian Squeeze. 8.4.what? In particular I'm wondering if you need this 8.4.6 fix: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f0e4331d04fa007830666c5baa2c3e37cce9c3ff regards, tom lane
Hello Tom! Sat, 09 Jul 2011 12:23:18 -0400, you wrote: > Gael Le Mignot <gael@pilotsystems.net> writes: >> Sat, 09 Jul 2011 11:06:16 +0200, you wrote: >>>> BTW, what's your PostgreSQL release? I assume at least 8.3 since you're >>>> using FTS? >> It's 8.4 from Debian Squeeze. > 8.4.what? It's 8.4.8-0squeeze1 > In particular I'm wondering if you need this 8.4.6 fix: > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f0e4331d04fa007830666c5baa2c3e37cce9c3ff Thanks for the tip, it very well could have been that, but it's 8.4.8, I checked the concerned source file and the patch is there, and I didn't find any Debian-specific patch that could collide with it. Regards, -- Gaël Le Mignot - gael@pilotsystems.net Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com
Hello, Here is an update on my problem : - the problem was caused by "VACUUM ANALYZE", but by a plain "VACUUM" ; - it was exactly the same with manual and automatic "VACUUM ANALYZE" ; - it was caused by a GIN index on a tsvector, using a very high (10000) statistics target. Setting back the statistics to 1000 reduced the amount of RAM used to a very reasonable amount. The value of 10000 is indeed not very realistic, but I think that would deserve some mention on the documentation, if possible with an estimate of the maximal memory usage for a given statistics target and table size. Do you think it's a good idea, and if so, if that estimate can be reasonably made ? Regards, -- Gaël Le Mignot - gael@pilotsystems.net Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com