Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
Yea sorry good point.. It's probably at least safe to say the process should not just hang though, and there should be more info in the log as to what it's doing.. John R Pierce wrote: Mike Christensen wrote: First off, when Postgres starts and sees that your database was not closed properly, it should tell you there's pending transactions and ask if you want to dump them or try to process them (or maybe save them for later). If you process them, there should be clear status and you should know what's going on. how does a system service ask you anything? a service normally starts when the system boots, before anyone logs onto the console. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
On Thu, Feb 19, 2009 at 02:58:02AM -0800, Mike Christensen wrote: > I would assume if the account the service is running under has limited > disk space, it won't really matter what OS you're running under. > Postgres will throw an "out of disk space" error. Similarly to Scott, every time I've come across this PG has recovered quite happily on its own. > The problem for me is I was in the middle of a transaction which > inserted about 50,000 rows into a table, and that transaction was > pending in the transaction log. For some reason, the service didn't > quit property and when it started back up, it began a repair process. > When it got to the pending transactions (I think about 16 megs worth of > data) it just hung with no useful errors or anything outputted to the > log or stderr. I think this needs to be fixed! By default PG will always create 16MB WAL files, if you're modifying a lot of data then multiple log files will be involved. I've done silly things like inserting the cross product of two large tables and wondered why things are taking so long it fails after a while with an out of disk space error, but PG never misbehaved when I did this. This would be inserting several tens of GB of data, i.e. 100's of millions of rows. > First off, when Postgres starts and sees that your database was not > closed properly, it should tell you there's pending transactions and ask > if you want to dump them or try to process them (or maybe save them for > later). Doing much of that would break things quite impressively. Applications are, or should be, designed to assume that once a transaction has commited then it's comitted. If the database can decide to rollback a commited transaction because you run out of space doing something else this is bad. Similarly, a transaction can't commit half way through, which is almost what you're saying you'd like to happen. This would lead to all sorts of inconsistency. > If you process them, there should be clear status and you > should know what's going on. It's very possible the service would have > /eventually/ started up for me had I waited long enough (I tried 5-6 > hrs, with no logs, output, or change in memory consumption; thus I > assumed it was dead).. PG, at least under Linux, is very verbose about things when they look suspicious. They must be appearing somewhere under Windows as well, maybe others can suggest where to look. > Also, if there are errors processing the transaction log, rather than > just error out and exit, it should at least tell you to run > pg_resetxlog.exe. Another idea is if I just delete everything in the > pg_xlog directory, it should recover and boot up without any errors > rather than complaining about missing checkpoint files. And potentially corrupt the entire database? PG's behavior is designed to be safely pessimistic and to keep your data safe. E.g. if a disk is on the way out and starts corrupting the log then you want to contain the error rather than spreading it further. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
Mike Christensen wrote: First off, when Postgres starts and sees that your database was not closed properly, it should tell you there's pending transactions and ask if you want to dump them or try to process them (or maybe save them for later). If you process them, there should be clear status and you should know what's going on. how does a system service ask you anything? a service normally starts when the system boots, before anyone logs onto the console. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
Mike Christensen wrote: I have well over 50 gigs free on that drive.. I doubt it. out of inodes. Scott Marlowe wrote: On Wed, Feb 18, 2009 at 1:39 PM, Mike Christensen wrote: Hi all - ERROR: could not extend relation 1663/41130/41177: No space left on device HINT: Check free disk space. You're running out of disk space. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
I would assume if the account the service is running under has limited disk space, it won't really matter what OS you're running under. Postgres will throw an "out of disk space" error. The problem for me is I was in the middle of a transaction which inserted about 50,000 rows into a table, and that transaction was pending in the transaction log. For some reason, the service didn't quit property and when it started back up, it began a repair process. When it got to the pending transactions (I think about 16 megs worth of data) it just hung with no useful errors or anything outputted to the log or stderr. I think this needs to be fixed! First off, when Postgres starts and sees that your database was not closed properly, it should tell you there's pending transactions and ask if you want to dump them or try to process them (or maybe save them for later). If you process them, there should be clear status and you should know what's going on. It's very possible the service would have /eventually/ started up for me had I waited long enough (I tried 5-6 hrs, with no logs, output, or change in memory consumption; thus I assumed it was dead).. Also, if there are errors processing the transaction log, rather than just error out and exit, it should at least tell you to run pg_resetxlog.exe. Another idea is if I just delete everything in the pg_xlog directory, it should recover and boot up without any errors rather than complaining about missing checkpoint files. No big problem, the good news is Postgres never once actually corrupted my data and I didn't lose a single byte (well, except for the pending transactions that I didn't care about). More good news is I learned a lot and even got familiar with some of the source code and debugging, and it's made me finally get around to writing an automatic backup script that runs every midnight. Mike Scott Marlowe wrote: On Thu, Feb 19, 2009 at 3:11 AM, Mike Christensen wrote: Actually I'm writing emails on my Mac However, the Postgres service is running on my Windows 2003 machine.. The disk space issue turned out to be a disk quota which was easy to solve. Unfortunately, the fact it crashed Postgres and with a massive transaction log left the server in a state where it wouldn't boot anymore. I was eventually able to fix it by resetting the transaction log manually. I'm hoping future versions of Postgres will handle this scenario a lot better.. They're certainly supposed to. I've had no such problems running out of space on linux in the past. I wonder if it's a windows thing.
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
On Thu, Feb 19, 2009 at 3:11 AM, Mike Christensen wrote: > Actually I'm writing emails on my Mac > > However, the Postgres service is running on my Windows 2003 machine.. > > The disk space issue turned out to be a disk quota which was easy to solve. > Unfortunately, the fact it crashed Postgres and with a massive transaction > log left the server in a state where it wouldn't boot anymore. I was > eventually able to fix it by resetting the transaction log manually. I'm > hoping future versions of Postgres will handle this scenario a lot better.. They're certainly supposed to. I've had no such problems running out of space on linux in the past. I wonder if it's a windows thing. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
Actually I'm writing emails on my Mac However, the Postgres service is running on my Windows 2003 machine.. The disk space issue turned out to be a disk quota which was easy to solve. Unfortunately, the fact it crashed Postgres and with a massive transaction log left the server in a state where it wouldn't boot anymore. I was eventually able to fix it by resetting the transaction log manually. I'm hoping future versions of Postgres will handle this scenario a lot better.. Mike Grzegorz Jaśkiewicz wrote: I bet it is on windows (judging by html in that email), but if isn't: open a console and issue: watch -n 0.5 df -h and run that insert again ;) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
I bet it is on windows (judging by html in that email), but if isn't: open a console and issue: watch -n 0.5 df -h and run that insert again ;) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
I just changed the account manually and did not set a disk quota.. However, now I have bigger problems since the service will not start up anymore. I tried re-booting twice. First I get: 2009-02-18 21:24:25 GMT FATAL: lock file "postmaster.pid" already exists 2009-02-18 21:24:25 GMT HINT: Is another postmaster (PID 1888) running in data directory "C:/Program Files/PostgreSQL/8.3/data"? After I delete the file, I try to start the service again and get: 2009-02-18 13:27:18 PST FATAL: could not create any TCP/IP sockets Any ideas? John R Pierce wrote: Mike Christensen wrote: Ooo good call, the account is part of the "Users" group which has a quota: The users will have the following disk quota: Disk space limited to 1024 MB Warning sent at 900 MB Which is the exact size of the database.. However, anyone have a clue on how to change this? This is Windows Server 2003 SBS, I'm not an expert I'm not particularly familiar with SBS, but in general, the postgres service account is not normally a part of -any- group on Windows. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
Mike Christensen wrote: Ooo good call, the account is part of the "Users" group which has a quota: The users will have the following disk quota: Disk space limited to 1024 MB Warning sent at 900 MB Which is the exact size of the database.. However, anyone have a clue on how to change this? This is Windows Server 2003 SBS, I'm not an expert I'm not particularly familiar with SBS, but in general, the postgres service account is not normally a part of -any- group on Windows. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
Ooo good call, the account is part of the "Users" group which has a quota: The users will have the following disk quota: Disk space limited to 1024 MB Warning sent at 900 MB Which is the exact size of the database.. However, anyone have a clue on how to change this? This is Windows Server 2003 SBS, I'm not an expert Bill Moran wrote: In response to Mike Christensen : I have well over 50 gigs free on that drive.. I doubt it. I'm not aware of that error having false-positives associated with it. Common confusion on this point could result from having quotas enabled, or possibly you're running out of space, then when you check free space (after the error has occurred) the space has already been reclaimed from the failed transaction and therefore it looks like you have plenty of free space, but it disappears when you're running the process. Also, 2x check that Postgres' data files are on the same partition as where you've got the 50G free (I've made that mistake more than once) If none of those help, provide more details. Based on the detail level you've provided, you've run out of disk space.
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
On Wed, Feb 18, 2009 at 12:39:50PM -0800, Mike Christensen wrote: > I'm doing some perf testing and need huge amounts of data. So I have a > program that is adding data to a few tables ranging from 500,000 to 15M > rows. I assume you're repeatedly inserting data and then deleting it? If so, PG won't get much of a chance to clean up after you. Because of the way it handles transactions all of the old data will be left in the table until the table is vacuumed and the appropriate tuples/rows are marked as deleted. > The program is just a simply C# program that blasts data into the > DB, Just out of interest, do you know about the COPY command? things will go much faster than a large number of INSERT statements. > but after about 3M rows or so I get an errror: > > ERROR: could not extend relation 1663/41130/41177: No space left on device > HINT: Check free disk space. > > If I do a full VACUUM on the table being inserted into, the error goes > away but it comes back very quickly. Obviously, I wouldn't want this > happening in a production environment. VACUUM FULL's should very rarely be done, routine maintenance would be to do plain VACUUMs or let the auto-vacuum daemon handle things. This will mark the space as available and subsequent operations will reuse the space. > What's the recommended setup in a production environment for tables > where tons of data will be inserted? If you're repeatedly inserting and deleting data then you'll probably want to intersperse some VACUUMs in there. > It seems to me there's some sort of "max table size" before you have to > allocate more space on the disk, however I can't seem to find where > these settings are and how to allow millions of rows to be inserted into > a table without having to vacuum every few million rows.. There's no maximum table size you get control over; 15million rows on its own isn't considered particularly big but you need to start being careful at that stage. If you've got a particularly "wide" table (i.e. lots of attributes/columns) this is obviously going to take more space and you may consider normalizing the data out into separate tables. Once your row count gets to 10 or 100 times what your dealing with you'd probably need to start thinking about partitioning the tables and how to do that would depend on your usage patterns. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
In response to Mike Christensen : > I have well over 50 gigs free on that drive.. I doubt it. I'm not aware of that error having false-positives associated with it. Common confusion on this point could result from having quotas enabled, or possibly you're running out of space, then when you check free space (after the error has occurred) the space has already been reclaimed from the failed transaction and therefore it looks like you have plenty of free space, but it disappears when you're running the process. Also, 2x check that Postgres' data files are on the same partition as where you've got the 50G free (I've made that mistake more than once) If none of those help, provide more details. Based on the detail level you've provided, you've run out of disk space. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
On Wed, Feb 18, 2009 at 1:55 PM, Mike Christensen wrote: > I have well over 50 gigs free on that drive.. I doubt it. Quotas? Something's making the OS think the drive is full. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
On Wed, 2009-02-18 at 12:55 -0800, Mike Christensen wrote: > I have well over 50 gigs free on that drive.. I doubt it. Are you sure the pg data directory is on the drive you think it is? Are you doing alot of deletes or are you merely inserting? Are you doing any sorting and therefore running out of temp space in your tmp partition [supposition, I've run into something like this before, but not specifically with Postgres]. -Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
On Wednesday 18 February 2009, Mike Christensen wrote: > > ERROR: could not extend relation 1663/41130/41177: No space left on > device HINT: Check free disk space. > It seems to me there's some sort of "max table size" before you have to > allocate more space on the disk, however I can't seem to find where > these settings are and how to allow millions of rows to be inserted into > a table without having to vacuum every few million rows.. The error indicates that your file system is full. It's not a PostgreSQL problem. Hence the "hint". -- A hybrid Escalade is missing the point much in the same way that having a diet soda with your extra large pepperoni pizza is missing the point. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
Mike Christensen writes: > I'm doing some perf testing and need huge amounts of data. So I have a > program that is adding data to a few tables ranging from 500,000 to 15M > rows. The program is just a simply C# program that blasts data into the > DB, but after about 3M rows or so I get an errror: > ERROR: could not extend relation 1663/41130/41177: No space left on device > HINT: Check free disk space. Not to put too fine a point on it, but maybe you need to buy a bigger disk. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
I have well over 50 gigs free on that drive.. I doubt it. Scott Marlowe wrote: On Wed, Feb 18, 2009 at 1:39 PM, Mike Christensen wrote: Hi all - ERROR: could not extend relation 1663/41130/41177: No space left on device HINT: Check free disk space. You're running out of disk space.
Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors
On Wed, Feb 18, 2009 at 1:39 PM, Mike Christensen wrote: > Hi all - > ERROR: could not extend relation 1663/41130/41177: No space left on device > HINT: Check free disk space. You're running out of disk space. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] When adding millions of rows at once, getting out of disk space errors
Hi all - I'm doing some perf testing and need huge amounts of data. So I have a program that is adding data to a few tables ranging from 500,000 to 15M rows. The program is just a simply C# program that blasts data into the DB, but after about 3M rows or so I get an errror: ERROR: could not extend relation 1663/41130/41177: No space left on device HINT: Check free disk space. If I do a full VACUUM on the table being inserted into, the error goes away but it comes back very quickly. Obviously, I wouldn't want this happening in a production environment. I've noticed some auto-vacuum settings as well (I just checked the box and left all the defaults) but that doesn't seem to help too much. What's the recommended setup in a production environment for tables where tons of data will be inserted? It seems to me there's some sort of "max table size" before you have to allocate more space on the disk, however I can't seem to find where these settings are and how to allow millions of rows to be inserted into a table without having to vacuum every few million rows.. Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
