Re: [HACKERS] Improving speed of copy
On 20 Sep 2002 at 10:27, Mike Benoit wrote: On Fri, 2002-09-20 at 08:52, Shridhar Daithankar wrote: Besides there is issue of space. Mysql takes 1.4GB space for 1.2GB text data and postgresql takes 3.2GB of space. Even with 40 bytes per row overhead mentioned in FAQ, that should come to around 1.7GB, counting for 40% increase in size. Vacuum was run on database. How did you calculate the size of database? If you used du make sure you do it in the data/base directory as to not include the WAL files. OK latest experiments, I turned number of buffers 15K and fsync is disabled.. Load time is now 1250 sec. I noticed lots of notices in log saying, XLogWrite: new log files created.. I am pushing wal_buffers to 1000 and wal_files to 40 to test again.. I hope it gives me some required boost.. And BTW about disk space usage, it's 2.6G with base pg_xlog taking 65M. still not good.. Will keep you guys updated.. Bye Shridhar -- It is necessary to have purpose.-- Alice #1, I, Mudd, stardate 4513.3 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Hosed PostGreSQL Installation
Pete St. Onge [EMAIL PROTECTED] writes: As a result of some disk errors on another drive, an admin in our group brought down the server hosting our pgsql databases with a kill -KILL after having gone to runlevel 1 and finding the postmaster process still running. No surprise, our installation was hosed in the process. That should not have been a catastrophic mistake in any version = 7.1. I suspect you had disk problems or other problems. Klamath suggested that I run pg_controldata: ... Latest checkpoint's StartUpID:21 Latest checkpoint's NextXID: 615 Latest checkpoint's NextOID: 18720 These numbers are suspiciously small for an installation that's been in production awhile. I suspect you have not told us the whole story; in particular I suspect you already tried pg_resetxlog -f, which was probably not a good idea. If I look into the pg_xlog directory, I see this: -rw---1 postgres postgres 16777216 Sep 20 23:13 0002 -rw---1 postgres postgres 16777216 Sep 19 22:09 0002007E Yeah, your xlog positions should be a great deal higher than they are, if segment 2/7E was previously in use. It is likely that you can recover (with some uncertainty about integrity of recent transactions) if you proceed as follows: 1. Get contrib/pg_resetxlog/pg_resetxlog.c from the 7.2.2 release (you can't use 7.1's pg_resetxlog because it doesn't offer the switches you'll need). Compile it *against your 7.1 headers*. It should compile except you'll have to remove this change: *** *** 853,858 --- 394,403 page-xlp_magic = XLOG_PAGE_MAGIC; page-xlp_info = 0; page-xlp_sui = ControlFile.checkPointCopy.ThisStartUpID; + page-xlp_pageaddr.xlogid = + ControlFile.checkPointCopy.redo.xlogid; + page-xlp_pageaddr.xrecoff = + ControlFile.checkPointCopy.redo.xrecoff - SizeOfXLogPHD; record = (XLogRecord *) ((char *) page + SizeOfXLogPHD); record-xl_prev.xlogid = 0; record-xl_prev.xrecoff = 0; Test it using its -n switch to make sure it reports sane values. 2. Run the hacked-up pg_resetxlog like this: pg_resetxlog -l 2 127 -x 10 $PGDATA (the -l position is next beyond what we see in pg_xlog, the 1-billion XID is just a guess at something past where you were. Actually, can you give us the size of pg_log, ie, $PGDATA/global/1269? That would allow computing a correct next-XID to use. Figure 4 XIDs per byte, thus if pg_log is 1 million bytes you need -x at least 4 million.) 3. The postmaster should start now. 4. *Immediately* attempt to do a pg_dumpall. Do not pass GO, do not collect $200, do not let in any interactive clients until you've done it. (I'd suggest tweaking pg_hba.conf to disable all logins but your own.) 5. If pg_dumpall succeeds and produces sane-looking output, then you've survived. initdb, reload the dump file, re-open for business, go have a beer. (Recommended: install 7.2.2 and reload into that, not 7.1.*.) You will probably still need to check for partially-applied recent transactions, but for the most part you should be OK. 6. If pg_dumpall fails then let us know what the symptoms are, and we'll see if we can figure out a workaround for whatever the corruption is. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] NUMERIC's transcendental functions
I have noticed a change in behavior following the recent changes for casting of numeric constants. In prior releases, we got regression=# select log(10.1); log -- 1.00432137378264 (1 row) CVS tip gives regression=# select log(10.1); log -- 1.0043213738 (1 row) The reason for the change is that 10.1 used to be implicitly typed as float8, but now it's typed as numeric, so this command invokes log(numeric) instead of log(float8). And log(numeric)'s idea of adequate output precision seems low. Similar problems occur with sqrt(), exp(), ln(), pow(). I realize that there's a certain amount of cuteness in being able to calculate these functions to arbitrary precision, but this is a database not a replacement for bc(1). ISTM the numeric datatype is intended for exact calculations, and so transcendental functions (which inherently have inexact results) don't belong. So proposal #1 is to rip out the numeric versions of these functions. If you're too attached to them, proposal #2 is to force them to calculate at least 16 digits of output, so that we aren't losing any accuracy compared to prior behavior. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Inconsistent Conversion Names
Oleg Bartunov writes: Someone said that the conversion table is actually koi8r + koi8u, being different from IANA's koi8_r. Not sure though. I found mention in the archives by Oleg B. that it is in fact koi8_r. on my system (linux) I have ru_RU.KOI8-R locale. We're not talking about your system. :-) Do you know whether PostgreSQL's conversion tables cover koi8-r or koi8-u or both? The documentation contains contradicting information about that. Actually, since we use the officially provided Unicode conversion tables, we should know what they cover. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump problems in upgrading
Awhile back, Oliver Elphick [EMAIL PROTECTED] wrote: I am trying to populate a 7.3 database from a 7.2 dump. I used 7.3's pg_dumpall, but this did not handle all the issues: 1. The language dumping needs to be improved: This is now fixed. 2. Either casts or extra default conversions may be needed: This too --- at least in the example you give. 3. A view is being created before one of the tables it refers to. On thinking about it, I'm having a hard time seeing how that case could arise, unless the source database was old enough to have wrapped around its OID counter. I'd be interested to see the details of your case. While the only long-term solution is proper dependency tracking in pg_dump, there might be some shorter-term hack that we should apply... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump problems in upgrading
On Sat, 2002-09-21 at 19:49, Tom Lane wrote: 3. A view is being created before one of the tables it refers to. On thinking about it, I'm having a hard time seeing how that case could arise, unless the source database was old enough to have wrapped around its OID counter. I'd be interested to see the details of your case. While the only long-term solution is proper dependency tracking in pg_dump, there might be some shorter-term hack that we should apply... While I don't think that the oids have wrapped round, the oid of the table in question is larger than the oid of the view. It is quite likely that the table was dropped and recreated after the view was created. In fact, the view no longer works: ERROR: Relation sales_forecast with OID 26246751 no longer exists so that must be what happened. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Charge them that are rich in this world, that they not be highminded nor trust in uncertain riches, but in the living God, who giveth us richly all things to enjoy; That they do good, that they be rich in good works, ready to distribute, willing to communicate; Laying up in store for themselves a good foundation against the time to come, that they may lay hold on eternal life. I Timothy 6:17-19 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump problems in upgrading
Oliver Elphick [EMAIL PROTECTED] writes: 3. A view is being created before one of the tables it refers to. While I don't think that the oids have wrapped round, the oid of the table in question is larger than the oid of the view. It is quite likely that the table was dropped and recreated after the view was created. In fact, the view no longer works: ERROR: Relation sales_forecast with OID 26246751 no longer exists so that must be what happened. Ah ... so the view was broken already. I'm surprised you didn't get a failure while attempting to dump the view definition. The new dependency stuff should help prevent this type of problem in future ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PGXLOG variable worthwhile?
On Fri, 20 Sep 2002, Thomas Lockhart wrote: Well, what I was hoping for, but no longer expect, is that features (store xlog in another area) can be implemented and applied without rejection by the new gatekeepers. It can be, and very simply. So long as you do it in the way which is not error-prone, rather than the way which is. I have no fundamental objection to extending and replacing implementation features as positive contributions to development. I do have trouble with folks rejecting features without understanding the issues, and sorry, there was a strong thread of why would anyone want to put storage on another device to the discussion. I doubt it. There was perhaps a strong thread of windows users are loosers, but certainly Unix folks put storage on another device all the time, using symlinks. This was mentioned many, many times. There has been a fundamental shift in the quality and civility of discussions over issues over the last couple of years, and I was naively hoping that we could work through that on this topic. Not happening, and not likely too. Well, when you're going to bring in Windows in a pretty heavily open-source-oriented group, no, it's not likely you're going to bring everyone together. (This is not a value judgement, it's just a, Hello, this is the usenet (or something similar), observation. That said, again, I don't think anybody was objecting to what you wanted to do. It was simply a bad implementation that I, and probably all the others, were objecting to. So please don't go on like we didn't like the concept. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Lyris looking to help fix PostgresSQL crashing problems
John Buckman [EMAIL PROTECTED] writes: It seems that with larger database sizes (500,000 rows and larger) and high stress, the server daemon has a tendency to core. We'd love to see some stack traces ... Yeah, I just didn't know what form this list prefers to work on things, which is why I'd prefer to hire a regular participant of this list. If gcc 'where' stack traces are what you want, we can do that. I suspect that the problems may be platform-or-build related, because we've often had trouble replicating customer problems on our own sysems. For example, we had many reports of problems with 7.2.x, and saw it crash often on a customer's redhat machine that we had ssh access to, but couldn't make it crash in our own lab. :( That's why we need help. If we could make a simple C test case that crashed pgsql, I'm sure you guys could fix the problem in a jiffy. -john ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Lyris looking to help fix PostgresSQL crashing problems
John Buckman wrote: John Buckman [EMAIL PROTECTED] writes: It seems that with larger database sizes (500,000 rows and larger) and high stress, the server daemon has a tendency to core. We'd love to see some stack traces ... Yeah, I just didn't know what form this list prefers to work on things, which is why I'd prefer to hire a regular participant of this list. If gcc 'where' stack traces are what you want, we can do that. Yep, in most cases, the crash creates a core file in the database directory. A backtrace of that core file is usually a good start. You should to sure there are debugging symbols in the binary (gcc -g). The server log files also often contain valuable information. I suspect that the problems may be platform-or-build related, because we've often had trouble replicating customer problems on our own systems. For example, we had many reports of problems with 7.2.x, and saw it crash often on a customer's redhat machine that we had ssh access to, but couldn't make it crash in our own lab. :( That's why we need help. If we could make a simple C test case that crashed pgsql, I'm sure you guys could fix the problem in a jiffy. Yes, that does make it harder, but a backtrace usually gets us started. It may also be tickling some OS bug or a hardware failure, or a simple exhaustion of some resource. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Lyris looking to help fix PostgresSQL crashing problems
John Buckman [EMAIL PROTECTED] writes: It seems that with larger database sizes (500,000 rows and larger) and high stress, the server daemon has a tendency to core. We'd love to see some stack traces ... Yeah, I just didn't know what form this list prefers in terms of info to be able to work on things, which is why I'd prefer to hire a regular participant of this list. If gcc 'where' stack traces from core files are what you want, we can do that. I suspect that the problems may be platform-or-build related, because we've often had trouble replicating customer problems on our own sysems. For example, we had many reports of problems with 7.2.x, and saw it crash often on a customer's redhat machine that we had ssh access to, but couldn't make it crash in our own lab. :( That's why we need help. If we could make a simple C test case that crashed pgsql, I'm sure you guys could fix the problem in a jiffym but localizing and recreating a problem is always 80% of it. -john ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
