I read a good blog called http://sqlfool.com/
The funniest thing about it for me was the artful blogger talked about having 87,000 VLFs, taking up 1.5TB, though didn't mention what a VLF was.
Anyway, I checked - VLFs are WAL files in SQLServer. In case, like me, you didn't know, VLF stands for Virtual Log File. So it's a virtual physical thing. Got it. :-\
Which means the blogged-about related to 1.5TB worth of WAL files in the equivalent of pg_xlog. Ouch!
Ah! That reminds me: on both SQLServer and DB2 the log contains undo information, which means that the transaction log can't reuse files while transactions are still active. So long running write transactions causes the log to grow in size and can eventually fill the disk. This can be a real pain for DBAs and requires workarounds. That effect doesn't occur with PostgreSQL, so pg_xlog never overflows for that reason. With PostgreSQL, undo information is effectively stored in the database itself in the form of previous row versions. So a database grows as changes occur. Long running write statements can have a similar effect in PostgreSQL, but not long running transactions. So, one more problem that us Postgres-people get to miss out on.
And just to complete the circle, I will avoid explaining what a WAL file is here.
Subscribe to:
Post Comments (Atom)
Posts
Long running transactions in Postgres will bloat the data with similar effect. I've got bad client programs that cause this all the time. (requiring kill -15 to the backend to stop the 'idle in transaction' situation from killing performance too badly).
ReplyDeleteThe benefit is that the log doesn't expand. The drawback is when that happens I have to CLUSTER to get the performance of the system back and shrink the bloated tables and indexes.
I'm not sure if this is an advantage or not for Postgres, its just different.
In previous releases of PostgreSQL *any* long running transaction definitely had the effect you describe. From 8.4 onwards we reset the xmin value after each snapshot, so that long running read-only transactions no longer cause this issue in the common case.
ReplyDelete