[HACKERS] Extrordinarily Poor Performance.... RESOLUTION
Thanks to input Bruce M., figured out my performance problems - had to do with a few QUERIES FROM HELL! After running EXPLAIN a few times I fine tuned some of the worst ones, mostly over use of sub queries. Still combing through my query log. Getting there... -r --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: New Linux xfs/reiser file systems
Here is a radical idea... What is it that is causing Postgres trouble? It is the file system's attempts to maintain some integrity. So I proposed a simple dbfs sort of thing which was the most basic sort of file system possible. I'm not sure, but I think we can test this hypothesis on the FAT32 file system on Linux. As far as I know, FAT32 (FAT in general) is a very simple file system and does very little during operation, except read and write the files and manage what's been allocated. Plus, the allocation table is very simple in comparison all the other file systems. Would pgbench run on a system using ext2, Reiser, then FAT32 be sufficient to get a feeling for the type of performance Postgres would get, or am I just off the wall? If this idea has some merit, what would be the best way to test it? Move the pg_xlog directory first, then try base? What's the best methodology to try? carl garland wrote: Just put a note in the installation docs that the place where the database is initialised to should be on a non-Reiser, non-XFS mount... Sure, we can do that now. I still think this is not necessarily the right approach either. One major purpose of using a journaling fs is for fast boot up time after crash. If you have a 100 GB database you may wish to have the data on XFS. I do think that the WAL log should be on a separate disk and on a non-journaling fs for performance. Best Regards, Carl Garland _ Get your FREE download of MSN Explorer at http://explorer.msn.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- I'm not offering myself as an example; every life evolves by its own laws. http://www.mohawksoft.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: New Linux xfs/reiser file systems
On Thu, May 03, 2001 at 11:41:24AM -0400, Bruce Momjian wrote: ext2 has serious problems with corrupt file systems after a crash, so I understand the need to move to another file system type. I have been waitin for Linux to get a more modern file system. Unfortunately, the new ones seem to be worse for PostgreSQL. If you fsync() a directory in Linux, all the metadata within that directory will be written out to disk. As for filesystem corruption, I can say the e2fsck is among the best fsck programs out there, and I've only ever had 1 occasion where I've lost any data on an ext2 filesystem, and that was due to bad sectors causing me to lose the root directory. (Well, apart from human errors, but that doesn't count) OK, we have considered this, but frankly, the new, modern file systems like FFS/softupdates have i/o rates near raw speed, with all the advantages a file system gives us. I believe most commercial dbs are moving away from raw devices and toward file systems. In the old days the SysV file system was pretty bad at i/o fragmentation, so they used raw devices. And Solaris' 1/01 media has better support for O_DIRECT (?), which they claim gives you 93% of the speed of a raw device. (Or something like that; I read this in marketing material a couple of months ago) Raw devices are designed to have filesystems on them. The only excuses for userland tools accessing them, are fs-specific tools (eg. dump, fsck, etc), or for non-unix filesystem tools, where the unix VFS doesn't handle things properly (hfstools). The ability to put indexes on a separate volume from data. The ability to put different tables on different volumes. And so on. We certainly need that, but raw devices would not make this any easier, I think. It would be cool if either at compile time or at database creation time, we could specify a printf-like format for placing tables, indexes, etc. It could become a serious problem as people start using reiser/xfs for their file systems and don't understand the performance problems. Even more likely is that they will turn off fsync, thinking reiser doesn't need it, when in fact, I think it does. ReiserFS only supports metadata logging. The performance slowdown must be due to logging things like mtime or atime, because otherwise ReiserFS is a very high performance FS. (Although, I admittedly haven't used it since it was early in it's development) -- Michael Samuel [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])
[HACKERS] Re: CVSup not working!
most odd ... its set to start on rebooted, but either it went down on its
own, or didn't ... restarted now, let me know if its not working ...
On Fri, 4 May 2001, Thomas Lockhart wrote:
cvsup -L 2 postgres.cvsup
Parsing supfile postgres.cvsup
Connecting to postgresql.org
Cannot connect to postgresql.org: Connection refused
Will retry at 22:31:23
...
Me too. Marc, could you take a peek at it? cvsupd seems to be gone or
port blocked or ??
- Thomas
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[HACKERS] Re: New Linux xfs/reiser file systems
Michael Samuel wrote: ReiserFS only supports metadata logging. The performance slowdown must be due to logging things like mtime or atime, because otherwise ReiserFS is a very high performance FS. (Although, I admittedly haven't used it since it was early in it's development) The way I understand it is that ReiserFS does not attempt to separate files at the block level. Multiple files can live in the same disk block. This is cool if you have many small files, but the extra overhead for large files such as those used by a database, is a bit much. I read some stuff about a year ago, and my impressions forced me to conclude that ReiserFS was geared toward applications. Which is a pretty good thing for applications, but not for databases. I really think a simple low down dirty file system is just what the doctor ordered for postgres. Remember, general purpose file systems must do for files what Postgres is already doing for records. You will always have extra work. I am seriously thinking of trying a FAT32 as pg_xlog. I wonder if it will improve performance, or if there is just something fundamentally stupid about FAT32 that will make it worse? -- I'm not offering myself as an example; every life evolves by its own laws. http://www.mohawksoft.com ---(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] Re: Extrordinarily Poor Performance....
Thus spake Ryan Mahoney
!! I haven't ran VACUUM ANALYZE since last night. Just ran it -
performance has improved significantly. I think I am going to have to run
it hourly during this high traffic time. Postmasters are still utilizing
about 100% of the CPU. Is this normal? I am considering increasing the
shmmax again.
Although it isn't supposed to be necessary, I find that I have to dump and
reload once in a while to keep performance hight.
--
D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: New Linux xfs/reiser file systems
Before we get too involved in speculating, shouldn't we actually measure the performance of 7.1 on XFS and Reiserfs? Since it's easy to disable fsync, we can test whether that's the problem. I don't think that logging file systems must intrinsically give bad performance on fsync since they only log metadata changes. I don't have a machine with XFS installed and it will be at least a week before I could get around to a build. Any volunteers? Ken Hirsch ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: New Linux xfs/reiser file systems
mlw [EMAIL PROTECTED] writes: I have looked at Reiser, and I don't think it is a file system suited for very large files, or applications such as postgres. What's the problem with big files? ReiserFS v2 doesn't seem to support it, while v3 seems just fine (of the ondisk format) That said, I'm certainly looking forward to xfs - I believe it will be the most widely used of the current batch of journaling file systems (reiserfs, jfs, XFS and ext3, the latter mainly focusing on an easy migration path for existing system) -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Postgresql.exe 7.1 for M$ OS
Where i can find a wonderful installer for postgres 7.1 on our windows2000 advanced servers ? We use postgres on linux systems, But... I'm not able to find on the net a binary for Postgres 7.1. I don't want to compile, i want a simple installer. windows machines are 200 Million, if you'll make a maintained binary postgres version for windows probably more developers more bug fixing will be... It's critical. Also in the web site , will be wonderful if, on the navbar, the download links are always there (latest stable, jdbc, odbc, DBD::Pg) etc. thanks, valter _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Postgresql.exe 7.1 for M$ OS
V. M. wrote: Where i can find a wonderful installer for postgres 7.1 on our windows2000 advanced servers ? www.cygwin.com -- Tout penseur avare de ses pensees est un penseur de Radin. -- Pierre Dac ---(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] Re: New Linux xfs/reiser file systems
Bruce == Bruce Momjian [EMAIL PROTECTED] writes: Well, arguably if you're setting up a database server then a reasonable DBA should think about such things... Bruce Yes, but people have trouble installing PostgreSQL. I Bruce can't imagine walking them through a newfs. In most of linux-land, the DBA is probably also the sysadmin. In bigger shops, and those which currently run, say Oracle or Sybase, the two roles are separate. When they are separate, you don't have to walk the DBA through it; he just walks over to the sysadmin and says I need X megabytes of space on a new Y filesystem. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Packaging 7.1.1
Tom Lane wrote: Seems like that stuff should be in CVS somewhere ... if only so someone else can pick up the ball if you get run over by a truck :-(. My wife appreciates the sentiment :-). As it stands now, better documentation distributed in the source RPM would help greatly. Everything necessary to do the build and maintain the package is in the source RPM as it stands now -- evidenced by the Linux distributors being able to take our source RPM, massage it to fit their particular system, and run with it. And I have a scad of history available in specfile form If it's just a small amount of code, I don't see what the harm would be in including it in the regular distro, though we should talk about just where it should go. If it's a large amount of code then perhaps a separate CVS project would be better, so that people who have no use for it don't end up pulling/downloading it. Not counting the JDBC jars, it's a hundred K or so uncompressed. The spec file is around 30k -- a small amount of code. contrib/rpm-dist? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] Packaging 7.1.1
On Thu, 3 May 2001, Rachit Siamwalla wrote: 1. `pidof` should be `pidof -s` (2 instances) 2. restart) should be stop; sleep x; start ideally, stop should actually wait till postgres fully stops. The sleep is just a temporary fix. Perhaps a naive question, but why not use the pg_ctl for starting and stopping? It has a -w option to have it wait for the stop/start/restart to complete. -rocco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Packaging 7.1.1
Lamar Owen [EMAIL PROTECTED] writes: As to why all these files aren't part of the source tree, well, unless there was a large cry for it to happen, I don't believe it should. PostgreSQL is very platform-agnostic -- and I like that. Including the RPM stuff as part of the Official Tarball (TM) would, IMHO, slant that agnostic stance in a negative way. Seems like that stuff should be in CVS somewhere ... if only so someone else can pick up the ball if you get run over by a truck :-(. If it's just a small amount of code, I don't see what the harm would be in including it in the regular distro, though we should talk about just where it should go. If it's a large amount of code then perhaps a separate CVS project would be better, so that people who have no use for it don't end up pulling/downloading it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] New Linux xfs/reiser file systems
I got some information from Stephen Tweedie on this - please keep him Cc: as he's not on this list Bruce Momjian [EMAIL PROTECTED] writes: I was talking to a Linux user yesterday, and he said that performance using the xfs file system is pretty bad. He believes it has to do with the fact that fsync() on log-based file systems requires more writes. Performance doing what? XFS has known performance problems doing unlinks and truncates, but not synchronous IO. The user should be using fdatasync() for databases, btw, not fsync(). First, XFS, ext3 and reiserfs are *NOT* log-based filesystems. They are journaling filesystems. They have a log, but they are not log-based because they do not store data permanently in a log structure. Berkeley LFS, Sprite and Spiralog are log-based filesystems. With a standard BSD/ext2 file system, WAL writes can stay on the same cylinder to perform fsync. Is that true of log-based file systems? Not true on ext2 or BSD. Write-aheads are _usually_ close to the inode, but not always. For true log-based filesystems, writes are always completely sequential, so the issue just goes away. For journaling filesystems, depending on the setup there may be a seek to the journal involved, but some journaling filesystems can use a separate disk for the journal so no seek is required. I know xfs and reiser are both log based. Do we need to be concerned about PostgreSQL performance on these file systems? I use BSD FFS with soft updates here, so it doesn't affect me. A database normally preallocates its data files and then performs most of its writes using update-in-place. In such cases, fsync() is almost always the wrong thing to be doing --- the data writes have changed nothing in the inode except for the timestamps, and there's no need to flush the timestamps to disk for every write. fdatasync() is designed for this --- if the only inode change is timestamps, fdatasync() will skip the seek to the inode and will only update the data. If any significant inode fields have been changed, then a full flush is done. Using fdatasync, most filesystems will incur no seeks for data flush, regardless of whether the filesystem is journaling or not. Cheers, Stephen -- Trond Eivind Glomsrød Red Hat, Inc. ---(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] Packaging 7.1.1
Lamar Owen [EMAIL PROTECTED] writes: contrib/rpm-dist? Contrib was my first thought also --- but on second thought, the RPM packaging support is hardly contrib-grade material. For a large proportion of our users it's a critical part of the distribution. So, if we are going to have it in the CVS tree at all, I'd vote for putting it in the main tree. Perhaps src/rpm-tools/ or some such name. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Packaging 7.1.1
Tom Lane wrote: Contrib was my first thought also --- but on second thought, the RPM packaging support is hardly contrib-grade material. For a large proportion of our users it's a critical part of the distribution. So, if we are going to have it in the CVS tree at all, I'd vote for putting it in the main tree. Perhaps src/rpm-tools/ or some such name. Let's see where the rest of core and hackers weighs in -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Packaging 7.1.1
Lamar Owen writes: contrib/rpm-dist? A separate CVS module sounds like a better idea to me. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Re: New Linux xfs/reiser file systems
Sure, we can do that now. What do we do when these are the default file systems for Linux? We can tell them to create other types of file What is a 'default file system' ? I know that untill now, everybody is using ext2. But that's only because there hasn't been anything comparable. Now we se ReiserFS, and my SuSE installation offers the choice. In the future, I believe that people can choose from ext2, ReiserFS,xfs, ext3 and maybe more. systems, but that is a pretty big hurdle. I wonder if it would be easier to get reiser/xfs to make some modifications. No, I don't think it's a big hurdle. If you just want to play with PostgreSQL, you wont care. If you're serious, you'll repartition. -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 14.00-18.00Web: www.suse.dk 2000 FrederiksbergLørdag 11.00-17.00 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: New Linux xfs/reiser file systems
[ Charset ISO-8859-1 unsupported, converting... ] Before we get too involved in speculating, shouldn't we actually measure the performance of 7.1 on XFS and Reiserfs? Since it's easy to disable fsync, we can test whether that's the problem. I don't think that logging file systems must intrinsically give bad performance on fsync since they only log metadata changes. I don't have a machine with XFS installed and it will be at least a week before I could get around to a build. Any volunteers? There have been multiple reports of poor PostgreSQL performance on Reiser and xfs. I don't have numbers, though. Frankly, I think we need xfs and reiser experts involved to figure out our options here. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: New Linux xfs/reiser file systems
[ Charset ISO-8859-1 unsupported, converting... ] Sure, we can do that now. What do we do when these are the default file systems for Linux? We can tell them to create other types of file What is a 'default file system' ? I know that untill now, everybody is using ext2. But that's only because there hasn't been anything comparable. Now we se ReiserFS, and my SuSE installation offers the choice. In the future, I believe that people can choose from ext2, ReiserFS,xfs, ext3 and maybe more. But some day the default will be a log-based file system, and people will have to hunt around to create a non-log based one. systems, but that is a pretty big hurdle. I wonder if it would be easier to get reiser/xfs to make some modifications. No, I don't think it's a big hurdle. If you just want to play with PostgreSQL, you wont care. If you're serious, you'll repartition. Yes, but we could get a reputation for slowness on these log-based file systems. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Re: New Linux xfs/reiser file systems
On Fri, May 04, 2001 at 08:02:17AM -0400, mlw wrote: The way I understand it is that ReiserFS does not attempt to separate files at the block level. Multiple files can live in the same disk block. This is cool if you have many small files, but the extra overhead for large files such as those used by a database, is a bit much. It should be at least as fast as other filesystems for large files. I suspect that it would be faster in fact. The only catch is that the performance of reiserfs sucks when it gets past 85% or so full. (ext2 has similar problems) That is pretty standard for most modern file systems. They need that free space to optimize. You can read about all this stuff at http://www.namesys.com/ I really think a simple low down dirty file system is just what the doctor ordered for postgres. Traditional BSD FFS or Solaris UFS is probably the best bet for postgres. That is my opinion. BSD FFS seems to be general enough to give good performance for a large scale of application needs. It is not as fast as XFS for streaming large files (media), and it doesn't optimize small files below the 1k size (fragments), and it does require fsck on reboot. However, looking at all those for PostgreSQL, the costs of the new Linux file systems seems pretty high, especially considering our need for fsync(). What I am really concerned about is when xfs/reiser become the default file systems for Linux, and people complain about PostgreSQL performance. And if we require special file systems, we lose some of our ability to easily grow. Because of ext2's problems with crash recovery, who is going to want to put other data on that file system when they have xfs/reiser available. And boots are going to have to fsck that ext2 file system. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Packaging 7.1.1
Lamar Owen [EMAIL PROTECTED] writes: contrib/rpm-dist? Contrib was my first thought also --- but on second thought, the RPM packaging support is hardly contrib-grade material. For a large proportion of our users it's a critical part of the distribution. So, if we are going to have it in the CVS tree at all, I'd vote for putting it in the main tree. Perhaps src/rpm-tools/ or some such name. It is platform-specific, which would seem to vote for /contrib. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] help!
Hello! I am a Technical Recruiter with MIS Consultants in Toronto Canada and I desperately need to find 2 POSTGRES DBA's for our Toronto client on a 3-4 month renewable contract, open $$$ based on experience. How do I go about finding these guys? Any help is much appreciated, thanks! Jeff Vainio, B.Comm *Technical Recruiter* MIS Consultants 905-305-1455 Ext.#203 1-800-311-2828 Fax#905-305-0033 [EMAIL PROTECTED] www.misconsult.com 3190 Steeles Ave. East, Suite#120 Markham, Ontario L3R 1G9 THIS E-MAIL MESSAGE IS FOR THE ADDRESSED PERSON ONLY. INITIAL INTRODUCTION OF A CANDIDATE VIA E-MAIL OR FAX IS CONSIDERED BY LAW TO BE REPRESENTATED BY MIS Consultants. OUR FEE IS DUE PAYABLE WHEN A CANDIDATE IS HIRED BY YOUR FIRM, A SUBSIDIARY OR DIVISION OR ANY OTHER FIRMS YOU REFER OUR CANDIDATE TO WITHIN A 12 MONTH PERIOD. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Packaging 7.1.1
Trond Eivind Glomsrød wrote: Rachit Siamwalla [EMAIL PROTECTED] writes: Also i never got a response on who actually packages those linux init scripts that appear in the RPM but not on the pgsql cvs tree. (i am also curious on why it is different, and how the RPM is built). Lamar Owen and I. Is the current snapshot available? I have submitted fixes twice now for what I am fairly sure is a bug in the init script. At least one of the posts was the shortly after lamar posted the RC3 RPM. Yet the bug remained. This is not a complaint -- you guys have put alot of effort into the RPMs and they are very solid IMHO. But I would like the chance to look at the RPMM as it stands sometime before 7.1, as I have to customize the RPM yet again to distribute a working init script to our servers. Have you thought about a CVS store some place for the RPM files? -- Karl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Packaging 7.1.1
Bruce Momjian [EMAIL PROTECTED] writes: Perhaps src/rpm-tools/ or some such name. It is platform-specific, which would seem to vote for /contrib. Huh? By that logic, all of src/makefiles/, src/template/, and src/backend/port/, not to mention large chunks of the configure mechanism, belong in contrib. Shall we rip out all BSD support and move it to contrib? contrib has never been about platform dependency in my mind; it's about whether we consider something part of the project mainstream (in terms of code quality and our willingness to support it). RPM support isn't going away, and I'm willing to call it mainstream ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: New Linux xfs/reiser file systems
mlw wrote: [EMAIL PROTECTED]">Bruce Momjian wrote: Just put a note in the installation docs that the place where the databaseis initialised to should be on a non-Reiser, non-XFS mount...Sure, we can do that now. What do we do when these are the default filesystems for Linux? We can tell them to create other types of filesystems, but that is a pretty big hurdle. I wonder if it would beeasier to get reiser/xfs to make some modifications. I have looked at Reiser, and I don't think it is a file system suited for verylarge files, or applications such as postgres. The Linux crowd should lobbyagainst any such trend. It is ok for many moderately small files. ReiserFSwould be great for a cddb server, but poor for a database box.XFS is a real big file system project, I'd bet that there are file propertiesor management tools to tell it to leave directories and files alone. Theyshould have addressed that years ago.One last mention..Having better control over WHERE various files in a database are located canmake it easier to deal with these things. I think it's worth noting that Oracle has been petitioning the kernel developers for better raw device support: in other words, the ability to write directly to the hard disk and bypassing the filesystem all together. If the db is going to assume the responsibility of disk write verification it seems reasonable to assume you might want to investigate the raw disk i/o options. Telling your installers that a major performance gain is attainable by doing so might be a start in the opposite direction. I've monitored a lot of discussions and from what I can gather, postgresql does it's own set of journaling operations. I don't think that it's necessary for writes to be double journalled anyway. Again, just my two cents worth...
Re: [HACKERS] Packaging 7.1.1
Lamar Owen wrote: Tom Lane wrote: Seems like that stuff should be in CVS somewhere ... if only so someone else can pick up the ball if you get run over by a truck :-(. My wife appreciates the sentiment :-). As it stands now, better documentation distributed in the source RPM would help greatly. Everything necessary to do the build and maintain the package is in the source RPM as it stands now -- evidenced by the Linux distributors being able to take our source RPM, massage it to fit their particular system, and run with it. And I have a scad of history available in specfile form If it's just a small amount of code, I don't see what the harm would be in including it in the regular distro, though we should talk about just where it should go. If it's a large amount of code then perhaps a separate CVS project would be better, so that people who have no use for it don't end up pulling/downloading it. Not counting the JDBC jars, it's a hundred K or so uncompressed. The spec file is around 30k -- a small amount of code. contrib/rpm-dist? Seems to work. But I would prefer to look at how ither packaging schemes work and come up with something that might be consistent and useful across the board. For starters, I'd make contrib/package/ Then make an rpm subdirectory. Also a pkg directory for system that use pkgmk/pkginfo/pkgadd/pkgrm. If there's a way to may debain packages paly the game, put them in as well. Then, if someaone is packages for a variety of systems, there is alt least the possibility of some small amount of consistency. Extending things, you could have contrib/package/rpm/redhat for redhat-specific stuff. contrib/package/rpm/mandrake for mandrafke stuff. You get the idea. At that point, I could even imagine contrib/mkpackage script that di som OS detection, and built wahtever you wanted. That may be a little far off, but I think there is an important nuggent in here. Tarballs are great for developers, but they are not that great for system administrators with large installed bases. PostgreSQL builds are great for the portability. The next logical step might in fact be to extend some of that consistency to the package creation arena. -- Karl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PQgetvalue: ERROR!
Hello to all I need help in the following one for the following error PQGETVALUE: ERROR! tuple number 0 is out of range 0.. -1 Segment violation This happens when I make a pg_dump namebd namebd.dump I work posgresql 7.0.3 with mandrake 7.2 A thousand thank you Antonio Acuña ---(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
[HACKERS] strange table access error using PHP
hi... i'm configuring a web based e-commerce site called pg_market that uses PHP and postgresql and i keep coming up with this error: Warning: PostgreSQL query failed: ERROR: Relation 'order_cntry' does not exist in /home/users/h/haresh/public_html/pgmarket-1.2.0/lib/dblib.inc.php on line 84 Can't execute query SELECT name FROM order_cntry WHERE cntry_id = 974 This script cannot continue, terminating. The table was created wiht the same user that is used to access the databse in the script. I've tried everything from granting permissions to doing a dump and repost. Hope someone knows what might be the problem. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RE: postgres jdbc source code
G'day mate,
Sure, it's fine to mail me...Actually I haven't found the time to get
involved in the JDBC driver (it now does what I need it to do, and I'm
behind schedule with a project of mine ;-) so your involvement is very
welcome!
Probably the best thing is to access the CVS but on the postgres FTP site
they also put a snapshot every so often. The opt package contains the jdbc
driver ('interface' in postgrespeak):
ftp://download.sourceforge.net/pub/mirrors/postgresql/dev/postgresql-opt-sna
pshot.tar.gz
The thing I modified was that setXXX methods in PreparedStatement have a
decent (and JDBC spec abiding) null parameter check. (I want, and the JDBC
spec demands, that when I call setObject(null), or setDate(null) a NULL
value, in the resulting query, gets send to the database.
The reason why PostgreSql is such a pain in the ass for Java developers is
that they have to use transactions to use BLOB's (when updating them, ok,
but _also_ when selecting them!). The solution in my opinion would be to
have transactions around ResultSets in autocommit mode...
A similar solution would probably not be wise for PreparedStatements,
because ps's get created ahead of use (could result in lot's of lingering
open transactions)
I'll send you my driver as well (Note that you have to change the JDBC1
implementation as well, I haven't checked but I should have kept them in
sync)
Greetz from the Low Lands,
Jeroen
-Original Message-
From: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 04, 2001 02:32
To: [EMAIL PROTECTED]
Subject: postgres jdbc source code
Jeroen,
Hi, hope its ok to email you - I found your email in the
postgres mail
archives. I'm trying to find the postgres jdbc driver source
code - I've had
a look through jdbc.postgresql.org without any luck.. I know
there's a lot of
stuff not implemented and would like to get stuck into it
do you know
where I can find the source (or could I get it off you -
email attachment
would be fine).
cheers
dim
postgresql_jdbc.zip
---(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
[HACKERS] Re: [PATCHES] Cyrillic to UNICODE conversion
On Sun, 29 Apr 2001, Tatsuo Ishii wrote: From: Tatsuo Ishii [EMAIL PROTECTED] Subject: Re: [PATCHES] Cyrillic to UNICODE conversion X-Mailer: Mew version 1.94.2 on Emacs 20.7 / Mule 4.1 [iso-2022-jp] (^[$B0*^[(B) Thanks for the fixes. I have committed your patches and they should appear in 7.1.1. BTW, I have not added cp1251.txt cp866.txt koi8-r.txt, since they come from Unicode.org and are not permitted to re-distribute. It is not true for koi8-r.txt. At least one which is included into catdoc distribution I've made myself from RFC1483, and only afterward it has appear on unicode.org, and Chernov's KOI8 pages. But anyway, if anybody is able to get them from unicode.org, why bother. -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] what is the limit for string
hello Would you tell me how many characters we can have as a string field? Thanks a lot. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re: Learning from other open source databases
Bruce Momjian wrote: Here is a general call for people to review other open-source database software and report back on things PostgreSQL can learn from them. i don't know how much there is to learn since it doesn't seem as though development has been active in a few years, but there's also GNU SQL, which i had always hoped would develop into a useable system. http://www.ispras.ru/~kml/gss/index.html -tfo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] [Fwd: Re: [GENERAL] Unisersal B-Tree]
Hi all, Not sure if this is useful, but it might be good to file and reference somewhere. Regards and best wishes, Justin Clift Original Message Subject: Re: [GENERAL] Unisersal B-Tree Date: Mon, 30 Apr 2001 17:59:49 +0200 From: Jörg Schulz [EMAIL PROTECTED] Organization: Gebäudereinigung Schulz To: Justin Clift [EMAIL PROTECTED] References: 9cb797$642$[EMAIL PROTECTED] [EMAIL PROTECTED] 001b01c0d143$b9b33f40$0600a8c0@opal [EMAIL PROTECTED] Do you mind if I forward this email to the [EMAIL PROTECTED] mailing list? Of cource you can forward it. Maybe you can correct my bad english :-) Jörg Schulz - Original Message - From: Justin Clift [EMAIL PROTECTED] To: Jörg Schulz [EMAIL PROTECTED] Sent: Monday, April 30, 2001 2:55 PM Subject: Re: [GENERAL] Unisersal B-Tree Hi Jörg, I know we have indices and sub-indices, but this also sounds interesting. Do you mind if I forward this email to the [EMAIL PROTECTED] mailing list? Regards and best wishes, Justin Clift Jörg Schulz wrote: Hi Cliff, I've read an article in the german magazine c't (2001/1 P174) about this new astonishing method. After I realized that none of the major commercial databases implement this for now (afaik there is only one database on the market Transbase HyperCube www.transaction.de), I thought it would be a great chance for an open source database. I even think it's a must have feature in the near future. But what is it about? It can dramatically speed up queries that run over more than one index. Think of a query like this: select a,b,c from table where ( amin_a and amax_a ) and ( bmin_b and bmax_b ) In a conventional implementation you have two indexes on attributes a and b. But to run this query the database engine profits only from one index. It has to run through all the values of the other. This gets even worse if you use more constraints, and this scheme is typical for things like OLAP. With the new methode you add one UB-index that embraces a and b. And you run only once through this index. There are a number of papers available under mistral.in.tum.de that explain the basic concepts. Regards, Jörg Schulz - Original Message - From: Justin Clift [EMAIL PROTECTED] To: JXrg Schulz [EMAIL PROTECTED] Sent: Sunday, April 29, 2001 6:40 AM Subject: Re: [GENERAL] Unisersal B-Tree Hi Jörg, What advantages do they have? Regards and best wishes, Justin Clift JXrg Schulz wrote: Are there any plans to implement UB-Trees multidimensional indexes? Jörg Schulz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: Metaphone function attachment
Joel Burton writes: I think, as little things in contrib/, it's easy for people to miss these. With a project page, some discussion, etc. ( a place in contrib/), more people would be able to use these. Most of the extension functions and types in contrib should, in my mind, eventually be moved into the core. contrib is a nice place for things that we don't really know how/whether they work, but once we're confident about the quality we might as well offer it by default. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Packaging 7.1.1
Karl DeBisschop writes: PostgreSQL builds are great for the portability. The next logical step might in fact be to extend some of that consistency to the package creation arena. This would have been cool in 1996. We would have evolved a large number of different packages along with the build system. But it didn't happen this way and now most packages are sufficiently contorted in a number of ways because of vendor requirements, different ideas of how an operating system is supposed to work, self-inflicted incompatibilities, and a number of other reasons, including not least importantly the desire to have control over what ships in your system. All valid reasons, of course. If we can work at, and succeed at, resolving most of these oddities, then tracking packages in the source tree might prove worthwhile. But as long as we're still required to keep track what vendor has 'chkconfig' or what version of what distribution has broken CFLAGS, to list some trivial things, as long as the packages need to track anything but the development of PostgreSQL itself, this undertaking is going to become a problem. What would be worthwhile is setting up another cvs module so packages can be developed and released at their own pace. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] SAPDB Open Souce
Bruce Momjian schrieb: Hi guys, I've used the open source SAPDB and the performance is pretty damned impressive. However, 'open source' in application to it is somewhat deceptive, since you have to make it with SAP's proprietary build tools/environment. In my opinion, however, it would be worth closely auditing SAP DB to see what postgres can learn. I downloaded it. The directories are two characters in length, the files are numbers, and it is a mixture of C++, Python, and Pascal. Need I say more. :-) Well, I used to use PostgreSQL and Adabas / SAP-DB and after that it's pretty much clear now, that there're not so many arguments for PostgreSQL. Marten ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: help!
On Wed, 2 May 2001, Jeff Vainio wrote: Hello! I am a Technical Recruiter with MIS Consultants in Toronto Canada and I desperately need to find 2 POSTGRES DBA's for our Toronto client on a 3-4 month renewable contract, open $$$ based on experience. How do I go about finding these guys? Jeff -- The *-hackers list is not the appropriate forum for this. This is for discussion of developing PostgreSQl, and for kvetching about users, and things like that. :-) . There's a web page at techdocs.postgresql.org about people looking for PG consultants. . Call Great Bridge (greatbridge.com) or PostgreSQL Inc. (pgsql.com); they both are commercial companies providing PG support. They might be able to shake loose someone. PG Inc. is in Canada, so they might be a great bet. . A short message to pgsql-general would get everyone's attention. I'm not sure how people feel about these kind of notices, though -- so, keep it short, and obviously titled. help!, for instance, should become Seeking PostgreSQL DBAs in Toronto, Canada -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: what is the limit for string
On Tue, 1 May 2001, Rosie Sedghi wrote: hello Would you tell me how many characters we can have as a string field? Thanks a lot. Questions like this should be sent to pgsql-general or pgsql-novice. There is no string field. There are CHAR, VARCHAR, TEXT, and a few other unusual text-type fields. Look at the section on data types in the User's Manual for info. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Packaging 7.1.1
Karl DeBisschop wrote: Trond Eivind Glomsrød wrote: Rachit Siamwalla [EMAIL PROTECTED] writes: Also i never got a response on who actually packages those linux init scripts that appear in the RPM but not on the pgsql cvs tree. (i am also curious on why it is different, and how the RPM is built). Lamar Owen and I. Is the current snapshot available? The current snapshot is the 7.1-1 release as of this time. I have submitted fixes twice now for what I am fairly sure is a bug in the init script. At least one of the posts was the shortly after lamar posted the RC3 RPM. Yet the bug remained. I thought I integrated that one, but I must not have. My apologies. This is not a complaint -- you guys have put alot of effort into the RPMs and they are very solid IMHO. But I would like the chance to look at the RPMM as it stands sometime before 7.1, as I have to customize the RPM yet again to distribute a working init script to our servers. Mail me the initscript as fixed. Put a [HACKERS] in the usbject so it goes to the right folder. The extant 7.1-1 RPMset is the last build I have made. Have you thought about a CVS store some place for the RPM files? Yes. Discussion currently underway in HACKERS. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Packaging 7.1.1
Peter Eisentraut wrote: What would be worthwhile is setting up another cvs module so packages can be developed and released at their own pace. This is an _excellent_ point, and one I had thought of before but had forgotten. FWIW, I have a project set up at greatbridge.org -- I just have to get myself in gear and get it done. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: Metaphone function attachmenty
Yeah, but things do seem to languish there for quite a while. (soundex(), for instance, was in contrib when I first looked at PG). Also, some things are in contrib/ that seem a bit out of date (I think there was still some early RI stuff in there last time I went through it) I understand the need not to stuff PG full of *everything* -- and perhaps stuff like soundex(), metaphone(), etc., shouldn't go into the core *. But I think if we leave them in contrib/, after a while, it feels like there's an implied comment on the quality/soundness of the code. Would it work to have a different mechanism for distributing proven yet out-of-the-mainstream stuff, like soundex(), etc. * - soundex(), in particular, should go into the core, though. Many other DBs have it built in, so users could reasonably have the expectation that we should have it. Added to TODO: * Move some things from /contrib into main tree, like soundex -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: New Linux xfs/reiser file systems
Ken Hirsch [EMAIL PROTECTED] writes: I don't have a machine with XFS installed and it will be at least a week before I could get around to a build. Any volunteers? I think I could do that... any useful benchmarks to run? -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] New Linux xfs/reiser file systems
Hi, On Fri, May 04, 2001 at 01:49:54PM -0400, Bruce Momjian wrote: Performance doing what? XFS has known performance problems doing unlinks and truncates, but not synchronous IO. The user should be using fdatasync() for databases, btw, not fsync(). This is hugely helpful. In PostgreSQL 7.1, we do use fdatasync() by default it is available on a platform. Good --- fdatasync is defined in SingleUnix, so it's probably safe to probe for it and use it by default if it is there. The 2.2 Linux kernel does not have fdatasync implemented, but glibc will fall back to fsync if that's all that the kernel supports. 2.4 implements both with the required semantics. OK, that is something we found too, that fdatasync() was there on some platforms, but was really just an fsync(). I believe some HPUX platforms had that. OK, so they need a 2.4 kernel to properly test performance of Reiser/xfs with fdatasync(). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: New Linux xfs/reiser file systems
Michael Samuel wrote: Remember, general purpose file systems must do for files what Postgres is already doing for records. You will always have extra work. I am seriously thinking of trying a FAT32 as pg_xlog. I wonder if it will improve performance, or if there is just something fundamentally stupid about FAT32 that will make it worse? Well, for a starters, file permissions... Ext2 would kick arse over FAT32 for performance. OK, I'll bite. In a database environment where file creation is not such an issue, why would ext2 be faster? The FAT file system has, AFAIK, very little overhead for file writes. It simply writes the two FAT tables on file extension, and data. Depending on cluster size, there is probably even less happening there. I don't think that anyone is saying that FAT is the answer in a production environment, but maybe we can do a comparison of various file systems and see if any performance issues show up. I mentioned FAT only because I was thinking about how postgres would perform on a very simple file system, one which bypasses most of the normal stuff a good general purpose file system would do. While I was thinking this, it occurred to me that FAT was about he cheesiest simple file system one could find, short of a ram disk, and maybe we could use it to test the assumptions about performance impact of the file system on postgres. Just a thought. If you know of some reason why ext2 would perform better in the postgres environment, I would love to hear why, I'm very curious. ---(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] New Linux xfs/reiser file systems
[ Charset ISO-8859-1 unsupported, converting... ] I got some information from Stephen Tweedie on this - please keep him Cc: as he's not on this list Bruce Momjian [EMAIL PROTECTED] writes: I was talking to a Linux user yesterday, and he said that performance using the xfs file system is pretty bad. He believes it has to do with the fact that fsync() on log-based file systems requires more writes. Performance doing what? XFS has known performance problems doing unlinks and truncates, but not synchronous IO. The user should be using fdatasync() for databases, btw, not fsync(). This is hugely helpful. In PostgreSQL 7.1, we do use fdatasync() by default it is available on a platform. First, XFS, ext3 and reiserfs are *NOT* log-based filesystems. They are journaling filesystems. They have a log, but they are not log-based because they do not store data permanently in a log structure. Berkeley LFS, Sprite and Spiralog are log-based filesystems. Sorry, I get those mixed up. With a standard BSD/ext2 file system, WAL writes can stay on the same cylinder to perform fsync. Is that true of log-based file systems? Not true on ext2 or BSD. Write-aheads are _usually_ close to the inode, but not always. For true log-based filesystems, writes are always completely sequential, so the issue just goes away. For journaling filesystems, depending on the setup there may be a seek to the journal involved, but some journaling filesystems can use a separate disk for the journal so no seek is required. I know xfs and reiser are both log based. Do we need to be concerned about PostgreSQL performance on these file systems? I use BSD FFS with soft updates here, so it doesn't affect me. A database normally preallocates its data files and then performs most of its writes using update-in-place. In such cases, fsync() is almost always the wrong thing to be doing --- the data writes have changed nothing in the inode except for the timestamps, and there's no need to flush the timestamps to disk for every write. fdatasync() is designed for this --- if the only inode change is timestamps, fdatasync() will skip the seek to the inode and will only update the data. If any significant inode fields have been changed, then a full flush is done. We do pre-allocate our log file space in chunks to avoid inode/block index writes. Using fdatasync, most filesystems will incur no seeks for data flush, regardless of whether the filesystem is journaling or not. Thanks. That is a big help. I wonder if people reporting performance problems were using 7.0.3. We only added fdatasync() in 7.1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Re: New Linux xfs/reiser file systems
There have been multiple reports of poor PostgreSQL performance on Reiser and xfs. I don't have numbers, though. Frankly, I think we need xfs and reiser experts involved to figure out our options here. I've done some testing to see how Reiserfs performs vs ext2, and also various for various values of wal_sync_method while on a reiserfs partition. The attached graph shows the results. The y axis is transactions per second and the x axis is the transaction number. It was clear that, at least for my specific app, ext2 was significantly faster. The hardware I tested on has an Athalon 1 Ghz cpu and 512 MB ram. The harddrive is a 2 year old IDE drive. I'm running Red Hat 7 with all the latest updates, and a freshly compiled 2.4.2 kernel with the latest Reiserfs patch, and of course PostgreSQL 7.1. The transactions were run in a loop, 700 times per test, to insert sample data into 4 tables. I used a PHP script running on the same machine to do the inserts. I'd be happy to provide more detail or try a different variation if anyone is interested. This is hugely helpful. Yikes, look at those lines. It shows a few things. First, under Reiser, nosync, fsync, and fdatasync are pretty much the same. The big surprise here is that fsync doesn't seem to have any effect. Second surprise is that open fsync, which synces on every write rather than on end of transaction, was slower. I believe this should be slower if multiple WAL writes are being made in one transaction. fdatasync would sync just at end of transaction, while each WAL write would be synced by open fsync. And the largest surpise is that ext2 is faster, but not because of fsync, and almost double so. Keep in mind that WAL writes are no the only write happening. Though in 7.1 we don't flush the data blocks to disk, we do write to disk as the buffer cache fill up with dirty buffers. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 7.1.1
I see by the messages that 7.1.1 is in the final packaging. Anyone know when it will be released? -Tony ---(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] Re: New Linux xfs/reiser file systems
Joe Conway [EMAIL PROTECTED] wrote: I've done some testing to see how Reiserfs performs vs ext2, and also various for various values of wal_sync_method while on a reiserfs partition. The attached graph shows the results. The y axis is transactions per second and the x axis is the transaction number. It was clear that, at least for my specific app, ext2 was significantly faster. This is great, thanks a lot! Among other things it tells us, it appears that fsync() is not the problem on Reiserfs. I don't know the details of Reiserfs, but I think a lot of work has gone into optimizing it for very small files, so you can use the file system as a simple database for strings, a la Windows registry. I don't remember hearing about optimizing for large files and large block reads and writes. XFS, on the other hand, is used for very large files on SGI systems. I think the XFS and Reiserfs folks will be happy to look at the performance problem, but it would be very helpful for them to have a prepackaged benchmark (or two or three) to use. We should set up an FTP area to share them. Joe, can you contribute yours? Does anybody else have anything? Already, Trond Eivind Glomsrød [EMAIL PROTECTED] has volunteered to test on XFS. The easier we make it, the more help we'll get. Ken Hirsch ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [Fwd: Re: [GENERAL] Unisersal B-Tree]
... Think of a query like this: select a,b,c from table where ( amin_a and amax_a ) and ( bmin_b and bmax_b ) In a conventional implementation you have two indexes on attributes a and b. But to run this query the database engine profits only from one index. It has to run through all the values of the other. This gets even worse if you use more constraints, and this scheme is typical for things like OLAP. With the new methode you add one UB-index that embraces a and b. And you run only once through this index. And this is different from a multicolumn btree index how? I looked at the referenced website when this message first went by, and was unhappy at the apparently proprietary nature of the technology (not to mention the excessive hype ratio). I lost interest ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PQgetvalue: ERROR!
Antonio Jose Acuña Jimenez [EMAIL PROTECTED] writes: I need help in the following one for the following error PQGETVALUE: ERROR! tuple number 0 is out of range 0.. -1 Segment violation Pre-7.1 versions of pg_dump are not very robust about situations like functions whose owner doesn't exist anymore, tables that refer to nonexistent datatypes, that sort of thing. The above is not enough to narrow down the problem, however. Try starting the postmaster with -d2 so that you can get a log of pg_dump's queries; then look to see what's the last query processed before it crashes. That should let you figure out which database item has the dangling reference. 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
[HACKERS] Postmaster refuses to start
My postmaster is refusing to start. I don't know what's wrong. If anyone has pointers/tips/whatever, please tell me. One of my backup scripts was wrong so I don't have current backups (that's what you get for trusting other people to do your backups). Any way to recover my databases? The oddities started yesterday. Everytime I'd call a PL/pgSQL function, the backend would die. The function was working perfectly up to that point so I know there's nothing wrong with it. I am running PG 7.1 from the Debian packages. Here's what my log says: [tons of these] pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection Server process (pid 3599) exited with status 11 at Thu May 3 10:32:47 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 10:32:55 2001 Reinitializing shared memory and semaphores Server process (pid 3616) exited with status 11 at Thu May 3 10:35:21 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 10:35:21 2001 Reinitializing shared memory and semaphores pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection Server process (pid 5115) exited with status 11 at Thu May 3 16:03:09 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:03:12 2001 Reinitializing shared memory and semaphores Server process (pid 5118) exited with status 11 at Thu May 3 16:03:42 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:03:42 2001 Reinitializing shared memory and semaphores Server process (pid 5122) exited with status 11 at Thu May 3 16:04:00 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:04:00 2001 Reinitializing shared memory and semaphores Server process (pid 5158) exited with status 11 at Thu May 3 16:08:43 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:08:45 2001 Reinitializing shared memory and semaphores Server process (pid 5176) exited with status 11 at Thu May 3 16:15:32 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:15:33 2001 Reinitializing shared memory and semaphores The Data Base System is starting up The Data Base System is starting up pq_recvbuf: unexpected EOF on client connection Smart Shutdown request at Thu May 3 16:17:59 2001 Server process (pid 5271) exited with status 11 at Thu May 3 16:21:35 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:21:36 2001 Reinitializing shared memory and semaphores Server process (pid 5288) exited with status 11 at Thu May 3 16:26:47 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:26:49 2001 Reinitializing shared memory and semaphores Server process (pid 5293) exited with status 11 at Thu May 3 16:28:32 2001 Terminating any active server processes... Server processes were terminated at Thu May 3 16:28:32 2001 Reinitializing shared memory and semaphores The Data Base System is starting up Server process (pid 7780) exited with status 512 at Fri May 4 04:06:34 2001 Terminating any active server processes... Server processes were terminated at Fri May 4 04:06:34 2001 Reinitializing shared memory and semaphores /usr/lib/postgresql/bin/postmaster: Startup proc 7781 exited with status 512 - abort /usr/lib/postgresql/bin/postmaster: Startup proc 9347 exited with status 512 - abort /usr/lib/postgresql/bin/postmaster: Startup proc 9364 exited with status 512 - abort /usr/lib/postgresql/bin/postmaster: Startup proc 9380 exited with status 512 - abort These last lines are me trying to restart the postmaster via pg_ctl with debugging set to 5. Any help is very much appreciated. -Roberto -- +| http://fslc.usu.edu USU Free Software GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: New Linux xfs/reiser file systems
I think the XFS and Reiserfs folks will be happy to look at the performance problem, but it would be very helpful for them to have a prepackaged benchmark (or two or three) to use. We should set up an FTP area to share them. Joe, can you contribute yours? Does anybody else have anything? I don't mind contributing the script and schema that I used, but one thing I failed to mention in my first post is that the first thing the script does is open connections to 256 databases (all on this same machine), and the transactions are relatively evenly dispersed among the 256 connections. The test was originally written to try out an idea to allow scalability by partitioning the data into seperate databases (which could eventually each live on its own server). If you are interested I can modify the test to use only one database and rerun the same tests this weekend. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] 7.1.1
I see by the messages that 7.1.1 is in the final packaging. Anyone know when it will be released? Only Marc knows. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postmaster refuses to start
Roberto Mello [EMAIL PROTECTED] writes: My postmaster is refusing to start. I don't know what's wrong. If anyone has pointers/tips/whatever, please tell me. Perhaps a gdb backtrace from one of the core files would yield clues. 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] IANA registration
Did we get anywhere with this? PostgreSQL typically uses port 5432 for client-server communications. It would be a good idea to register this with IANA. This will help to avoid a clash with other services that might try to use the port. DB2, Interbase, MS SQL, MySQL, Oracle, Sybase, etc. are already registered. Might someone with a reasonable grasp of the low level messages in PostgreSQL care to submit a registration? http://www.iana.org/ http://www.iana.org/cgi-bin/usr-port-number.pl http://www.isi.edu/in-notes/iana/assignments/port-numbers -- Pete Forman -./\.- Disclaimer: This post is originated WesternGeco -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- opinion of Schlumberger, Baker http://www.crosswinds.net/~petef -./\.- Hughes or their divisions. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.1.1
On Fri, 4 May 2001, Bruce Momjian wrote: I see by the messages that 7.1.1 is in the final packaging. Anyone know when it will be released? Only Marc knows. :-) Tomorrow aft ... sorry, got tied up with a client finishing his server move to v7.1 this afternoon, and we hit problems with a programmer who didn't realize that telling the scripts to connect to a specific host was a good idea :) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.1.1
OK, I have updated the file dates for a release tomorrow. On Fri, 4 May 2001, Bruce Momjian wrote: I see by the messages that 7.1.1 is in the final packaging. Anyone know when it will be released? Only Marc knows. :-) Tomorrow aft ... sorry, got tied up with a client finishing his server move to v7.1 this afternoon, and we hit problems with a programmer who didn't realize that telling the scripts to connect to a specific host was a good idea :) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] 7.1.1
thnks :)
On Fri, 4 May 2001, Bruce Momjian wrote:
OK, I have updated the file dates for a release tomorrow.
On Fri, 4 May 2001, Bruce Momjian wrote:
I see by the messages that 7.1.1 is in the final packaging. Anyone know
when it will be released?
Only Marc knows. :-)
Tomorrow aft ... sorry, got tied up with a client finishing his server
move to v7.1 this afternoon, and we hit problems with a programmer who
didn't realize that telling the scripts to connect to a specific host was
a good idea :)
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
