|
Home > Archive > PostgreSQL Bugs > January 2006 > Re: Huge number of disk writes after migration to 8.1
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Huge number of disk writes after migration to 8.1
|
|
| Tom Lane 2006-01-17, 8:24 pm |
| Marcin <migor@op.pl> writes:
> Attached you'll find the archive with data you've asked.
Well, breaking down the contents of the stats file I find:
190 backends (218880 bytes)
118 databases (8496 bytes)
81952 tables (8523008 bytes)
And the ps listing shows there actually are about 190 live backends,
so the idea about leaked backend stats entries is clearly wrong.
The per-table data is 104 bytes/table, up from 80 in 8.0, so that's
a noticeable increase but hardly a killer. What I have to conclude
is that 8.1 is tracking stats for a lot more tables than 8.0 was.
(Do you happen to have a number for the size of the stats file in
the 8.0 installation? I don't need to see its contents, I just want
to know how big it is.)
Do you actually have 81952 tables in the installation? Maybe we are
looking at a table-level leak. Do you have autovacuum on where it was
not in 8.0? Maybe that's causing it.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Tom Lane 2006-01-17, 8:24 pm |
| Marcin <migor@op.pl> writes:
> Autovacuum is off.
> The VACUUM FULL bottom line is:
> INFO: free space map contains 59842 pages in 25371 relations
> DETAIL: A total of 426720 page slots are in use (including overhead).
> 426720 page slots are required to track all free space.
> And the quick iterations for all the DBs with psql -t -c "\dt" shows
> 67654 rows.
> So there's not 81952, BUT, the tables are created (and dropped) quite
> often during the work hours (and they're regular, not TEMPORARY tables).
> I also find out, that there were 11170 tables created (and most of them
> dropped) today.
Looking at the code, stats entries for dropped tables are cleaned out
only when a VACUUM command is done; which is something we probably ought
to change. However, that's always been the case, so I don't understand
why your stats file is so much bigger in 8.1. Have you changed your
vacuuming strategy at all since the 8.0 installation? Perhaps row or
block statistics weren't enabled in the 8.0 installation and are now?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Alvaro Herrera 2006-01-17, 8:24 pm |
| Tom Lane wrote:
> Marcin <migor@op.pl> writes:
>
> Looking at the code, stats entries for dropped tables are cleaned out
> only when a VACUUM command is done; which is something we probably ought
> to change.
I was going to ask if you were confusing pgstat_vacuum_tabsta
t with a
VACUUM command, when I noticed that only in vacuum() is that function
called! This surprised me and I agree that it should be changed. I'm
not sure what would be a more appropiate place to call it, however.
Maybe in heap_drop_with_catal
og() we could place a call, or a limited
version that'd only "vacuum" the table being dropped.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tom Lane 2006-01-18, 3:24 am |
| Alvaro Herrera < alvherre@commandprom
pt.com> writes:
> Tom Lane wrote:
[color=darkred]
> I was going to ask if you were confusing pgstat_vacuum_tabsta
t with a
> VACUUM command, when I noticed that only in vacuum() is that function
> called! This surprised me and I agree that it should be changed. I'm
> not sure what would be a more appropiate place to call it, however.
I don't have a problem with that. What I'm thinking is that a DROP
TABLE command should issue a tabpurge message for the specific table
(or index) being zapped. We still need vacuum_tabstat as a backstop
in case the tabpurge message gets lost, though.
Another thought is that in autovacuum, pgstat_vacuum_tabsta
t is really
called too often: once per autovac cycle would be sufficient, but
instead it's repeated for each table we vacuum.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Marcin 2006-01-18, 3:24 am |
| On Tue, Jan 17, 2006 at 05:12:09PM -0500, Tom Lane wrote:
> However, that's always been the case, so I don't understand
> why your stats file is so much bigger in 8.1. Have you changed your
> vacuuming strategy at all since the 8.0 installation? Perhaps row or
> block statistics weren't enabled in the 8.0 installation and are now?
The only change was the upgrade to 8.1. I run VACUUM FULL everyday, and
double checked that it finished succesfully in last two days.
The block and row statistics are disabled:
stats_start_collecto
r = on
stats_command_string
= off
stats_block_level = off
stats_row_level = off
stats_reset_on_serve
r_start = off
I have no idea, why the pgstat.stat is so large. I just shutdown the
idle backend connection, so there're only 30 left (instead of 200),
and the pgstat size decreases by 200KB from 8.8 to 8.6 MB. The lowest size
of pgstat in last six hours was 8.5 MB.
--
Marcin
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Tom Lane 2006-01-18, 9:24 am |
| Marcin <migor@op.pl> writes:
> On Tue, Jan 17, 2006 at 05:12:09PM -0500, Tom Lane wrote:
[color=darkred]
> The only change was the upgrade to 8.1. I run VACUUM FULL everyday, and
> double checked that it finished succesfully in last two days.
> The block and row statistics are disabled:
> stats_start_collecto
r = on
> stats_command_string
= off
> stats_block_level = off
> stats_row_level = off
> stats_reset_on_serve
r_start = off
Stats off and it's still bloating the file??
[ studies code... ] I see the culprit: it's the pgstat_report_vacuum
and pgstat_report_analyz
e routines that were added in 8.1. Those send
messages unconditionally, meaning that the collector will create table
entries for every table during a database-wide vacuum, even with stats
turned off.
This seems like a bad idea. Given the nature of what's counted, I think
that treating these messages as "row level" stats would be appropriate.
Alvaro, what do you think?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Alvaro Herrera 2006-01-18, 11:24 am |
| Tom Lane wrote:
> Stats off and it's still bloating the file??
>
> [ studies code... ] I see the culprit: it's the pgstat_report_vacuum
> and pgstat_report_analyz
e routines that were added in 8.1. Those send
> messages unconditionally, meaning that the collector will create table
> entries for every table during a database-wide vacuum, even with stats
> turned off.
>
> This seems like a bad idea.
Sorry, clearly my bug :-(
> Given the nature of what's counted, I think that treating these
> messages as "row level" stats would be appropriate. Alvaro, what do
> you think?
Yeah, row level seems appropiate for what we use it. I'll take care of
it, unless you want to do it.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2006-01-18, 11:24 am |
| Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Yeah, row level seems appropiate for what we use it. I'll take care of
> it, unless you want to do it.
I'll fix it --- I want to put in an immediate tabpurge on drop, too.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2006-01-18, 1:24 pm |
| Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Tom Lane wrote:
[color=darkred]
> Yeah, row level seems appropiate for what we use it. I'll take care of
> it, unless you want to do it.
Actually, there's another problem here: if we do have row-level stats
turned on, a manual "VACUUM" command will still cause the set of tables
listed in the stats file to grow to include every table in the DB,
whether or not anything interesting is happening to that table. I think
this is probably undesirable. I'm tempted to change pgstat_recv_vacuum
and pgstat_recv_analyze so that they will not create new hash entries,
but only insert the row count if the hash entry already exists. I am a
bit worried that I might be missing something about possible
interactions with autovacuum though. I see that autovac skips vacuuming
tables that have no hash entry ... is there some circular reasoning
going on there?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Alvaro Herrera 2006-01-18, 1:24 pm |
| Tom Lane wrote:
> Actually, there's another problem here: if we do have row-level stats
> turned on, a manual "VACUUM" command will still cause the set of tables
> listed in the stats file to grow to include every table in the DB,
> whether or not anything interesting is happening to that table. I think
> this is probably undesirable. I'm tempted to change pgstat_recv_vacuum
> and pgstat_recv_analyze so that they will not create new hash entries,
> but only insert the row count if the hash entry already exists. I am a
> bit worried that I might be missing something about possible
> interactions with autovacuum though. I see that autovac skips vacuuming
> tables that have no hash entry ... is there some circular reasoning
> going on there?
The idea was that autovac should skip tables for which it doesn't have
info, because it can't decide and we chose to err on the side of
caution. However, after a vacuum or analyze we do have info about the
table, which is what we store in the pgstat_recv functions
inconditionally. Thus the next autovacuum is able to make an informed
decision about this table.
The principles are: 1) store as much information as possible,
2) autovacuum should act iff it has information.
I don't think this is a bug, but I'm not dead set on it if you want to
change this behavior.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"The first of April is the day we remember what we are
the other 364 days of the year" (Mark Twain)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Tom Lane 2006-01-18, 1:24 pm |
| Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Tom Lane wrote:
[color=darkred]
> The idea was that autovac should skip tables for which it doesn't have
> info, because it can't decide and we chose to err on the side of
> caution. However, after a vacuum or analyze we do have info about the
> table, which is what we store in the pgstat_recv functions
> inconditionally. Thus the next autovacuum is able to make an informed
> decision about this table.
However, if it skips the table for lack of info, then there still won't
be any info next time.
More to the point, that's not why we skip entry-less tables. We skip
entry-less tables because there is no need for autovac to do anything to
a table that's not being modified, and if it *is* being modified then
those operations will result in creation of a table entry. So I'm still
not seeing why vacuum or analyze should force creation of an entry.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Alvaro Herrera 2006-01-18, 1:24 pm |
| Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>
>
> However, if it skips the table for lack of info, then there still won't
> be any info next time.
Of course, because there's nowhere to take it from.
> More to the point, that's not why we skip entry-less tables. We skip
> entry-less tables because there is no need for autovac to do anything to
> a table that's not being modified, and if it *is* being modified then
> those operations will result in creation of a table entry. So I'm still
> not seeing why vacuum or analyze should force creation of an entry.
Well, if you issue a manual vacuum then you can argue that it has been
"modified" (it's seeing some activity). But immediately after a vacuum
the table will not be vacuumed by autovac if there's no other activity,
because there's no need for it, so we can create the entry anyway and it
won't make an immediate difference. However, it (to have the pgstat
table entry) _will_ make a difference as soon as that entry is modified
by other activity, because the number of tuples pgstat knows about will
be more accurate.
So maybe we could supress the entry creation on analyze (because analyze
_can_ force a vacuum during the next autovac iteration, which is
something we may want to avoid), but keep it on vacuum.
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 17.7", W 73º 14' 26.8"
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tom Lane 2006-01-18, 1:24 pm |
| Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Well, if you issue a manual vacuum then you can argue that it has been
> "modified" (it's seeing some activity). But immediately after a vacuum
> the table will not be vacuumed by autovac if there's no other activity,
> because there's no need for it, so we can create the entry anyway and it
> won't make an immediate difference.
You're ignoring the point of this thread, which is that creating
hashtable entries for tables that aren't actively being modified
causes significant ongoing overhead that we ought to try to minimize.
What I'm suggesting is that only foreground modification activity
(insert/update/delete) should trigger creation of a hashtable entry.
Once that has happened, it's reasonable to update the entry after
any vacuum or analyze. But a vacuum or analyze shouldn't force an
entry to spring into existence when nothing else is happening to
the table. That just bloats the stats file.
Given the current autovac logic, ordinary autovac operations won't cause
useless entries to be created anyway ... but an anti-wraparound vacuum
will, and so will manual vacuum or analyze commands.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Alvaro Herrera 2006-01-18, 1:24 pm |
| Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>
> You're ignoring the point of this thread, which is that creating
> hashtable entries for tables that aren't actively being modified
> causes significant ongoing overhead that we ought to try to minimize.
True. I agree with the rest of the reasoning in this case.
Maybe the fact that the stat file is completely rewritten every 500 ms
should be reconsidered, if in the future someone chooses to rewrite
the stat system. We can reconsider this part then, as well.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org
Jason Tesser: You might not have understood me or I am not understanding you.
Paul Thomas: It feels like we're 2 people divided by a common language...
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Marcin 2006-01-18, 8:25 pm |
| Tom Lane wrote:
> [ studies code... ] I see the culprit: it's the pgstat_report_vacuum
> and pgstat_report_analyz
e routines that were added in 8.1. Those send
> messages unconditionally, meaning that the collector will create table
> entries for every table during a database-wide vacuum, even with stats
> turned off.
So in my case disabling the daily vacuum would, in fact, improve the
write performance ;) ... just joking.
I think I turn off the stats completely, and wait patiently for a fix.
Do you think it will be included in one of next 8.1.x releases, or do I
have to wait for 8.2?
Thanks again for a great help,
--
Marcin
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2006-01-18, 8:25 pm |
| Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Maybe the fact that the stat file is completely rewritten every 500 ms
> should be reconsidered, if in the future someone chooses to rewrite
> the stat system. We can reconsider this part then, as well.
Yeah, it's becoming pretty obvious that that design does not scale very
well. I don't immediately have any ideas about a better way though.
I am working on some marginal hacks like not writing more of the backend
activity strings than is needed, but it'd be nicer to think of a
different solution.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Tom Lane 2006-01-18, 8:25 pm |
| Marcin <migor@op.pl> writes:
> Do you think it will be included in one of next 8.1.x releases, or do I
> have to wait for 8.2?
I'm working on a fix for 8.1.3.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|
|
|
|
|