Re: [HACKERS] Help me recovering data
Not being able to issue new transactions *is* data loss --- how are you going to get the system out of that state? Yes, but I also would prefer the server to say something as The database is full, please vacuum. - the same as when the hard disk is full and you try to record something on it - it's not exactly data loss, just an incapability to continue the job. The thing is that a warning is issued only when you start the vacuum itself: play=# VACUUM; WARNING: some databases have not been vacuumed in 1613770184 transactions HINT: Better vacuum them within 533713463 transactions, or you may have a wraparound failure. VACUUM So, it's something like the chicken and the egg problem, you have to vacuum in order to receive a message that you had to do it earlier, but sometimes it's just too late. As it was in my case, I have just discovered that almost all of my data is missing - not even a notice or a warning message to let me know that the end of the world is approaching. :) Regards, Kouber Saparev ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Help me recovering data
The checkpointer is entirely incapable of either detecting the problem (it doesn't have enough infrastructure to examine pg_database in a reasonable way) or preventing backends from doing anything if it did know there was a problem. Well, I guess I meant 'some regularly running process'... I think people'd rather their db just stopped accepting new transactions rather than just losing data... Not being able to issue new transactions *is* data loss --- how are you going to get the system out of that state? Not allowing any transactions except a vacuum... autovacuum is the correct long-term solution to this, not some kind of automatic hara-kiri. Yeah, seems like it should really happen soon... Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] UTF8 or Unicode
On Mon, 2005-02-14 at 22:05 -0500, Bruce Momjian wrote: Abhijit Menon-Sen wrote: At 2005-02-14 21:14:54 -0500, pgman@candle.pha.pa.us wrote: Should our multi-byte encoding be referred to as UTF8 or Unicode? The *encoding* should certainly be referred to as UTF-8. Unicode is a character set, not an encoding; Unicode characters may be encoded with UTF-8, among other things. (One might think of a charset as being a set of integers representing characters, and an encoding as specifying how those integers may be converted to bytes.) I know UTF8 is a type of unicode but do we need to rename anything from Unicode to UTF8? I don't know. I'll go through the documentation to see if I can find anything that needs changing. I looked at encoding.sgml and that mentions Unicode, and then UTF8 as an acronym. I am wondering if we need to make UTF8 first and Unicode second. Does initdb accept UTF8 as an encoding? in PG: unicode = utf8 = utf-8 Our internal routines in src/backend/utils/mb/encnames.c accept all synonyms. The official internal PG name for UTF-8 is UNICODE :-( It's historical reason that UTF8 = UNICODE, because there was UNICODE first. It's same like WIN for WIN1251 (in sources it's marked as _dirty_ alias)... I think initdb uses pg_char_to_encoding() from src/backend/utils/mb/encnames.c and it should be accept all aliases. Karel -- Karel Zak [EMAIL PROTECTED] ---(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] I will be on Boston
I will be at the BLU booth Tuesday. Any and all, drop by. I will be on Boston for Linuxworld from Tuesday through Thursday. I will read email only occasionally. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] getting oid of function
How would this differ from PERFORM? I think perform goes through the SQL by using SPI to execute the function, where as this statement will invoke a plpgsql function without going through the sql ( :-) ..in case i manage to add this statement ) thankz alot for your replies regards Sibtay On Tue, 15 Feb 2005 14:55:38 +1100, Neil Conway [EMAIL PROTECTED] wrote: On Mon, 2005-02-14 at 17:02 +0500, Sibtay Abbas wrote: thank you for the detailed reply But what i wanted to know is that how can we actually get a function's oid from its name from within postgresql code itself You'll want to query the syscache. Note that due to function overloading, there may be multiple functions with the same name, so you'll need to figure out which one ought to be invoked by using the number and types of the parameters. See FuncnameGetCandidates() in namespace.c for an example. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] UTF8 or Unicode
Am Dienstag, 15. Februar 2005 10:22 schrieb Karel Zak: in PG: unicode = utf8 = utf-8 Our internal routines in src/backend/utils/mb/encnames.c accept all synonyms. The official internal PG name for UTF-8 is UNICODE :-( I think in the SQL standard the official name is UTF8. If someone wants to verify that this is the case and is exactly the encoding we offer (perhaps modulo the 0x1 issue), then it might make sense to change the canonical form to UTF8. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] enforcing a plan (in brief)
And the user maintenance of updating those hints for every release of PostgreSQL as we improve the database engine. I don't think so. Basically an optimizer hint simply raises or lowers the cost of an index, mandates a certain join order, allows or disallows a seq scan ... Imho it is not so far from the things people currently do with the set seq_scan= type of commands. (I don't think actually giving a certain plan is a good idea) A good optimizer hint system would imho not circumvent the optimizer, but only give it hints. The hints should be very specifically aimed, like an index on column x is going to be more expensive than you (the optimizer) think, if used with this query. like: select /*+ avoid_index(atab atab_x0) */ * from atab ... The people who are actually doing the work think their time is more usefully spent on improving the planner's intelligence than on devising ways to override it. The subject of this mail and override it imho goes too far, I would like to be able to give advice in the form of hints to the optimizer. One consistent problem is the planner not being able to handle this or that scenario. At this stage, the *best* way to improve the planner is to add the ability to place hints in the plan. sql statement I agree. Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
On Sun, 13 Feb 2005 19:06:34 -0500 (EST), Bruce Momjian pgman@candle.pha.pa.us wrote: Anyway, this is too large to put into 8.0, but I am attaching a patch for 8.1 that has the proper configure tests to check if the C library supports this behavior. If it does not, the build will use our port/snprintf.c. One problem with that is that our snprintf.c is not thread-safe. Seems the increases use of it will require us to fix this soon. I have added to TODO: * Make src/port/snprintf.c thread-safe Okay, I am applying your patch to CVS HEAD and getting hands on making snprintf.c thread-safe. I will submit a roll up pathch in a day or two. Regards, Nicolai ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS]
I've tested the performance of 8.0.1 at my dual-boot notebook (Linux and Windows XP). I installed 8.0.1 for Linux and Windows XP, and run pgbench -c 1 -t 1000 Under Linux (kernel 2.6.10) I got about 800 tps, and under Windows XP - about 20-24 tps. Next I switched off virtual memory under Windows (as it was recommended in posting http://www.pgsql.ru/db/mw/msg.html?mid=2026070). It does not help. Without virtual memory I got 15-17 tps. Several yeas ago (about 1997-1998) Oleg Bartunov and me had the same performance results (Linux vs Windows NT + cygwin). It was the discussion at this list with resume that the reason is the implementation of shared memory under Windows. Every IPC operation results the HDD access. Looks like this is the same for 8.0.1. Did somebody resolved this issue successfully? _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(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]
I've tested the performance of 8.0.1 at my dual-boot notebook (Linux and Windows XP). I installed 8.0.1 for Linux and Windows XP, and run pgbench -c 1 -t 1000 Under Linux (kernel 2.6.10) I got about 800 tps, and under Windows XP - about 20-24 tps. Next I switched off virtual memory under Windows (as it was recommended in posting http://www.pgsql.ru/db/mw/msg.html?mid=2026070). It does not help. Without virtual memory I got 15-17 tps. Question 1: Is your writeback cache really disabled in Linux, on the harddrive? Windows fsync will *write through the disk write cache* if the driver is properly implemented. AFAIK, on Linux if write cache is enabled on the drive, fsync will only get into the cache. 800tps sounds unreasonably high on a notebook. Question 2: Please try disabling the stats connector and see if that helps. Merlin Moncure reported some scalability issues with the stats collector previously. Several yeas ago (about 1997-1998) Oleg Bartunov and me had the same performance results (Linux vs Windows NT + cygwin). It was the discussion at this list with resume that the reason is the implementation of shared memory under Windows. Every IPC operation results the HDD access. It shouldn't in 8.0 - at least not on the native win32. Don't know about cygwin. //Magnus ---(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]
On Tue, 15 Feb 2005, Magnus Hagander wrote: I've tested the performance of 8.0.1 at my dual-boot notebook (Linux and Windows XP). I installed 8.0.1 for Linux and Windows XP, and run pgbench -c 1 -t 1000 Under Linux (kernel 2.6.10) I got about 800 tps, and under Windows XP - about 20-24 tps. Next I switched off virtual memory under Windows (as it was recommended in posting http://www.pgsql.ru/db/mw/msg.html?mid=2026070). It does not help. Without virtual memory I got 15-17 tps. Question 1: Is your writeback cache really disabled in Linux, on the harddrive? Windows fsync will *write through the disk write cache* if the driver is properly implemented. AFAIK, on Linux if write cache is enabled on the drive, fsync will only get into the cache. Difficult to say concerning writeback cache... I have 2.6.10 without any additional tuning, file system is ext2. From dmesg: hda: TOSHIBA MK8026GAX, ATA DISK drive hda: max request size: 128KiB hda: 156301488 sectors (80026 MB), CHS=65535/16/63, UDMA(100) hda: cache flushes supported 800tps sounds unreasonably high on a notebook. Yes, I also was surprized. The same test at Xeon 2.4GHz server indicates about 700 tps. But it is another issue. Question 2: Please try disabling the stats connector and see if that helps. Merlin Moncure reported some scalability issues with the stats collector previously. Sorry, what is stats connector? Several yeas ago (about 1997-1998) Oleg Bartunov and me had the same performance results (Linux vs Windows NT + cygwin). It was the discussion at this list with resume that the reason is the implementation of shared memory under Windows. Every IPC operation results the HDD access. It shouldn't in 8.0 - at least not on the native win32. Don't know about cygwin. Yes, I also expected that the performance for native implementation will be more reasonable. In fact, during pgbench test under Windows and under Linux HDD LED lights continiously, so looks like under Windows there are much more disk operations compared with Linux. Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(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]
Magnus Hagander wrote: I've tested the performance of 8.0.1 at my dual-boot notebook (Linux and Windows XP). I installed 8.0.1 for Linux and Windows XP, and run pgbench -c 1 -t 1000 Under Linux (kernel 2.6.10) I got about 800 tps, and under Windows XP - about 20-24 tps. Next I switched off virtual memory under Windows (as it was recommended in posting http://www.pgsql.ru/db/mw/msg.html?mid=2026070). It does not help. Without virtual memory I got 15-17 tps. Question 1: Is your writeback cache really disabled in Linux, on the harddrive? Windows fsync will *write through the disk write cache* if the driver is properly implemented. AFAIK, on Linux if write cache is enabled on the drive, fsync will only get into the cache. 800tps sounds unreasonably high on a notebook. Question 2: Please try disabling the stats connector and see if that helps. Merlin Moncure reported some scalability issues with the stats collector previously. Also, didn't someone recently report some very significant performance differences Windows networking QoS (Quality of Service) installed? You might try that. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS]
On Tue, 15 Feb 2005, Matthew T. O'Connor wrote: Magnus Hagander wrote: Also, didn't someone recently report some very significant performance differences Windows networking QoS (Quality of Service) installed? You might try that. It's unlikely. Postmaster listens loopback, AFAIK loopback does not affected by any networking tuning and parameters. Additionally, during pgbench test I have not any other network activity, hence QoS flag looks unrelated for this problem. Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
On Tue, Feb 15, 2005 at 08:03:39PM +0300, E.Rodichev wrote: On Tue, 15 Feb 2005, Magnus Hagander wrote: Question 1: Is your writeback cache really disabled in Linux, on the harddrive? Windows fsync will *write through the disk write cache* if the driver is properly implemented. AFAIK, on Linux if write cache is enabled on the drive, fsync will only get into the cache. Difficult to say concerning writeback cache... I have 2.6.10 without any additional tuning, file system is ext2. From dmesg: hda: TOSHIBA MK8026GAX, ATA DISK drive hda: max request size: 128KiB hda: 156301488 sectors (80026 MB), CHS=65535/16/63, UDMA(100) hda: cache flushes supported Write caching is generally on by default with IDE drives. Disable it like so: hdparm -W 0 /dev/hda -Mike Adler ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
I've tested the performance of 8.0.1 at my dual-boot notebook (Linux and Windows XP). I installed 8.0.1 for Linux and Windows XP, and run pgbench -c 1 -t 1000 Under Linux (kernel 2.6.10) I got about 800 tps, and under Windows XP - about 20-24 tps. Next I switched off virtual memory under Windows (as it was recommended in posting http://www.pgsql.ru/db/mw/msg.html?mid=2026070). It does not help. Without virtual memory I got 15-17 tps. Question 1: Is your writeback cache really disabled in Linux, on the harddrive? Windows fsync will *write through the disk write cache* if the driver is properly implemented. AFAIK, on Linux if write cache is enabled on the drive, fsync will only get into the cache. Difficult to say concerning writeback cache... I have 2.6.10 without any additional tuning, file system is ext2. From dmesg: hda: TOSHIBA MK8026GAX, ATA DISK drive hda: max request size: 128KiB hda: 156301488 sectors (80026 MB), CHS=65535/16/63, UDMA(100) hda: cache flushes supported Run: hdparm -I /dev/hda If you get a line like: Commands/features: Enabled Supported: *READ BUFFER cmd *WRITE BUFFER cmd *Host Protected Area feature set *Look-ahead *Write cache ... (last line is what matters here) you have write cacheing enabled. To turn it of, run hdparm -W0 /dev/hda Not sure if you need to reboot, I don'tt hink so. Then re-run the benchmark on linux. 800tps sounds unreasonably high on a notebook. Yes, I also was surprized. The same test at Xeon 2.4GHz server indicates about 700 tps. But it is another issue. The CPU probably has nothing to do with this, it's probably all I/O. Question 2: Please try disabling the stats connector and see if that helps. Merlin Moncure reported some scalability issues with the stats collector previously. Sorry, what is stats connector? That's supposed to be stats collector, as you realised in your other mail. Sorry. Several yeas ago (about 1997-1998) Oleg Bartunov and me had the same performance results (Linux vs Windows NT + cygwin). It was the discussion at this list with resume that the reason is the implementation of shared memory under Windows. Every IPC operation results the HDD access. It shouldn't in 8.0 - at least not on the native win32. Don't know about cygwin. Yes, I also expected that the performance for native implementation will be more reasonable. In fact, during pgbench test under Windows and under Linux HDD LED lights continiously, so looks like under Windows there are much more disk operations compared with Linux. That would be consistent with the theory that write-back caching is enabled on linux and not on windows. //Magnus ---(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]
On Tue, 15 Feb 2005, Magnus Hagander wrote: Run: hdparm -I /dev/hda If you get a line like: Commands/features: Enabled Supported: *READ BUFFER cmd *WRITE BUFFER cmd *Host Protected Area feature set *Look-ahead *Write cache ... (last line is what matters here) you have write cacheing enabled. Thanks. Ok, really I have it enabled (fortunately :) To turn it of, run hdparm -W0 /dev/hda Done. Now it is disabled. Not sure if you need to reboot, I don'tt hink so. Then re-run the benchmark on linux. No, under Linux reboot makes nothing (it is completely dynamical system). Now I have /usr/local/pgsql/bin:14pgbench -c 1 -t 500 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 1 number of transactions per client: 500 number of transactions actually processed: 500/500 tps = 89.528064 (including connections establishing) tps = 89.560730 (excluding connections establishing) It is about 9 times slower. But again 4 times faster then under Windows. After [EMAIL PROTECTED]:/e# hdparm -W1 /dev/hda /dev/hda: setting drive write-caching to 1 (on) /usr/local/pgsql/bin:24pgbench -c 1 -t 500 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 1 number of transactions per client: 500 number of transactions actually processed: 500/500 tps = 846.189777 (including connections establishing) tps = 849.481986 (excluding connections establishing) Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Query optimizer 8.0.1 (and 8.0)
On Mon, Feb 14, 2005 at 09:55:38AM -0800, Ron Mayer wrote: I still suspect that the correct way to do it would not be to use the single correlation, but 2 stats - one for estimating how sequential/random accesses would be; and one for estimating the number of pages that would be hit. I think the existing correlation does well for the first estimate; but for many data sets, poorly for the second type. Should this be made a TODO? Is there some way we can estimate how much this would help without actually building it? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PostgreSQL at Linux World
I was at Linux world Tuesday, it was pretty good. I was in the org pavilion, where the real Linux resides. The corporate people were on the other side of the room. (There was a divider where the rest rooms and elevators were.) I say that this was where the real linux resides because all the real brains behind Linux were there, x.org, debian, fsf, kde, gnome, gentoo, and so on. Bruce was sort of in the middle of the room with his company. Our booth was off in the corner. (cold drafty corner.) Anyway, I noticed Pervasive software selling PostgreSQL support in the corporate end of the room. Bless them, they were trying to sell me on a GUI front end for PostgreSQL, asked What tool to you use to administer PostgreSQL? My answer? psql The crestfallen salesman knew that I was not interested in GUI frontends. It was kind of funny, sad in a a way, but funny never the less. Anyway, it was good to see PostgreSQL out in the corporate end of the building, I even got a couple very cute rubber elephants. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] enforcing a plan (in brief)
On Tue, 2005-02-15 at 02:38 -0500, Greg Stark wrote: I don't know what software you work with but the Postgres source is far and away the best documented source I've had the pleasure to read. I agree the PostgreSQL source is very nice (for the most part), but I think there could be more higher-level documentation of the internals. For example, until a few days ago the access method API was completely undocumented (in SGML, at least). Tom has now written some good docs for it -- that's an example of the kind of improvement I'm talking about. Having documents describing how to add a new index type, how to add a new planner node, how to add a new DML/DDL command, and so forth would be cool. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
