Re: [ADMIN] benchmarks with pgbench
On Mon, 2005-01-24 at 13:19, Kavan, Dan (IMS) wrote: > I hate to admit this publically, but I've been reading my results > backwards. > > I was getting 100 tps on Solaris - postgres 64 bit and 300 tps on SUSE > postgres both x86-64. > So, 300 is better than 100 right? I was reading it backwards. > I was thinking 300 was the actual speed to process a certain amount of > transactions, but actually the x86-64 system is performing better than > all, not worse. > > ~Dj > > > > Yes, they are both running on the same hardware - NAS. Yep, 300 is better than 100. Glad to hear it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] benchmarks with pgbench
I hate to admit this publically, but I've been reading my results backwards. I was getting 100 tps on Solaris - postgres 64 bit and 300 tps on SUSE postgres both x86-64. So, 300 is better than 100 right? I was reading it backwards. I was thinking 300 was the actual speed to process a certain amount of transactions, but actually the x86-64 system is performing better than all, not worse. ~Dj Yes, they are both running on the same hardware - NAS. On Mon, 2005-01-24 at 10:48, Kavan, Dan (IMS) wrote: > Hi Guys, > I''ve been running pgbench tests for a while. I have one server set > up to run pgbench tpc tests (7.x). A new server that I just > configured with SUSE and 8.0.0 just gets killed even though it has the > same memory 8 GB and it's a x86-64 box. The other one is Solaris full > 64-bit. That seems reasonable, but we thought postgres would run > better on a linux box than solaris. Also, the x86-64 box does much > worse than a 32-bit linux box with mandrake and a lot less RAM. I've > restarted postgres with many different postgresql.conf configs and > nothing seems to make much of a difference to pgbench. Has anyone > else experienced slower performance on 64-bit linux as compared to > 32-bit linux? pgbench is notorious for providing poor measure of a database's performance under real world load. Are you sure your Solaris and Linux boxes are both running on SCSI hard drives (IDE drives are well known for not obeying fsync() calls, but simply saying "yep, synced that data" when in fact they haven't. So, if you Linux box is set to both fsync properly AND is writing access time to each file, it may be quite a bit slower than a Solaris box if that box is writing to IDE drives, has fsync turned off, and / or has access time writing disabled. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] pg_dump --data-only problem with PgSQL 8.0
On 2005-01-24 13:46-0500, Tom Lane wrote: > Jani Averbach <[EMAIL PROTECTED]> writes: > > > This will fail with lots of these kinds of errors: > > ERROR: insert or update on table "mytable" > > violates foreign key constraint "mytable_myattr_fkey" > > Try it with --disable-triggers. > Thanks a lot! That fixed it. > > Or have I found an ordering bug with pg_dump > > when you are doing "--data-only" dumps? > > No. pg_dump can't guarantee a safe order for loading data when there > are pre-existing foreign key constraints in place (since the constraints > could be circular, and it wouldn't necessarily know what they are anyway). > So it doesn't try. You have to use --disable-triggers instead. I was wondering because data-only load from a dump generated with 7.2.5 succeeded, but data only load with dump genererated from 8.0.0 didn't. Any way, it works now, thank you for your help! BR, Jani -- Jani Averbach ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Trouble Escaping Quotes
"Haron, Charles" <[EMAIL PROTECTED]> writes: > *** Information From /data/serverlog *** > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: WARNING: > Message from PostgreSQL backend: > The Postmaster has informed me that some other backend > died abnormally and possibly corrupted shared memory. Hmm. That moves it out of the realm of "user error", which is what I think we'd all been assuming, and into the realm of "server bug". Can you supply a self-contained test case that causes this? Or at least a debugger back trace from the point of the core dump? regards, tom lane ---(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: [ADMIN] pg_dump --data-only problem with PgSQL 8.0
Jani Averbach <[EMAIL PROTECTED]> writes: > the following won't work: > 4) Dump only data from just created 8.0 database: >pg_dump \ > "--data-only" \ > "--column-inserts" \ > "--use-set-session-authorization" \ > new_db > new_db.data-only.dump > 5) Re-Create the new_db: >dropdb new_db; createdb new_db >CreateDBSchema.sh new_db > 6) Try to reload data which was dumped from new ver 8.0 database: > psql new_db < new_db.data-only.dump > This will fail with lots of these kinds of errors: > ERROR: insert or update on table "mytable" > violates foreign key constraint "mytable_myattr_fkey" Try it with --disable-triggers. > Or have I found an ordering bug with pg_dump > when you are doing "--data-only" dumps? No. pg_dump can't guarantee a safe order for loading data when there are pre-existing foreign key constraints in place (since the constraints could be circular, and it wouldn't necessarily know what they are anyway). So it doesn't try. You have to use --disable-triggers instead. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] pg_dump --data-only problem with PgSQL 8.0
On Mon, Jan 24, 2005 at 11:16:56AM -0700, Jani Averbach wrote: > What I am doing wrong? Or have I found an ordering bug with pg_dump > when you are doing "--data-only" dumps? In a simple test I see the same ordering (alphabetical?) whether I use --data-only or not. The schema+data dump succeeds because the dump doesn't add foreign key constraints until after all the data is loaded. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] benchmarks with pgbench
Yes, they are both running on the same hardware - NAS. On Mon, 2005-01-24 at 10:48, Kavan, Dan (IMS) wrote: > Hi Guys, > I''ve been running pgbench tests for a while. I have one server set > up to run pgbench tpc tests (7.x). A new server that I just > configured with SUSE and 8.0.0 just gets killed even though it has the > same memory 8 GB and it's a x86-64 box. The other one is Solaris full > 64-bit. That seems reasonable, but we thought postgres would run > better on a linux box than solaris. Also, the x86-64 box does much > worse than a 32-bit linux box with mandrake and a lot less RAM. I've > restarted postgres with many different postgresql.conf configs and > nothing seems to make much of a difference to pgbench. Has anyone > else experienced slower performance on 64-bit linux as compared to > 32-bit linux? pgbench is notorious for providing poor measure of a database's performance under real world load. Are you sure your Solaris and Linux boxes are both running on SCSI hard drives (IDE drives are well known for not obeying fsync() calls, but simply saying "yep, synced that data" when in fact they haven't. So, if you Linux box is set to both fsync properly AND is writing access time to each file, it may be quite a bit slower than a Solaris box if that box is writing to IDE drives, has fsync turned off, and / or has access time writing disabled. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Postgres 8.0 Backups
Scott Marlowe <[EMAIL PROTECTED]> writes: > On Mon, 2005-01-24 at 12:05, Tom Lane wrote: >> In the context of online backup operations, that advice isn't relevant >> anymore ... > Really, is this an 8.0 thing then, that I can make file system backups > and expect them to be coherent, or did I misunderhear what you meant and > this has to do with some other issue I'm not getting? No, it means that when doing PITR stuff you don't actually care that your base backup isn't consistent --- you expect WAL replay to fix it. See http://www.postgresql.org/docs/8.0/static/backup-online.html regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Postgres 8.0 Backups
On Mon, 2005-01-24 at 12:05, Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > On Mon, 2005-01-24 at 10:47, Pallav Kalva wrote: > >> I am working on a backup script for Postgres 8.0 online backups and > >> since i have to copy the whole pgdata directory , i am wondering after i > >> copy the pgdata directory can I run gzip or tar on the data directory ? > > > Generally speaking, file system level backups are not the best way to > > backup postgresql, since they require either shutting down the server or > > using a snapshot file system to get a coherent backup. > > In the context of online backup operations, that advice isn't relevant > anymore ... Really, is this an 8.0 thing then, that I can make file system backups and expect them to be coherent, or did I misunderhear what you meant and this has to do with some other issue I'm not getting? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] pg_dump --data-only problem with PgSQL 8.0
Hello, I have a following problem: In short: I can't reload pg_dump --data-only dump (ver. 8.0) back to the database, because the loading will violate ref. integrity. The long story: We have a PgSQL 7.2.5 database, and we like to bring only the data to the PgSQL 8.0 system. So I did following: 1) dump 7.2.5 database with: pg_dump \ "--data-only" \ "--column-inserts" \ "--use-set-session-authorization" \ old_db > old_db.data-only.dump 2) Create a schema on the new PgSQL system: CreateDBSchema.sh new_db 3) Load the old data to the new system: psql new_db < old_db.data-only.dump So far, so good, the loading succeeded. Now If continue, the following won't work: 4) Dump only data from just created 8.0 database: pg_dump \ "--data-only" \ "--column-inserts" \ "--use-set-session-authorization" \ new_db > new_db.data-only.dump 5) Re-Create the new_db: dropdb new_db; createdb new_db CreateDBSchema.sh new_db 6) Try to reload data which was dumped from new ver 8.0 database: psql new_db < new_db.data-only.dump This will fail with lots of these kinds of errors: ERROR: insert or update on table "mytable" violates foreign key constraint "mytable_myattr_fkey" However, If I take a data+schema dump after step #3, 7) Dump data and schema from new database: pg_dump "--use-set-session-authorization" \ new_db > new_db.dump 8) And load that, it will succeed: dropdb new_db; createdb new_db psql new_db < new_db.dump What I am doing wrong? Or have I found an ordering bug with pg_dump when you are doing "--data-only" dumps? These two databases are living in different machines, so there can't be any version mismatch between pg_dump, psql and databases. Thanks for any help, Jani -- Jani Averbach ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Postgres 8.0 Backups
Scott Marlowe <[EMAIL PROTECTED]> writes: > On Mon, 2005-01-24 at 10:47, Pallav Kalva wrote: >> I am working on a backup script for Postgres 8.0 online backups and >> since i have to copy the whole pgdata directory , i am wondering after i >> copy the pgdata directory can I run gzip or tar on the data directory ? > Generally speaking, file system level backups are not the best way to > backup postgresql, since they require either shutting down the server or > using a snapshot file system to get a coherent backup. In the context of online backup operations, that advice isn't relevant anymore ... Personally I would do "tar cfz pgdata.tar.gz $PGDATA" or equivalent, rather than making an explicit copy of the directory tree first. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] benchmarks with pgbench
On Mon, 2005-01-24 at 10:48, Kavan, Dan (IMS) wrote: > Hi Guys, > I''ve been running pgbench tests for a while. I have one server set up > to run pgbench tpc tests (7.x). A new server that I just configured > with SUSE and 8.0.0 just gets killed even though it has the same memory > 8 GB and it's a x86-64 box. The other one is Solaris full 64-bit. That > seems reasonable, but we thought postgres would run better on a linux > box than solaris. Also, the x86-64 box does much worse than a 32-bit > linux box with mandrake and a lot less RAM. I've restarted postgres > with many different postgresql.conf configs and nothing seems to make > much of a difference to pgbench. Has anyone else experienced slower > performance on 64-bit linux as compared to 32-bit linux? pgbench is notorious for providing poor measure of a database's performance under real world load. Are you sure your Solaris and Linux boxes are both running on SCSI hard drives (IDE drives are well known for not obeying fsync() calls, but simply saying "yep, synced that data" when in fact they haven't. So, if you Linux box is set to both fsync properly AND is writing access time to each file, it may be quite a bit slower than a Solaris box if that box is writing to IDE drives, has fsync turned off, and / or has access time writing disabled. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Postgres 8.0 Backups
On Mon, 2005-01-24 at 10:47, Pallav Kalva wrote: > Hi Everybody, > > I am working on a backup script for Postgres 8.0 online backups and > since i have to copy the whole pgdata directory , i am wondering after i > copy the pgdata directory can I run gzip or tar on the data directory ? > is it safe to do gzip or tar and I can safely restore the backups later ? Yes, however, you may have some issues to deal with. Generally speaking, file system level backups are not the best way to backup postgresql, since they require either shutting down the server or using a snapshot file system to get a coherent backup. File system backups taken by normal copy / tar during database operation may result in backups that don't work when you need them to. pg_dump is generally the better way to back things up, and with the right switches will create a Z compressed tar file automagically for you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] benchmarks with pgbench
Hi Guys, I''ve been running pgbench tests for a while. I have one server set up to run pgbench tpc tests (7.x). A new server that I just configured with SUSE and 8.0.0 just gets killed even though it has the same memory 8 GB and it's a x86-64 box. The other one is Solaris full 64-bit. That seems reasonable, but we thought postgres would run better on a linux box than solaris. Also, the x86-64 box does much worse than a 32-bit linux box with mandrake and a lot less RAM. I've restarted postgres with many different postgresql.conf configs and nothing seems to make much of a difference to pgbench. Has anyone else experienced slower performance on 64-bit linux as compared to 32-bit linux? D.J. ---(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
[ADMIN] Postgres 8.0 Backups
Hi Everybody, I am working on a backup script for Postgres 8.0 online backups and since i have to copy the whole pgdata directory , i am wondering after i copy the pgdata directory can I run gzip or tar on the data directory ? is it safe to do gzip or tar and I can safely restore the backups later ? Thanks in advance, Pallav ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Trouble Escaping Quotes
Below is logging information from a client-side SQL monitor, as well as what
I found in /data/serverlog:
I this particular instance the offending quote is in the problem_desc field.
The value of problem_desc being "Bob's Problem" (double quotes excluded).
*** Client-Side SQL monitor data ***
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET DATESTYLE TO ISO;BEGIN;
insert into "rmas"
("id", "company_id", "repair_types_id", "printer_models_id",
"serial_number", "problem_desc")
values
(1240916, 126, 3, 595, '45845384568', 'Bob''s Problem')
Command executed sucessfully
1 row(s) affected
Commiting transaction
END;
SELECT send_rma(rma_info) FROM rma_info WHERE rma = 1240916
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
*** Information From /data/serverlog ***
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING:
Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
Failed.
> -Original Message-
> From: Richard Poole [mailto:[EMAIL PROTECTED]
> Sent: Sunday, January 23, 2005 11:59 AM
> To: pgsql-admin@postgresql.org
> Cc: Haron, Charles
> Subject: Re: Trouble Escaping Quotes
>
> On Fri, Jan 21, 2005 at 08:43:00AM -0700, Haron, Charles wrote:
> > Yes, but how do you escape the quote when the value of the string
> > isn't know until the function is run?
> >
> > If $rma_data->{\'company_name\'} is returning Bob's Fixit
> at run time,
> > how do escape the quote BEFORE Perl interprets the string and
> > generates an error?
>
> Your quote-escaping looks fine to me; \' is just as good as
> '', although less standard. I can't reproduce your problem
> here; your function works for me under 8.0.0 and perl 5.8.6 .
> What's your error message when you call it? Are you sure that
> your problem isn't happening earlier, when you put the data
> into the database in the first place?
>
>
> Richard
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] postgresql 7.4.6 - FATAL Database Startup Message
On Fri, 21 Jan 2005, Rao Kumar wrote: > Database System startup message > > In postgres version 7.4.6, I notice that sometimes when the database starts > up, it outputs a log message such as "2005-01-18 14:44:13 FATAL: the > database system is starting up". Shouldn't this be "LOG" instead of "FATAL" > message. Well, it's not like the database does not startup or anything, the > server starts up just fine all the time ! Is it possible for a client to be attempting to connect during that time? IIRC, that message is the one sent to the client when it tries to connect during the startup time. ---(end of broadcast)--- TIP 8: explain analyze is your friend
