Postgresql – Running a TPC-C Benchmark Without sleep() i.e. key-in + think time

benchmarkpostgresqlpostgresql-9.2

We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost 0 from 300 that we got at times) TPS graph even in supposedly steady state. During these tests we had not specified the sleeptimes for transactions (there are different sleep times recommended for different transaction types). So, by default all agents (for example, 100) continuously ran a mix of five transaction types without any sleeps in between. Later, we found out that we could in fact specify the sleep times; and when we did we could see a smoother graph without much variations.

So, the question is, if not specifying the sleep could be the real reason behind the bad results in first few tests?

Best Answer

Apologies for the late response to this, but there is a key reason as to why you are not observing an expected smooth behavior: You have a client-server model violation when you eliminate think time.

The client server model is built upon a foundation that not all user are using system resources at the same time. This is different than the mainframe model with terminals where all users are being serviced at the same time by the core host. With client-server distributed processing exists and there is a delay between requests during which the resources are free'd to service the needs of other inbound users. Normally when you place a host under load with think time these free internals tend to become smaller and smaller and as load increases you eventually reach a tipping point of no free resources to service requests.

By eliminating the think time you eliminate the free resource window to service additional requests and your tests become invalid as a predictor in real world performance. If you ever hear a performance tester tell you that they have eliminated think time and every one user is equal to n real world users, fire them on the spot.

Related Solutions

Postgresql – Group By primary key or DISTINCT increase query time over 1000x with limit

First off, LIMIT / OFFSET without ORDER BY are of limited usefulness, since the order is arbitrary and can change any time (when VACUUM runs or when the table is manipulated in at any way). It is only somewhat reliable with read-only tables. That's fine if you don't care which rows you get back, but it may break paging.

You may be able to solve your conundrum with the good old EXISTS. PostgreSQL can stop searching for more hits as soon as the first is found - as opposed to your query with DISTINCT, where it tries to collect all matches.

It's hard to be more specific without knowing the table structure, cardinalities, index definitions and what's behind your peculiar WHERE expression. But this might just do it:

SELECT s.chem_compound_id AS chem1_0_ 
       -- 10 more columns all short varchar or date fields
FROM   simple_compound s
WHERE  EXISTS (
   SELECT 1
   FROM   compound_composition sc
   JOIN   chemical_structure   c  ON sc.chemical_structure_id = c.structure_id 
   WHERE  c.structure_id  @ ('CCNc1ccccc1', '')::bingo.sub
   AND    sc.chem_compound_id = s.chem_compound_id
   )
LIMIT  5 
OFFSET 5

This also assumes you are only interested in columns from simple_compound in the output.

PostgreSQL – Function quote_nullable(timestamp without time zone) Not Unique

Works as is in my installation (Postgres 9.3).

You probably can fix this by casting to text.

SELECT quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy')::text);

And do not cast the result to date. It would be pointless to call quote_nullable() if you try to cast the result back to date. It would also fail for NULL input.

Postgres allows function overloading. My installation has one instance of quote_nullable() taking text and another one taking anyelement (polymorphic type). The latter takes care of the date.

As for timestamp without time zone: Postgres first looks for an exact match (date in your case), then considers other options like types that can be cast implicitly or polymorphic types. The manual has the exact procedure for Function Type Resolution.

Not sure about EnterpriseDB. Run this to diagnose:

SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  proname = 'quote_nullable';

More about function overloading in this related answer on SO:
ERROR: function addgeometrycolumn is not unique

Best Answer

Related Solutions

Postgresql – Group By primary key or DISTINCT increase query time over 1000x with limit

PostgreSQL – Function quote_nullable(timestamp without time zone) Not Unique

Related Question