Re: [PERFORM] Benchmarking PostgreSQL?
Ivan Voras <[EMAIL PROTECTED]> writes: > I'm conducting some benchmarking (mostly for fun and learning), and one > part of it is benchmarking PostgreSQL (7.4.1, on FreeBSD 4.9 and 5.2). > I'm using pgbench from the contrib directory, but I'm puzzled by the > results. It is notoriously hard to get reproducible results from pgbench. However... > - I'm running pgbench with 35 clients and 50 transactions/client (1) what scale factor did you use to size the database? One of the gotchas is that you need to use a scale factor at least as large as the number of clients you are testing. The scale factor is equal to the number of rows in the "branches" table, and since every transaction wants to update some row of branches, you end up mostly measuring the effects of update contention if the scale factor is less than about the number of clients. scale 1 is particularly deadly, it means all the transactions get serialized :-( (2) 50 xacts/client is too small to get anything reproducible; you'll mostly be measuring startup transients. I usually use 1000 xacts/client. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow delete times??
First of, thanks, Tom. Although I've been very careful on this kind of things, looks like I missed one index on a referencing column. Still, I don't allow an entire delete of a table if it has referencing columns with values, so at the moment of the deletion, it has no rows at all. I checked datatype mismatches, and there are none. All my FKs are integers, like the referenced column of the referenced table. I was thinking on dropping the indexes before doing the deletes, but Joshua suggested using TRUNCATE instead. Thanks. Octavio. Tom Lane said: > "Octavio Alvarez" <[EMAIL PROTECTED]> writes: >> Please tell me if this timing makes sense to you for a Celeron 433 w/ RAM=256MB dedicated testing server. I expected some slowness, but not this >> high. > > I'll bet you have foreign keys referencing this table, and the > referencing columns do not have indexes. PG will let you do that ... but it makes updates and deletes horribly slow. You generally want to add those indexes. > > If they *are* indexed, check for datatype mismatches. That's > another thing that kills performance ... > > regards, tom lane > -- Octavio Alvarez Piza. E-mail: [EMAIL PROTECTED] -- Octavio Alvarez Piza. E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Benchmarking PostgreSQL?
I'm conducting some benchmarking (mostly for fun and learning), and one part of it is benchmarking PostgreSQL (7.4.1, on FreeBSD 4.9 and 5.2). I'm using pgbench from the contrib directory, but I'm puzzled by the results. I have little experience in benchmarking, but I don't think I should be getting the scattered results I have. - the computer is P3 @ 933MHz, 1Gb RAM - I'm running pgbench with 35 clients and 50 transactions/client - benchmark results are differentiating by about +/- 6 TPS. The results are between 32 TPS and 44 TPS - the results seem to be getting worse over time then suddenly jumping to the maximum (saw-tooth like). Sometime there is even (very noticable as a pattern!) indication of more-or-less regular alteration between the minimum and maximum values (e.g. first measurement yields 32, second yields 44, third again 32 or 31, etc...) - running vacuumdb -z -f on the database does not influence the results in predictable ways - waiting (sleeping) between pgbench runs (either regular or random) does not influence it in predictable ways - the same patterns appear between operating systems (FreeBSD 4.9 and 5.2) and reinstalls of it (the numbers are ofcourse somewhat different) postgresql.conf contains only these active lines: max_connections = 40 shared_buffers = 1 sort_mem = 8192 vacuum_mem = 32768 I've used these settings as they are expected to be used on a regular work load when the server goes into production. I've upped vacuum_mem as it seems to shorten vacuum times dramaticaly (I assume the memory is allocated when needed as the postgresql process is only 88MB in size (80Mb shared buffers)). What I'm really asking here is: are these results normal? If not, can they be improved, or is there a better off-the-shelf PostgreSQL benchmark tool? ---(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] High Performance/High Reliability File system on SuSE64
Mark Kirkwood wrote a little unclearly: And yes, I would recommend xfs - noticeably faster than ext3, and no sign of any mysterious hangs under load. I was thinking about the reported mini-hangs that folks are seeing with jfs, except the all important keyword "jfs" didnt make it out of my head and into the 2nd bit of the message :-( Sorry for the confusion. So I meant "faster that ext3, and without strange mini hangs like jfs...". Joshua wrote: The hangs you are having are due to several issues... one of them is the way ext3 syncs. Never suffered this personally, it was an unexpected filesystem corruption with ext3 that "encouraged" me to try out xfs instead. best wishes Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
Paul Ganainm <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] says... > > >> XFS.. hands down. > > > Off topic question here, but I'm a bit at a loss to understand exactly > what sgi are doing. > > > I thought that they were removing IRIX and going with Linux as the OS to > their high end graphical workstations, yet I see they still have IRIX on > their site. > > > What, exactly, is the story with this? Well, they do have a very large installed base of Irix systems, and a certain obligation to keep supporting them... As long as there are people whi still want MIPS/Irix they will probably keep selling at least a few models. -Doug ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Persistent Connections
Hi I have a php script and i make a pg_pconnect If i want to make 4-10 pg_query in that script Have i to close the connection at end of the script? (i would say yes, is it right?) Sorry I m a little bit confused about the persistent thing!! Is it smart to use persistent connections at all if i expect 100K Users to hit the script in an hour and the script calls up to 10-15 pg functions? I have at the mom one function but the server needs 500 ms, its a little bit too much i think, and it crashed when i had 20K users Thanks Bye
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
[EMAIL PROTECTED] says... > XFS.. hands down. Off topic question here, but I'm a bit at a loss to understand exactly what sgi are doing. I thought that they were removing IRIX and going with Linux as the OS to their high end graphical workstations, yet I see they still have IRIX on their site. What, exactly, is the story with this? Paul... -- plinehan y_a_h_o_o and d_o_t com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. "XML avoids the fundamental question of what we should do, by focusing entirely on how we should do it." quote from http://www.metatorial.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
Mark Kirkwood wrote: They seem pretty clean (have patched vanilla kernels + xfs for Mandrake 9.2/9.0). And yes, I would recommend xfs - noticeably faster than ext3, and no sign of any mysterious hangs under load. The hangs you are having are due to several issues... one of them is the way ext3 syncs. What kernel version are you running? Sincerely, Joshua Drake best wishes Mark Christopher Browne wrote: Do the patches work? As far as I have heard, quite well indeed. But the fact of it not having been 'official' is a fair little bit of a downside. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(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
