I've been putting a little bit of thought into how to go about testing the
performance of this patch. From reading the previous threads quite a bit of
testing was done with a certain data set where all that tested found it to
be a big winner with staggering performance gains with the skewed dataset.
Still the wiki page states that it needs performance testing. I'm guessing
what we really need to test now is ask: Are non skewed sets any slower now?
Where do we start seeing the gains?

So I've been working a little on a set of data that can be created simply
just be running a few SQLs. I've yet run the tests as I'm having some
hardware problem with my laptop. In the meantime I thought I'd share what I
was going to test with the community to see if I'm going about things the
right way.

The idea I came up with for benchmarking was a little similar to what I
remember from the original tests. I have a sales orders table and a products
table. My version of the sales orders table contains a customer column. Data
for 10 customers is populated into the sales orders table, customer 1 has a
totally non-skewed set of orders, where customer 10 has the most skew. I've
done this by creating 10000 products each with a product code that has been
cast into a varchar and padded up to 5 chars in length with '0's. Each
customer has the same number of rows in the sales orders table, customer 10
mostly orders products that when cast as INT are evenly divisible by 10,
where customer 2 mostly orders products that are evenly divisible by 2. You
get the idea.

Once I get this laptop sorted out or get access to some better hardware It
was my plan to benchmark and chart the results from customers 1 to 10 for
with and without the patch. What I hope to prove is that customer 1 is
almost the same for with as without the patch and hopefully see an even rise
in performance as the customer id number increases.

Currently I'm unsure the best way to ensure that the hash join goes into
more than one batch apart from just making the dataset very large.

Does anyone have any thoughts about the way I plan to go about benchmarking?

Please see the attached document for the benchmark script.

David.

Search Discussions

  • Tom Lane at Feb 10, 2009 at 10:36 pm

    "David Rowley" <dgrowley@gmail.com> writes:
    Currently I'm unsure the best way to ensure that the hash join goes into
    more than one batch apart from just making the dataset very large.
    Make work_mem very small?

    But really there are two different performance regimes here, one where
    the hash data is large enough to spill to disk and one where it isn't.
    Reducing work_mem will cause data to spill into kernel disk cache, but
    if the total problem fits in RAM then very possibly that data won't ever
    really go to disk. So I suspect such a test case will act more like the
    small-data case than the big-data case. You probably actually need more
    data than RAM to be sure you're testing the big-data case.

    Regardless, I'd like to see some performance results from both regimes.
    It's also important to be sure there is not a penalty for single-batch
    cases.

    regards, tom lane
  • Lawrence, Ramon at Feb 11, 2009 at 3:51 am

    The idea I came up with for benchmarking was a little similar to what I
    remember from the original tests. I have a sales orders table and a
    products
    table. My version of the sales orders table contains a customer column.
    Data
    for 10 customers is populated into the sales orders table, customer 1 has
    a
    totally non-skewed set of orders, where customer 10 has the most skew.
    I've
    done this by creating 10000 products each with a product code that has
    been
    cast into a varchar and padded up to 5 chars in length with '0's. Each
    customer has the same number of rows in the sales orders table, customer
    10
    mostly orders products that when cast as INT are evenly divisible by 10,
    where customer 2 mostly orders products that are evenly divisible by 2.
    You
    get the idea.
    Currently I'm unsure the best way to ensure that the hash join goes into
    more than one batch apart from just making the dataset very large.

    Does anyone have any thoughts about the way I plan to go about
    benchmarking?
    Thank you for testing the patch - it is very much appreciated. If you
    use the test version of the patch, it will print out statistics that
    will be helpful.

    I think your approach should work. I have two comments:

    1) You will need to scale the data set larger to go multi-batch. Even a
    minimum work_mem of 1 MB may be enough to keep the product table in
    memory unless each tuple is large. For the TPC-H tests, the size of
    product was 200,000 for 1 GB tests and 2 million tuples for 10 GB tests.

    2) The current formula may not generate the skew you expect on
    sales.productcode. To simplify the discussion, I will only consider
    customer 1 (c1) and customer 10 (c10) and a total of 100,000 sales
    (50,000 for each customer).

    If I look at product 10 for instance, it will be ordered 50,000/1,000 =
    50 times by c10 and 50,000/10,000 = 5 times by c1 for a total of 55
    times. Product 10 represents only 0.055% of all sales. For all mod 10
    products combined, they represent 55% of sales, which is significant BUT
    requires us to store 10% of product in memory (1000 tuples all of which
    need to be in the stats record).

    This two customer test would be interesting. There should be no benefit
    for customer 1. In fact, you would see the worst case as you would plan
    for skew but not get any benefit. For customer 10 you should see a
    benefit if your stats have 1000 tuples. The issue is that you cannot
    scale this test easily. Increasing by a factor of 10 would require
    stats of 10,000, and increasing by a factor of 100 is not possible.

    The Zipfian distribution used in the previous experiments causes the top
    few values to be exponentially better than the average value. For
    instance, the top 100 products may represent 10 to 50% of total sales
    even for 1 million products. In the previous case, the top 100 products
    represent only 0.0055% of total sales for 1 million products. This
    level of skew would be ignored by the algorithm which has a cutoff value
    that at least 1% of the probe relation must match with the skew values
    buffered in memory.

    To test higher values of skew, you could setup the experiment like this
    (may scale down by a factor of 10 depending on your hardware):

    products - 1 million
    sales - 10 million
    customers - 5
    - Each customer has 2 million orders.
    - Customer 1 orders each product equally.
    - Customer 2 orders each product mod 10^2 equally.
    - Customer 5 orders each product mod 10^5 equally.

    It is customer 5's orders that result in most of the skew as every
    100,000th product will be ordered 200,000 times (customer 5 only orders
    10 products). Then, there is a huge benefit for customer 5 for keeping
    these 10 products in memory during the join. The benefit decreases for
    each customer all the way down to customer 1 which will see no benefit.

    --
    Ramon Lawrence
  • Lawrence, Ramon at Feb 11, 2009 at 4:04 am

    -----Original Message-----
    From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
    owner@postgresql.org] On Behalf Of Tom Lane
    But really there are two different performance regimes here, one where
    the hash data is large enough to spill to disk and one where it isn't.
    Reducing work_mem will cause data to spill into kernel disk cache, but
    if the total problem fits in RAM then very possibly that data won't ever
    really go to disk. So I suspect such a test case will act more like the
    small-data case than the big-data case. You probably actually need more
    data than RAM to be sure you're testing the big-data case.
    Is there a way to limit the kernel disk cache? (We are running SUSE
    Linux.)

    We have been testing hybrid hash join performance and have seen that the
    performance varies considerably less than expected even for dramatic
    changes in work_mem and the I/Os that appear to be performed.

    --
    Ramon Lawrence

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 10, '09 at 10:08p
activeFeb 11, '09 at 4:04a
posts4
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase