Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
Most RDBMS won't even consider using an index on a table with only 4 rows due to the overhead in I/O and processing.

I created the table in a Sybase database, filled it with about 2500 rows with my db-stress tool and tested against it (with only 1000 iterations).

First without an index:
Benchmark: timing 1000 iterations of Way1A, Way2A, Way2B, Way3A, Way4A +, Way4B, Way5A, Way5B, Way6A... Way1A: 52 wallclock secs ( 1.46 usr + 0.28 sys = 1.74 CPU) @ 57 +4.05/s (n=1000) Way2A: 30 wallclock secs ( 1.17 usr + 0.17 sys = 1.34 CPU) @ 74 +5.16/s (n=1000) Way2B: 30 wallclock secs ( 1.31 usr + 0.18 sys = 1.49 CPU) @ 66 +9.79/s (n=1000) Way3A: 47 wallclock secs ( 1.02 usr + 0.22 sys = 1.24 CPU) @ 80 +5.80/s (n=1000) Way4A: 30 wallclock secs ( 1.31 usr + 0.18 sys = 1.49 CPU) @ 67 +0.24/s (n=1000) Way4B: 29 wallclock secs ( 1.14 usr + 0.20 sys = 1.34 CPU) @ 74 +4.60/s (n=1000) Way5A: 29 wallclock secs ( 1.16 usr + 0.12 sys = 1.28 CPU) @ 78 +0.03/s (n=1000) Way5B: 25 wallclock secs ( 0.92 usr + 0.18 sys = 1.10 CPU) @ 90 +8.27/s (n=1000) Way6A: 53 wallclock secs ( 1.30 usr + 0.27 sys = 1.57 CPU) @ 63 +6.13/s (n=1000) Rate Way1A Way6A Way2B Way4A Way4B Way2A Way5A Way3A Way5B Way1A 574/s -- -10% -14% -14% -23% -23% -26% -29% -37% Way6A 636/s 11% -- -5% -5% -15% -15% -18% -21% -30% Way2B 670/s 17% 5% -- -0% -10% -10% -14% -17% -26% Way4A 670/s 17% 5% 0% -- -10% -10% -14% -17% -26% Way4B 745/s 30% 17% 11% 11% -- -0% -5% -8% -18% Way2A 745/s 30% 17% 11% 11% 0% -- -4% -8% -18% Way5A 780/s 36% 23% 16% 16% 5% 5% -- -3% -14% Way3A 806/s 40% 27% 20% 20% 8% 8% 3% -- -11% Way5B 908/s 58% 43% 36% 36% 22% 22% 16% 13% --
Next with an index on column 'usedby':
Benchmark: timing 1000 iterations of Way1A, Way2A, Way2B, Way3A, Way4A +, Way4B, Way5A, Way5B, Way6A... Way1A: 32 wallclock secs ( 0.93 usr + 0.18 sys = 1.11 CPU) @ 89 +9.28/s (n=1000) Way2A: 12 wallclock secs ( 1.27 usr + 0.19 sys = 1.46 CPU) @ 68 +4.46/s (n=1000) Way2B: 13 wallclock secs ( 1.18 usr + 0.12 sys = 1.30 CPU) @ 76 +8.05/s (n=1000) Way3A: 26 wallclock secs ( 1.00 usr + 0.23 sys = 1.23 CPU) @ 81 +1.03/s (n=1000) Way4A: 16 wallclock secs ( 1.36 usr + 0.16 sys = 1.52 CPU) @ 65 +7.03/s (n=1000) Way4B: 13 wallclock secs ( 1.30 usr + 0.14 sys = 1.44 CPU) @ 69 +3.48/s (n=1000) Way5A: 13 wallclock secs ( 0.92 usr + 0.14 sys = 1.06 CPU) @ 94 +2.51/s (n=1000) Way5B: 7 wallclock secs ( 0.90 usr + 0.17 sys = 1.07 CPU) @ 93 +2.84/s (n=1000) Way6A: 8 wallclock secs ( 1.09 usr + 0.23 sys = 1.32 CPU) @ 75 +6.43/s (n=1000) Rate Way4A Way2A Way4B Way6A Way2B Way3A Way1A Way5B Way5A Way4A 657/s -- -4% -5% -13% -14% -19% -27% -30% -30% Way2A 684/s 4% -- -1% -10% -11% -16% -24% -27% -27% Way4B 693/s 6% 1% -- -8% -10% -14% -23% -26% -26% Way6A 756/s 15% 11% 9% -- -2% -7% -16% -19% -20% Way2B 768/s 17% 12% 11% 2% -- -5% -15% -18% -19% Way3A 811/s 23% 18% 17% 7% 6% -- -10% -13% -14% Way1A 899/s 37% 31% 30% 19% 17% 11% -- -4% -5% Way5B 933/s 42% 36% 35% 23% 21% 15% 4% -- -1% Way5A 943/s 43% 38% 36% 25% 23% 16% 5% 1% --
and last with an index on both (usedby, uses). For this I had to use 5000 iteration :-) :
Benchmark: timing 5000 iterations of Way1A, Way2A, Way2B, Way3A, Way4A +, Way4B, Way5A, Way5B, Way6A... Way1A: 43 wallclock secs ( 5.12 usr + 1.03 sys = 6.15 CPU) @ 81 +3.14/s (n=5000) Way2A: 34 wallclock secs ( 6.04 usr + 0.84 sys = 6.88 CPU) @ 72 +6.85/s (n=5000) Way2B: 38 wallclock secs ( 6.22 usr + 0.66 sys = 6.88 CPU) @ 72 +6.74/s (n=5000) Way3A: 46 wallclock secs ( 5.35 usr + 1.20 sys = 6.55 CPU) @ 76 +3.36/s (n=5000) Way4A: 34 wallclock secs ( 6.29 usr + 0.59 sys = 6.88 CPU) @ 72 +6.74/s (n=5000) Way4B: 35 wallclock secs ( 6.15 usr + 0.71 sys = 6.86 CPU) @ 72 +8.86/s (n=5000) Way5A: 33 wallclock secs ( 4.88 usr + 0.62 sys = 5.50 CPU) @ 90 +9.42/s (n=5000) Way5B: 31 wallclock secs ( 4.76 usr + 0.72 sys = 5.48 CPU) @ 91 +2.74/s (n=5000) Way6A: 46 wallclock secs ( 5.52 usr + 1.17 sys = 6.69 CPU) @ 74 +7.38/s (n=5000) Rate Way2B Way4A Way2A Way4B Way6A Way3A Way1A Way5A Way5B Way2B 727/s -- -0% -0% -0% -3% -5% -11% -20% -20% Way4A 727/s 0% -- -0% -0% -3% -5% -11% -20% -20% Way2A 727/s 0% 0% -- -0% -3% -5% -11% -20% -20% Way4B 729/s 0% 0% 0% -- -2% -5% -10% -20% -20% Way6A 747/s 3% 3% 3% 3% -- -2% -8% -18% -18% Way3A 763/s 5% 5% 5% 5% 2% -- -6% -16% -16% Way1A 813/s 12% 12% 12% 12% 9% 7% -- -11% -11% Way5A 909/s 25% 25% 25% 25% 22% 19% 12% -- -0% Way5B 913/s 26% 26% 26% 25% 22% 20% 12% 0% --
The result don't differ that much. Reasons in this case might be that the database server is running on another machine, all data is sent over the net and most of the processing is done in the program and not in the database.
So i fired up the database monitor and checked again. Here only some performance figures for the runs:
Without any index:
Engine Busy Utilization Engine 0 42.4 % + Inserts APL Heap Table 13950 Total Lock Requests 2391135
With index on (usedby):
Engine Busy Utilization Engine 0 35.5 % + Inserts APL Heap Table 11025 Total Lock Requests 769881
With index on both (usedby, uses):
Engine Busy Utilization Engine 0 37.3 % + Inserts APL Heap Table 10125 Total Lock Requests 814515
The figure pointing to network load are not immediatly visible and hard to interpret, so i skipped them. But the network had definitely an impact.
So the server was 35% to 42% busy, which is not much, but far more interesting it processed about 2.4 million pages w/o using an index and about 800,000 pages w/ an index. This can cause serious problems with locking and caching in the database server.
With only 2,500 rows the index is just one level high, so to have a real impact with indexes you have to have far more rows.

So here's the next test with about 50,000 rows (db-stress is cool :-) (but only 500 iteration, i don't want to spend the night on this):

First without any index:

Benchmark: timing 500 iterations of Way1A, Way2A, Way2B, Way3A, Way4A, + Way4B, Way5A, Way5B, Way6A... Way1A: 82 wallclock secs ( 0.55 usr + 0.17 sys = 0.72 CPU) @ 69 +3.48/s (n=500) Way2A: 48 wallclock secs ( 0.62 usr + 0.10 sys = 0.72 CPU) @ 69 +3.48/s (n=500) Way2B: 52 wallclock secs ( 0.57 usr + 0.04 sys = 0.61 CPU) @ 81 +8.33/s (n=500) Way3A: 78 wallclock secs ( 0.55 usr + 0.11 sys = 0.66 CPU) @ 75 +6.43/s (n=500) Way4A: 52 wallclock secs ( 0.60 usr + 0.05 sys = 0.65 CPU) @ 76 +8.05/s (n=500) Way4B: 53 wallclock secs ( 0.61 usr + 0.09 sys = 0.70 CPU) @ 71 +4.29/s (n=500) Way5A: 52 wallclock secs ( 0.51 usr + 0.06 sys = 0.57 CPU) @ 87 +4.13/s (n=500) Way5B: 45 wallclock secs ( 0.39 usr + 0.09 sys = 0.48 CPU) @ 10 +39.50/s (n=500) Way6A: 70 wallclock secs ( 0.61 usr + 0.15 sys = 0.76 CPU) @ 65 +7.03/s (n=500) Rate Way6A Way2A Way1A Way4B Way3A Way4A Way2B Way5A Way5B Way6A 657/s -- -5% -5% -8% -13% -14% -20% -25% -37% Way2A 693/s 6% -- -0% -3% -8% -10% -15% -21% -33% Way1A 693/s 6% 0% -- -3% -8% -10% -15% -21% -33% Way4B 714/s 9% 3% 3% -- -6% -7% -13% -18% -31% Way3A 756/s 15% 9% 9% 6% -- -2% -8% -13% -27% Way4A 768/s 17% 11% 11% 8% 2% -- -6% -12% -26% Way2B 818/s 25% 18% 18% 15% 8% 7% -- -6% -21% Way5A 874/s 33% 26% 26% 22% 16% 14% 7% -- -16% Way5B 1040/s 58% 50% 50% 46% 37% 35% 27% 19% -- Sybase performance values: Engine Busy Utilization Engine 0 34.4 % + Inserts APL Heap Table 20250 Total Lock Requests 4331515
And with an index on (usedby, uses):
Benchmark: timing 500 iterations of Way1A, Way2A, Way2B, Way3A, Way4A, + Way4B, Way5A, Way5B, Way6A... Way1A: 48 wallclock secs ( 0.52 usr + 0.16 sys = 0.68 CPU) @ 73 +4.21/s (n=500) Way2A: 26 wallclock secs ( 0.75 usr + 0.03 sys = 0.78 CPU) @ 64 +0.20/s (n=500) Way2B: 29 wallclock secs ( 0.69 usr + 0.08 sys = 0.77 CPU) @ 64 +8.51/s (n=500) Way3A: 5 wallclock secs ( 0.52 usr + 0.04 sys = 0.56 CPU) @ 89 +1.27/s (n=500) Way4A: 3 wallclock secs ( 0.58 usr + 0.05 sys = 0.63 CPU) @ 79 +2.39/s (n=500) Way4B: 3 wallclock secs ( 0.72 usr + 0.09 sys = 0.81 CPU) @ 61 +6.52/s (n=500) Way5A: 28 wallclock secs ( 0.50 usr + 0.05 sys = 0.55 CPU) @ 90 +7.44/s (n=500) Way5B: 30 wallclock secs ( 0.56 usr + 0.04 sys = 0.60 CPU) @ 83 +3.33/s (n=500) Way6A: 61 wallclock secs ( 0.62 usr + 0.15 sys = 0.77 CPU) @ 64 +7.67/s (n=500) Rate Way4B Way2A Way6A Way2B Way1A Way4A Way5B Way3A Way5A Way4B 617/s -- -4% -5% -5% -16% -22% -26% -31% -32% Way2A 640/s 4% -- -1% -1% -13% -19% -23% -28% -29% Way6A 648/s 5% 1% -- -0% -12% -18% -22% -27% -29% Way2B 649/s 5% 1% 0% -- -12% -18% -22% -27% -29% Way1A 734/s 19% 15% 13% 13% -- -7% -12% -18% -19% Way4A 792/s 29% 24% 22% 22% 8% -- -5% -11% -13% Way5B 833/s 35% 30% 29% 29% 14% 5% -- -6% -8% Way3A 891/s 45% 39% 38% 37% 21% 12% 7% -- -2% Way5A 907/s 47% 42% 40% 40% 24% 15% 9% 2% -- Sybase performance values: Engine Busy Utilization Engine 0 6.2 % + Inserts APL Heap Table 7875 Total Lock Requests 84663
The throughput doesn't differ much, because benchmark is only benchmarking cpu-time use by the process, but the wallclock time differs much. This time is spend on the database server.

So when you do benchmarks, you have to consider both sides.


In reply to Re: Re: Re: How to count rows using DBI & mysql - Performance results by busunsl
in thread How to count rows using DBI & mysql - Performance results by Brovnik

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others perusing the Monastery: (15)
    As of 2014-07-10 21:01 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      When choosing user names for websites, I prefer to use:








      Results (216 votes), past polls