Re: [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: * The schema now uses foreign keys to more accurately reflect a finacial DDL Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. * The history table now has a primary key that uses a serial Ditto. Again, part of the point :) * The respective balance columns have been increased to int8 to deal with larger values Ditto. This was done because you can easily break pgbench without the increase in data type. pgbench -c 850 -t 1000 pgbench gave a stream of errors like this before ending: Client 18 aborted in state 8: ERROR: integer out of range Client 429 aborted in state 8: ERROR: integer out of range Client 168 aborted in state 8: ERROR: integer out of range PG error log showed: 2006-08-22 15:45:19 PDT-[local]STATEMENT: UPDATE branches SET bbalance = bbalance + 4209228 WHERE bid = 679; 2006-08-22 15:45:19 PDT-[local]ERROR: integer out of range * Initalization will be done in a new schema/namespace, pgbench will exit if this schema/namespace exists OK, maybe that doesn't matter. Yeah I did it just so we wouldn't stomp on somebody on accident. * The new DDL should allow both Mammoth Replicator and Slony to be tested using pgbench (at least basic replication) Erm ... exactly why couldn't you do that before? history was missing a primary key. It could be done before. I just removed a step in getting it to work. pgbench doesn't have all that many things to recommend it, but what it does have is that it's been a stable testbed across quite a few PG releases. Arbitrarily whacking around the tested functionality will destroy that continuity. Well to be fair, I wasn't doing it arbitrarily. I had a specific purpose which was to have it use a schema that would be closer to a production schema, without breaking existing behavior. This patch does that :) I fell into this trap before myself ... I have a local copy of pgbench that produces TPS numbers quite a lot better than the standard pgbench, against exactly the same server. What's wrong with that picture? Well I think we all agree that some of the behavior of pgbench has been weird. Sincerely, Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Some minor changes to pgbench
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. pgbench is not about reality, though. If we can't rely on it to give consistent results across versions then I don't think it's useful at all. There are many other benchmarks you can run that do speak to reality (eg OSDL's work). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. pgbench is not about reality, though. If we can't rely on it to give consistent results across versions then I don't think it's useful at all. There are many other benchmarks you can run that do speak to reality (eg OSDL's work). Would it be worthwhile to add a switch so that the foreign key test is only used if they use the switch in conjunction with a -i? Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Some minor changes to pgbench
Joshua D. Drake [EMAIL PROTECTED] writes: Would it be worthwhile to add a switch so that the foreign key test is only used if they use the switch in conjunction with a -i? I wouldn't object to providing that as a (non default) option. The int8 change should be unnecessary in view of Tatsuo's recent fix to make the random deltas symmetrical about zero. I would counsel against making the other changes either, as they seem to accomplish little except make the comparability of results more doubtful. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Would it be worthwhile to add a switch so that the foreign key test is only used if they use the switch in conjunction with a -i? I wouldn't object to providing that as a (non default) option. O.k. I will take a look at what that would take.. The int8 change should be unnecessary in view of Tatsuo's recent fix to make the random deltas symmetrical about zero. O.k. that may be the case, my testing was with the 8.1 version pgbench. I will verify with -HEAD before I change the int8. I would counsel against making the other changes either, as they seem to accomplish little except make the comparability of results more doubtful. I am not interested in really changing the overall internals. I just wanted the foreign key option. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Some minor changes to pgbench
Joshua D. Drake [EMAIL PROTECTED] writes: * The schema now uses foreign keys to more accurately reflect a finacial DDL Addition of foreign key checking will certainly impact performance significantly. * The history table now has a primary key that uses a serial Ditto. * The respective balance columns have been increased to int8 to deal with larger values Ditto. * Initalization will be done in a new schema/namespace, pgbench will exit if this schema/namespace exists OK, maybe that doesn't matter. * The new DDL should allow both Mammoth Replicator and Slony to be tested using pgbench (at least basic replication) Erm ... exactly why couldn't you do that before? pgbench doesn't have all that many things to recommend it, but what it does have is that it's been a stable testbed across quite a few PG releases. Arbitrarily whacking around the tested functionality will destroy that continuity. I fell into this trap before myself ... I have a local copy of pgbench that produces TPS numbers quite a lot better than the standard pgbench, against exactly the same server. What's wrong with that picture? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
