Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: Re: Re: How to count rows using DBI & mysql - Performance results

by busunsl (Vicar)
on Jun 11, 2001 at 18:09 UTC ( #87494=note: print w/ replies, xml ) Need Help??


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

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.


Comment on Re: Re: Re: How to count rows using DBI & mysql - Performance results
Select or Download Code
Re: Re: Re: Re: How to count rows using DBI & mysql - Performance results
by elwarren (Curate) on Jun 11, 2001 at 22:45 UTC
    Good point. I'm not sure how intelligent MySQL is, but the optimizer in Oracle will make this choice.

    If you submit a count(*) query on a table with a primary key, it will use the index to generate the count instead of actually counting all the rows in the table.

    The same holds true when doing a count(colname) and an index exists on that column.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://87494]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2014-09-24 03:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (245 votes), past polls