Re: [GENERAL] Libpq Asynchronous Command Processing
=?iso-8859-1?Q?Alonso_Garc=EDa_=2C_Bruno_Elier?= wrote: > And the problems I am finding are the following: > ->Queries from the client to the new DB server take a lot of time. > ->Queries from the client to the old DB server are fast. > ->The same query takes 150 secs in one case an 1 sec in the other case. With that analysis, I'd be betting against it being a client problem. (If you wanted, you might confirm that by pointing an old client at the new server.) I'd look into how the data was loaded into the new server and how the database is configured: number of buffers, indexes, and whether analyze has been run or not. It would be strange indeed (possible, but very strange) to find such a slowdown between 7.x and 8.x when the team is preparing to push 9.0 out the door. Surely it would have been known before; therefore it's a practical certatinty that there is something different about the configuration of your two servers. Giles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hi, trying to compile postgres 8.3.11
erobles wrote: >> Do you need the Pg server to run on SCO OpenServer? > > Yes, i need it :-P Of course it's none of my business, but whenever I had a supplier insisting on some idosyncratic or obsolete OS I started thinking hard about replacing the supplier and their product. Even worse if the supplier is gone and you're using a totally unsupported product. All IMHO of course, and I've supported some peculiar setups when business requirements made alternatives impossible. Good luck, Giles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to run createlang (or psql for that matter)
Dave Page wrote: > The account doesn't have a password by default as it's a service > account and you shouldn't need to use it interactively. > > If you really want to though, just set a password: > > gator:~ dpage$ sudo passwd postgres > Changing password for postgres. > New password: > Retype new password: > gator:~ dpage$ su - postgres > Password: > gator:~ postgres$ Or even without a password, from an administrative account which can use sudo: $ sudo -i -u postgres Password: sapphire:~ postgres$ id uid=770(postgres) gid=770(postgres) groups=770(postgres),402(com.apple.sharepoint.group.1),61(localaccounts),12(everyone) Regards, Giles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database viewpoint of subject - Sending e-mails from database table with Cronjob
Andre Lopes wrote: > My doubt is... The CronJob runs every 10 minutes, but If I have 100.000 > e-mails to send the script will not be able to send all the 100.000 e-mails > in 10 minutes. More generally, your question is how to deal with a cron job that is intended to run every ten minutes, but which you don't want to run more than one copy of (i.e. this has little or nothing to do with PostgreSQL, really). My usual solution is twofold: 1. have the cron job take a lock file as the first thing it does: this ensures only one job can run at a time Something like FreeBSD's lockf(1) is good for this, and isn't hard to port or write a similar utility for any OS I've had to use: http://www.freebsd.org/cgi/man.cgi?query=lockf&apropos=0&sektion=1&manpath=FreeBSD+8.0-RELEASE&format=html 2. include in the job a 'dead man switch' that terminates the job if it runs for "too long", whatever "too long" is in your context. Obviously, 10 minutes is possible; 10 hours is probably not. Making sure client applications terminate (be they cron jobs, CGI scripts, or anything else) stops all your database connections being tied up by hung scripts, to bring the discussion slightly back to PostgreSQL). > How can I deal with this problem? There is no problem to have multiple > CronJobs runing in background? Multiple cron jobs in general, no. Multiple instances of a single cron job depends on the cron job. As another poster wrote, I do hope this isn't for spam, as you say it isn't, but the basics I'd follow are #1 and #2 above, and let the mail system handle both the queue and the allowable amount of parallel outbound SMTP connections. That's what mail software is for. Of course, I'd _definitely_ not use any such thing for spamming Regards, Giles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Perl interface
> I would like to extract data from my postgreSQL7.0 database and present > them on a web-page. I want to use CGI scripts written in Perl. How do I > connect to and query the DB from Perl? A reference to a tutorial or some > sort of documentation would be highly appreciated! :)) The choices for perl/PostgresQL interfacing are DBI and DBD::Pg which you can find on CPAN (http://www.perl.com/CPAN), and the Pg module which you can find in src/interfaces/perl5 in the postgresql distribution. The Pg module is closer to libpq. The DBI interface is more portable and has the benefit of being documented in "Programming the Perl DBI" by Alligator Descartes and Tim Bunce. Regards, Giles
Re: [GENERAL] postmaster logs
> I would like to direst the postmaster log output to a file. At the > same time, because it will grow indefinitely I would like to be able > to rotate the log using newsyslog (as I do for other daemons). > > Is there a mechanism for doing this? There was discussion of this recently, with some degree of consensus for the introduction of a (possibly optional) logging program. The Apache "rotatelogs" program was offered, but some of us didn't find it quite paranoid enough about error handling (filesystem full, file table full, etc). I'm writing a more careful version of rotatelogs and intend that it also be capable of being controlled by newsyslog. When it's done I'll offer it for inclusion, probably by posting it to -patches since it will be small. Real Soon Now, but no promises of a date. As ever Real Life places demands from time to time that must be accomodated. :-) Regards, Giles
Re: [GENERAL] optimization by removing the file system layer?
> I think that the Un*x filesystem is one of the reasons that large > database vendors rather use raw devices, than filesystem storage > files. This used to be the preference, back in the late 80s and possibly early 90s. I'm seeing a preference toward using the filesystem now, possibly with some sort of async I/O and co-operation from the OS filesystem about interactions with the filesystem cache. Performance preferences don't stand still. The hardware changes, the software changes, the volume of data changes, and different solutions become preferable. > Using a raw device on the disk gives them the possibility to have > complete control over their files, indices and objects without being > bothered by the operating system. > > This speeds up things in several ways : > - the least possible OS intervention Not that this is especially useful, necessarily. If the "raw" device is in fact managed by a logical volume manager doing mirroring onto some sort of storage array there is still plenty of OS code involved. The cost of using a filesystem in addition may not be much if anything and of course a filesystem is considerably more flexible to administer (backup, move, change size, check integrity, etc.) > - choose block sizes according to applications > - reducing fragmentation > - packing data in nearby cilinders ... but when this storage area is spread over multiple mechanisms in a smart storage array with write caching, you've no idea what is where anyway. Better to let the hardware or at least the OS manage this; there are so many levels of caching between a database and the magnetic media that working hard to influence layout is almost certainly a waste of time. Kirk McKusick tells a lovely story that once upon a time it used to be sensible to check some registers on a particular disk controller to find out where the heads were when scheduling I/O. Needless to say, that is history now! There's a considerable cost in complexity and code in using "raw" storage too, and it's not a one off cost: as the technologies change, the "fast" way to do things will change and the code will have to be updated to match. Better to leave this to the OS vendor where possible, and take advantage of the tuning they do. > - Anyone other ideas -> the sky is the limit here > It also aids portability, at least on platforms that have an > equivalent of a raw device. I don't understand that claim. Not much is portable about raw devices, and they're typically not nearlly as well documented as the filesystem interfaces. > It is also independent of the standard implemented Un*x filesystems, > for which you will have to pay extra if you want to take extra > measures against power loss. Rather, it is worse. With a Unix filesystem you get quite defined semantics about what is written when. > The problem with e.g. e2fs, is that it is not robust enough if a CPU > fails. ext2fs doesn't even claim to have Unix filesystem semantics. Regards, Giles
[GENERAL] Re: Industrial-Strength Logging
--- Blind-Carbon-Copy To: [EMAIL PROTECTED] Subject: Re: Industrial-Strength Logging In-reply-to: <[EMAIL PROTECTED]> Date: Sat, 03 Jun 2000 22:59:34 +1000 Message-ID: <[EMAIL PROTECTED]> From: Giles Lean <[EMAIL PROTECTED]> On Sat, 3 Jun 2000 01:48:33 +0200 (CEST) Peter Eisentraut wrote: > Yeah, let's have another logging discussion... :) Mmm, seems popular. There was a mention on -ports and -general a couple of weeks ago, and here we are (were) on -patches. I'm moving this discussion to -hackers (hope that's a good choice) since that is where Tim Holloway's proposals were discussed late last year. A start point I found in the archives for Tim's proposal is: http://www.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00747.html I'm not proposing anything that advanced. In particular, I'm not discussing the -content- of log messages at all. For now it would be nice to see the logging mechanism improved; changing or improving the content can be another project. I don't discuss the current logging implementation except to note that the backend postgres processes' logging depends on whether the process is running under postmaster or not, has a controlling terminal or not, whether a -o option was provided, and whether postgres was compiled to use syslog. Maybe that functionality can be simplified a bit ... ;-) One more thing I don't discuss is how the debug log level is set. Certainly something more sophisticated and dynamically variable than the current command line method would be nice, but that too can be a discussion for another day; it isn't much related to -how- the error messages are tucked away. Typical logging methods === (a)(i) write to standard error with redirection to a file Pro: - what the code (mostly) does currently - very easy to set up, just redirect standard error at startup - efficient and low overhead Con: - can't rotate log files - problematic when there is an I/O error or the filesystem the log file is on fills up (a)(ii) write to standard error, with standard error piped to another process Pro: - administrator chooses between (i) and (ii) and can change this via shutdown and restart, no recompilation needed - no code changes to backend programs - clean separation of functionality - choice of backend logging programs o Bernstein's logtools o Apache's rotatelogs o swatch o logsurfer o ... Con: - backend can block if the logging process is not reading log messages fast enough (can the backends generate enough data for this to be a problem in practice?) - reliability of message logging is dependent on the log process - log messages can be lost if the log process aborts, or is not started (solution: make portmaster responsible for starting and restartin the log process) (b) write to named log file(s) One way to allow rotation of log files is for the backend processes to know what log files they write to, and to have them open them directly without shell redirection. There is some support for this with the postgres -o option, but no support for rotating these files that I have seen so far. In the simplest case, the backend processes open the log file at when they start and close it when they exit. This allows rotation of the log file by moving it and waiting for all the currently running backend processes to finish. Pro: - relatively simple code change - still efficient and low overhead Con: - backend processes can run for some time, and postmaster runs indefinitely, so at least postmaster needs to know about log file rotation - doesn't help much for I/O errors or full filesystem To address these limitations some applications open their log file for each message and then close it afterward: Pro: - nothing holds the log file open for long - still efficient and low overhead for the actual writing the log file Con: - all error logging has to be via a log routine. This would be elog(), but there is some use of fprintf(stderr, ...) around the place that would want to be changed - there will be some efficiency hit for the open() and close() calls. This won't be -too- bad since the operating system's inode cache (or local equivalent) should contain an entry for the log file, but it is still two more system calls. Another way to handle rotation with long running processes is to signal them to re-open their log file, like syslogd is managed: Pro: - it's a solution Con: - more code in the backend processes - more communication with the backend processes - more complication (c) log via some logging facility such as syslo
Re: [GENERAL] Migrating from mysql.
On Wed, 24 May 2000 14:26:32 -0500 "Ross J. Reedstrom" wrote: > Actually, it's "\d tablename". The rest is right, though. Teach me to try to tidy things up before posting won't it? Thanks! Giles (sigh, time for coffee)
Re: [GENERAL] Migrating from mysql.
On Mon, 15 May 2000 23:04:48 +0100 Joe Karthauser wrote: > And last but not least I'm used to using the 'desc tablename' sql command > to show the structure of a table within MySQL. How do I do the same in > PostgreSQL. In psql "\i tablename". Check out \? or the documentation for all the different backslash commands. You might want \z for access permissions as well. Regards, Giles
Re: Logging (was Re: [GENERAL] PostgreSQL 7.0-2 RPMset released.)
On Mon, 22 May 2000 00:19:45 -0400 Tom Lane wrote: > There needn't be a lot of code involved, we just need a > well-thought-out spec for how it should work. Comments anyone? I run postmaster under Dan Bernstein's "daemontools", which include logging facilities: http://cr.yp.to/daemontools.html The summary of this setup is that postmaster runs in the forground writing error messages to standard error, and standard error is a pipe to another process. The second process is responsible for selecting messages to write, writing them, and rotating the log file. More traditional Unix solutions would involve teaching postmaster what the name of its log file is, and to reopen it on receipt of some signal. Usually SIGHUP is used since SIGHUP is unlikely to be useful to a daemon running in the background. There are issues for logging errors that many applications handle badly. What happens when: o there is an I/O error writing to a log file? o the log file is at maximum size? o the filesystem the log file is in is full? o a write to a log file blocks? To take a not random example, syslogd is OK for log file rotation but makes a mess and a muddle of things otherwise including the points I list. Regards, Giles
