Re: [PERFORM] Compile Vs RPMs
[EMAIL PROTECTED] ("Anjan Dave") writes:
> I would like to know whether there are any significant performance
> advantages of compiling (say, 7.4) on your platform (being RH7.3, 8,
> and 9.0, and Fedora especially) versus getting the relevant binaries
> (rpm) from the postgresql site? Hardware is Intel XEON (various
> speeds, upto 2.8GHz, single/dual/quad configuration).
Some Linux distribution makers make grand claims of such advantages,
but it is not evident that this is much better than superstition.
You are certainly NOT going to see GCC generating MMX code
automagically that would lead to PostgreSQL becoming 8 times faster.
Indeed, in database work, it is quite likely that you will find things
to be largely I/O bound, with CPU usage being a very much secondary
factor.
I did some relative benchmarking between compiling PostgreSQL on GCC
versus IBM's PPC compilers a while back; did not see differences that
could be _clearly_ discerned as separate from "observational noise."
You should expect find that adding RAM, or adding a better disk
controller would provide discernable differences in performance. It
is much less clear that custom compiling will have any substantial
effect on I/O-bound processing.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
Re: [PERFORM] Database conversion woes...
On Tuesday 03 February 2004 22:29, Kevin wrote: > The mammoth replicator has been working well. I had tried > the pgsql-r and had limited success with it, and dbmirror was just > taking to long having to do 4 db transactions just to mirror one > command. I have eserv but was never really a java kind of guy. When this is over and you've got the time, I don't suppose you could put together a few hundred words describing your experiences with the Mammoth replicator - there are a couple of places they could be posted. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Compile Vs RPMs
On 03/02/2004 20:58 Anjan Dave wrote: Hello, I would like to know whether there are any significant performance advantages of compiling (say, 7.4) on your platform (being RH7.3, 8, and 9.0, and Fedora especially) versus getting the relevant binaries (rpm) from the postgresql site? Hardware is Intel XEON (various speeds, upto 2.8GHz, single/dual/quad configuration). Very unlikely I would have thought. Databases tend to speed-limited by I-O performance and the amount of RAM available for caching etc. Having said that, I've only got one machine (the laptop on which I'm writing this email) which has still got its rpm binaries. My other machines have all been upgraded from source. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Compile Vs RPMs
Anjan Dave wrote: Hello, I would like to know whether there are any significant performance advantages of compiling (say, 7.4) on your platform (being RH7.3, 8, and 9.0, and Fedora especially) versus getting the relevant binaries (rpm) from the postgresql site? Hardware is Intel XEON (various speeds, upto 2.8GHz, single/dual/quad configuration). "significant" is a relative term. 1% can be significant under the proper circumstances ... http://www.potentialtech.com/wmoran/source.php The information isn't specific to Postgres, and the results aren't really conclusive, but hopefully it helps. I really think that if someone would actually test this with Postgres and post the results, it would be very beneficial to the community. I have it on my list of things to do, but it's unlikely to get done in the first quarter the way things are going. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Database conversion woes...
First just wanted to say thank you all for the quick and helpful answers. With all the input I know I am on the right track. With that in mind I created a perl script to do my migrations and to do it based on moving from a db name to a schema name. I had done alot of the reading on converting based on the miss match of data types that MySQL likes to use. I must say it is VERY nice having a intelligent system that say won't let a date of '-00-00' be entered. Luckily I didn't have to deal with any enumerations. So the conversion goes on. I will definitely be back and forth in here as I get the new queries written and start migrating all I can back into the pg backend using plpgsql or c for the stored procedures where required. The mammoth replicator has been working well. I had tried the pgsql-r and had limited success with it, and dbmirror was just taking to long having to do 4 db transactions just to mirror one command. I have eserv but was never really a java kind of guy. Alright then - back to my code. Again thanks for the help and info. Kevin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Compile Vs RPMs
Title: Message Hello, I would like to know whether there are any significant performance advantages of compiling (say, 7.4) on your platform (being RH7.3, 8, and 9.0, and Fedora especially) versus getting the relevant binaries (rpm) from the postgresql site? Hardware is Intel XEON (various speeds, upto 2.8GHz, single/dual/quad configuration). Thankyou, Anjan ** This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.
Re: [PERFORM] Database conversion woes...
[EMAIL PROTECTED] ("Kevin Carpenter") writes:
> I am doing a massive database conversion from MySQL to Postgresql for a
> company I am working for. This has a few quirks to it that I haven't
> been able to nail down the answers I need from reading and searching
> through previous list info.
>
> For starters, I am moving roughly 50 seperate databases which each
> one represents one of our clients and is roughly 500 megs to 3 gigs
> in size. Currently we are using the MySQL replication, and so I am
> looking at Mammoths replicator for this one. However I have seen it
> only allows on DB to be replicated at a time. With the size of each
> single db, I don't know how I could put them all together under one
> roof, and if I was going to, what are the maximums that Postgres can
> handle for tables in one db? We track over 2 million new points of
> data (records) a day, and are moving to 5 million in the next year.
I'll be evasive about replication, because the answers are pretty
painful :-(, but as for the rest of it, nothing about this sounds
challenging.
There is room for debate here as to whether you should have:
a) One postmaster and many database instances,
2) One postmaster, one (or a few) database instances, and do the
client 'split' via schemas, or
iii) Several postmasters, many database instances.
Replication will tend to work best with scenario 2), which minimizes
the number of connections that are needed to manage replication;
that's definitely a factor worth considering.
It is also possible for it to be worthwhile to spread vastly differing
kinds of activity across different backends so that they can have
separate buffer caches. If all the activity is shared across one
postmaster, that means it is all shared across one buffer cache, and
there are pathological situations that are occasionally observed in
practice where one process will be "trashing" the shared cache,
thereby injuring performance for all other processes using that back
end. In such a case, it may be best to give the "ill-behaved" process
its own database instance with a small cache that it can thrash on
without inconveniencing others.
Jan Wieck is working on some improvements for buffer management in 7.5
that may improve the situation vis-a-vis buffering, but that is
certainly not something ready to deploy in production just yet.
> Second what about the physical database size, what are the limits
> there? I have seen that it was 4 gig on Linux from a 2000 message,
> but what about now? Have we found way's past that?
There's NO problem with having enormous databases now; each table is
represented as one or more files (if you break a size barrier, oft
configured as 1GB, it creates an "extent" and extends into another
file), and for there to be problems with this, the problems would be
_really crippling_ OS problems.
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"We come to bury DOS, not to praise it."
-- Paul Vojta <[EMAIL PROTECTED]>, paraphrasing a quote of
Shakespeare
---(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: [PERFORM] Database conversion woes...
On Tuesday 03 February 2004 16:42, Kevin Carpenter wrote: > > Thanks in advance, will give more detail - just looking for some open > directions and maybe some kicks to fuel my thought in other areas. I've taken to doing a lot of my data manipulation (version conversions etc) in PG even if the final resting place is MySQL. It's generally not too difficult to transfer data but you will have problems with MySQL's more "relaxed attitude" to data types (things like all-zero timestamps). I tend to write a script to tidy the data before export, and repeatedly restore from backup until the script corrects all problems.Not sure how convenient that'll be with dozens of gigs of data. Might be practical to start with the smaller databases, let your script grow in capabilities before importing the larger ones. -- Richard Huxton Archonet Ltd ---(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: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
Wow, I didn't know that (didn't get far enough to test any rollback). That's not a good thing. But then again, it's MySQL who needs rollback anyway? On Feb 2, 2004, at 5:44 PM, Christopher Kings-Lynne wrote: One more thing that annoyed me. If you started a process, such as a large DDL operation, or heaven forbid, a cartesian join (what? I never do that!). I believe InnoDB also has O(n) rollback time. eg. if you are rolling back 100 million row changes, it takes a long, long time. In PostgreSQL rolling back is O(1)... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Database conversion woes...
Kevin, > With the size of each single db, I don't > know how I could put them all together under one roof, and if I was > going to, what are the maximums that Postgres can handle for tables in > one db? We track over 2 million new points of data (records) a day, and > are moving to 5 million in the next year. Use schemas per Scott's suggestion. This will also ease the sharing of data between "databases". > Second what about the physical database size, what are the limits there? > I have seen that it was 4 gig on Linux from a 2000 message, but what > about now? Have we found way's past that? The biggest database I've ever worked with was 175G, but I've seen reports of 2TB databases out there. We don't know what the limit is; so far it's always been hardware. > Thanks in advance, will give more detail - just looking for some open > directions and maybe some kicks to fuel my thought in other areas. Come back to this list for help tuning your system! You'll need it, you've got an unusual set-up. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] views?
> > This is called a "materialized view". PostgreSQL doesn't support them > > yet, but most people think it would be a Good Thing to have. > > There is a project on gborg (called "mview" iirc) though I don't know how far > it's got - I think it's still pretty new. tnx > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
Josh Berkus wrote: Folks, I've had requests from a couple of businesses to see results of infomal MySQL +InnoDB vs. PostgreSQL tests.I know that we don't have the setup to do full formal benchmarking, but surely someone in our community has gone head-to-head on your own application? Josh, how does someone compare an Apache+PHP+MySQL "thing" against something implemented with half the stuff done in stored procedures and the entire business model guarded by referential integrity, custom triggers and whatnot? Seriously, I am tired of this kind of question. You gotta get bold enough to stand up in a "meeting" like that, say "guy's, you can ask me how this compares to Oracle ... but if you're seriously asking me how this compares to MySQL, call me again when you've done your homework". Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Database conversion woes...
On Tue, 3 Feb 2004, Kevin Carpenter wrote: > Hello everyone, > > I am doing a massive database conversion from MySQL to Postgresql for a > company I am working for. This has a few quirks to it that I haven't > been able to nail down the answers I need from reading and searching > through previous list info. > > For starters, I am moving roughly 50 seperate databases which each one > represents one of our clients and is roughly 500 megs to 3 gigs in size. > Currently we are using the MySQL replication, and so I am looking at > Mammoths replicator for this one. However I have seen it only allows on > DB to be replicated at a time. Look into importing all those seperate databases into seperate schemas in one postgresql database. > With the size of each single db, I don't > know how I could put them all together under one roof, There's no functional difference to postgresql if you have 1 huge database or 50 smaller ones that add up to the same size. > and if I was > going to, what are the maximums that Postgres can handle for tables in > one db? None. also see: http://www.postgresql.org/docs/faqs/FAQ.html#4.5 > We track over 2 million new points of data (records) a day, and > are moving to 5 million in the next year. That's quite a bit. Postgresql can handle it. > Second what about the physical database size, what are the limits there? none. > I have seen that it was 4 gig on Linux from a 2000 message, but what > about now? Have we found way's past that? It has never been 4 gig. It was once, a long time ago, 2 gig for a table I believe. That was fixed years ago. > Thanks in advance, will give more detail - just looking for some open > directions and maybe some kicks to fuel my thought in other areas. Import in bulk, either using copy or wrap a few thousand inserts inside begin;end; pairs. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
Jeff <[EMAIL PROTECTED]> writes: > Not sure at what point it will topple, in my case it didn't matter if it > ran good with 5 clients as I'll always have many more clients than 5. I did some idle, very unscientific tests the other day that indicated that MySQL insert performance starts to suck with just 2 concurrent inserters. Given a file containing 1 INSERT commands, a single mysql client ran the file in about a second. So if I feed the file simultaneously to two mysqls in two shell windows, it should take about two seconds total to do the 2 inserts, right? The observed times were 13 to 15 seconds. (I believe this is with a MyISAM table, since I just said CREATE TABLE without any options.) It does scream with only one client though ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Database conversion woes...
On Tue, 03 Feb 2004 11:42:59 -0500 "Kevin Carpenter" <[EMAIL PROTECTED]> wrote: > For starters, I am moving roughly 50 seperate databases which each one > represents one of our clients and is roughly 500 megs to 3 gigs in > size. > Currently we are using the MySQL replication, and so I am looking at > Mammoths replicator for this one. However I have seen it only allows > on DB to be replicated at a time. With the size of each single db, I Not knowing too much about mammoths, but how the others work, you should be able to run a replicator for each db. (Or hack a shell script up to make it run the replicator for each db.. either way each db will be replicated independant of the others) > don't know how I could put them all together under one roof, and if I > was going to, what are the maximums that Postgres can handle for > tables in one db? We track over 2 million new points of data > (records) a day, and are moving to 5 million in the next year. > >From the docs: Maximum size for a database unlimited (4 TB databases exist) Maximum size for a table16 TB on all operating systems Maximum size for a row 1.6 TB Maximum size for a field1 GB Maximum number of rows in a table unlimited Maximum number of columns in a table250 - 1600 depending on column types Maximum number of indexes on a table unlimited ... My largest PG db is 50GB. My busiest PG db runs about 50 update|delete|insert's / second (sustained throughout the day. It bursts up to 150 now and then). And we're doing about 40 selects / second. And the machine it is running on is typically 95% idle. (Quad 2ghz xeon) -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
Jeff <[EMAIL PROTECTED]> writes: > On Tue, 03 Feb 2004 11:46:05 -0500 > Tom Lane <[EMAIL PROTECTED]> wrote: >> I did some idle, very unscientific tests the other day that indicated >> that MySQL insert performance starts to suck with just 2 concurrent >> inserters. Given a file containing 1 INSERT commands, a single >> mysql client ran the file in about a second. So if I feed the file >> simultaneously to two mysqls in two shell windows, it should take >> about two seconds total to do the 2 inserts, right? The observed >> times were 13 to 15 seconds. (I believe this is with a MyISAM table, >> since I just said CREATE TABLE without any options.) > MyISAM is well known to suck if you update/insert/delete because it > simply aquires a full table lock when you perform those operations! Sure, I wasn't expecting it to actually overlap any operations. (If you try the same test with Postgres, the scaling factor is a little better than linear because we do get some overlap.) But that shouldn't result in a factor-of-seven slowdown. There's something badly wrong with their low-level locking algorithms I think. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
On Tue, 03 Feb 2004 11:46:05 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Jeff <[EMAIL PROTECTED]> writes: > > Not sure at what point it will topple, in my case it didn't matter > > if it ran good with 5 clients as I'll always have many more clients > > than 5. > > I did some idle, very unscientific tests the other day that indicated > that MySQL insert performance starts to suck with just 2 concurrent > inserters. Given a file containing 1 INSERT commands, a single > mysql client ran the file in about a second. So if I feed the file > simultaneously to two mysqls in two shell windows, it should take > about two seconds total to do the 2 inserts, right? The observed > times were 13 to 15 seconds. (I believe this is with a MyISAM table, > since I just said CREATE TABLE without any options.) > MyISAM is well known to suck if you update/insert/delete because it simply aquires a full table lock when you perform those operations! InnoDB is supposed to be better at that. So your results are fairly in line with what you should see. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Database conversion woes...
Hello everyone, I am doing a massive database conversion from MySQL to Postgresql for a company I am working for. This has a few quirks to it that I haven't been able to nail down the answers I need from reading and searching through previous list info. For starters, I am moving roughly 50 seperate databases which each one represents one of our clients and is roughly 500 megs to 3 gigs in size. Currently we are using the MySQL replication, and so I am looking at Mammoths replicator for this one. However I have seen it only allows on DB to be replicated at a time. With the size of each single db, I don't know how I could put them all together under one roof, and if I was going to, what are the maximums that Postgres can handle for tables in one db? We track over 2 million new points of data (records) a day, and are moving to 5 million in the next year. Second what about the physical database size, what are the limits there? I have seen that it was 4 gig on Linux from a 2000 message, but what about now? Have we found way's past that? Thanks in advance, will give more detail - just looking for some open directions and maybe some kicks to fuel my thought in other areas. Thanks, -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
On Tue, 3 Feb 2004 16:02:00 +0200 "Rigmor Ukuhe" <[EMAIL PROTECTED]> wrote: > > script [I also decided to use this perl script for testing PG to be > > fair]. > > > > For one client mysql simply screamed. > > > > If already have test case set up, you could inform us, from where > Postgres starts to beat MySql. Because if with 5 clients it still > "screams" then i would give it a try in case of that kind of > requirements. > I just checked (to see about restarting the innodb test) and it appears that it'll take a bit of work to get the machine up and running. I don't have time right now to do further testing. However, you could try it out. Not sure at what point it will topple, in my case it didn't matter if it ran good with 5 clients as I'll always have many more clients than 5. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Increasing number of PG connections.
On 2 Feb 2004 at 16:45, scott.marlowe wrote: > Do you have the cache set to write back or write through? Write through > can be a performance killer. But I don't think your RAID is the problem, > it looks to me like postgresql is doing a lot of I/O. When you run top, > do the postgresql processes show a lot of D status? That's usually waiting > on I/O > Actually I'm not sure. It's setup with the factory defaults from IBM. Actually when I start hitting the limit I was surprised to find only a few D status indicators. Most of the processes where sleeping. > what you want to do is get the machine to a point where the kernel cache > is about twice the size or larger, than the shared_buffers. I'd start at > 1 shared buffers and 4096 sort mem and see what happens. If you've > still got >2 gig kernel cache at that point, then increase both a bit (2x > or so) and see how much kernel cache you've got. If your kernel cache > stays above 1Gig, and the machine is running faster, you're doing pretty > good. > I've set shared to 1 and sort to 4096. I just have to wait until the afternoon before I see system load start to max out. Thanks for the tips I'm crossing my fingers. -- Kevin Barnard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PQexecParams and types
Czuczy Gergely <[EMAIL PROTECTED]> writes: > to leave it unspecified what value should I set to the paramTypes array? > and could you insert this answer to to docs, it could be useful It is in the docs: paramTypes[] specifies, by OID, the data types to be assigned to the parameter symbols. If paramTypes is NULL, or any particular element in the array is zero, the server assigns a data type to the parameter symbol in the same way it would do for an untyped literal string. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PQexecParams and types
hello to leave it unspecified what value should I set to the paramTypes array? and could you insert this answer to to docs, it could be useful Bye, Gergely Czuczy mailto: [EMAIL PROTECTED] PGP: http://phoemix.harmless.hu/phoemix.pgp The point is, that geeks are not necessarily the outcasts society often believes they are. The fact is that society isn't cool enough to be included in our activities. On Tue, 3 Feb 2004, Tom Lane wrote: > Czuczy Gergely <[EMAIL PROTECTED]> writes: > > i've read in the docs to use the proper indexes both types must match in > > the where clause, to achive this the user can simply put a string into the > > side of the equation mark and pgsql will convert it automaticly. my > > question is, when I'm using PQexecParams, should I give all the values as > > a string, and it will be converted, or I have to figure out the type of > > the given field somehow? > > You should leave the parameter types unspecified. Their types will be > resolved in much the same way that a quoted literal is handled. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PQexecParams and types
Czuczy Gergely <[EMAIL PROTECTED]> writes: > i've read in the docs to use the proper indexes both types must match in > the where clause, to achive this the user can simply put a string into the > side of the equation mark and pgsql will convert it automaticly. my > question is, when I'm using PQexecParams, should I give all the values as > a string, and it will be converted, or I have to figure out the type of > the given field somehow? You should leave the parameter types unspecified. Their types will be resolved in much the same way that a quoted literal is handled. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
> script [I also decided to use this perl script for testing PG to be > fair]. > > For one client mysql simply screamed. > If already have test case set up, you could inform us, from where Postgres starts to beat MySql. Because if with 5 clients it still "screams" then i would give it a try in case of that kind of requirements. Rigmor Ukuhe > Then I decided to see what happens with 20 clients. > > MySQL clocked in at 650 seconds. During this time the machine was VERY > unresponsive. To be fair, that could be Linux, not MySQL. > > PG (7.3.4) clocked in at 220 seconds. The machine was perfectly fine > during the test - nice and responsive. > > The hardware wasn't much - dual p2-450 running stock RH8. (2x15k 18g > scsi drives for the data volume) > > Then I decided to try the "beloved" InnoDB. > > Well.. after it sat for a few hours at 100% cpu loading the data I > killed it off and gave up on InnoDB.. I am interested in the numbers. > Perhaps I'll fire it up again someday and let it finish loading. > > Remember - you cannot judge mysql by since connection performance - you > can't beat it. But just add up the concurrency and watch the cookies > tumble > > -- > Jeff Trout <[EMAIL PROTECTED]> > http://www.jefftrout.com/ > http://www.stuarthamm.net/ > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004 > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
Well, when I prepared my PG presentation I did some testing of MySQL (So I could be justified in calling it lousy :). I used the latest release (4.0.something I think) I was first bitten by my table type being MyISAM when I thought I set the default ot InnoDB. But I decided since my test was going to be read-only MyISAM should be the best possible choice. I loaded up a couple million records and changed my stored procedure into a perl script [I also decided to use this perl script for testing PG to be fair]. For one client mysql simply screamed. Then I decided to see what happens with 20 clients. MySQL clocked in at 650 seconds. During this time the machine was VERY unresponsive. To be fair, that could be Linux, not MySQL. PG (7.3.4) clocked in at 220 seconds. The machine was perfectly fine during the test - nice and responsive. The hardware wasn't much - dual p2-450 running stock RH8. (2x15k 18g scsi drives for the data volume) Then I decided to try the "beloved" InnoDB. Well.. after it sat for a few hours at 100% cpu loading the data I killed it off and gave up on InnoDB.. I am interested in the numbers. Perhaps I'll fire it up again someday and let it finish loading. Remember - you cannot judge mysql by since connection performance - you can't beat it. But just add up the concurrency and watch the cookies tumble -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] cache whole data in RAM
Put it on a RAM disk. chris On Tue, 2004-02-03 at 07:54, David Teran wrote: > Hi, > > we are trying to speed up a database which has about 3 GB of data. The > server has 8 GB RAM and we wonder how we can ensure that the whole DB > is read into RAM. We hope that this will speed up some queries. > > regards David > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] cache whole data in RAM
David Teran wrote: we are trying to speed up a database which has about 3 GB of data. The server has 8 GB RAM and we wonder how we can ensure that the whole DB is read into RAM. We hope that this will speed up some queries. Neither the DBa or postgresql has to do anything about it. Usually OS caches the data in it's buffer cache. That is certainly true for linux and freeBSD does that. Most of the unices certainly do. To my knowledge linux is most aggresive one at that..(Rather over aggressive..) Make sure that you size effective cache size correctly. It helps postgresql planner at times.. HTH Shridhar ---(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: [PERFORM] cache whole data in RAM
On Tue, Feb 03, 2004 at 13:54:17 +0100, David Teran <[EMAIL PROTECTED]> wrote: > Hi, > > we are trying to speed up a database which has about 3 GB of data. The > server has 8 GB RAM and we wonder how we can ensure that the whole DB > is read into RAM. We hope that this will speed up some queries. The OS should do this on its own. What you don't want to do is set shared_buffers (in postgresql.conf) too high. From what I remember from past discussions it should be something between about 1000 and 1. sort_mem is trickier since that memory is per sort and a single query can potentially generate multiple parallel sorts. You will have to make some guesses for this based on what you think the number of concurrent sorts will be when the system is stressed and not use too much memory. You might also find that after a point you don't get a lot of benefit from increasing sort_mem. You should set effective_cache_size pretty large. Probably you want to subtract the space used by shared_buffers and sort_mem (* times estimated parallel sorts) and what you think is reasonable overhead for other processes from the 8GB of memory. Since your DB's disk blocks will almost certainly all be in buffer cache, you are going to want to set random_page_cost to be pretty close to 1. ---(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
[PERFORM] cache whole data in RAM
Hi, we are trying to speed up a database which has about 3 GB of data. The server has 8 GB RAM and we wonder how we can ensure that the whole DB is read into RAM. We hope that this will speed up some queries. regards David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] PQexecParams and types
Hello i've read in the docs to use the proper indexes both types must match in the where clause, to achive this the user can simply put a string into the side of the equation mark and pgsql will convert it automaticly. my question is, when I'm using PQexecParams, should I give all the values as a string, and it will be converted, or I have to figure out the type of the given field somehow? i'm writing an interface for my php extension(i'm not statisfied by the boundled), so i cannot figure out the type of the fields in most cases. what should be done for the best performance in this situation? Bye, Gergely Czuczy mailto: [EMAIL PROTECTED] PGP: http://phoemix.harmless.hu/phoemix.pgp The point is, that geeks are not necessarily the outcasts society often believes they are. The fact is that society isn't cool enough to be included in our activities. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: inserting large number of rows was: Re: [PERFORM] Increasing
You could do high speed inserts with COPY command: http://developer.postgresql.org/docs/postgres/sql-copy.html Check whenether your database adapter/client lib supports it (i guess it does). Note that it doesnt help very much if there are fk's/triggers's on the target table. Bill Moran wrote: I must have missed this post when it was made earlier. Pardon the noise if my suggestion has already been made. Unlike MySQL (and possibly other database servers) PostgreSQL is faster when inserting inside a transaction. Depending on the method in which you are actually adding the records. In my own experience (generating a list of INSERT statements from a perl script and using psql to execute them) the difference in performance was incredibly dramatic when I added a "BEGIN WORK" at the beginning and "COMMIT WORK" at the end. scott.marlowe wrote: On Mon, 2 Feb 2004, Qing Zhao wrote: I am new here. I have a question related to this in some way. Our web site needs to upload a large volume of data into Postgres at a time. The performance deterioates as number of rows becomes larger. When it reaches 2500 rows, it never come back to GUI. Since the tests were run through GUI, my suspision is that it might be caused by the way the application server talking to Postgres server, the connections, etc.. What might be the factors involved here? Does anyone know? Actually, I'm gonna go out on a limb here and assume two things: 1. you've got lotsa fk/pk relationships setup. 2. you're analyzing the table empty before loading it up. What happens in this instance is that the analyze on an empty, or nearly so, table, means that during the inserts, postgresql thinks you have only a few rows. At first, this is fine, as pgsql will seq scan the tables to make sure there is a proper key in both. As the number of rows increases, the planner needs to switch to index scans but doesn't, because it doesn't know that the number of rows is increasing. Fix: insert a few hundred rows, run analyze, check to see if the explain for inserts is showing index scans or not. If not, load a few more hundred rows, analyze, rinse, repeat. Also, look for fk/pk mismatches. I.e. an int4 field pointing to an int8 field. That's a performance killer, so if the pk/fk types don't match, see if you can change your field types to match and try again. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
