RE: Servere performance issue with compiled (pre-prepared) querie s - they're 50% slower than parsed queries
Kevin Cowley <[email protected]>
| Newsgroups | gmane.comp.db.mysql.bugs |
|---|---|
| Message-ID | <A170CC6BD457D3118EF800508B2C304805C366EF@mailhost.alchemetrics.co.uk> |
I've just downloaded the latest snapshot, rebuilt 4.1.2 and re-run the
tests. The results are below. It looks like the Inserts are now quicker, but
not by much, but the selects are considerably slower.
I'll download and rerun the tests at the end of the week unless you
specifically ask for another run sooner.
Parsed Queries
Insert
total average mean mean max min
run records time time time records time time
0 24851 6.820585 0.000274 0.000275 2620 19829.000000
226.000000
1 24851 6.065923 0.000244 0.000231 4456 8437.000000
225.000000
2 24851 6.304409 0.000254 0.000231 3459 30257.000000
226.000000
3 24851 6.510745 0.000262 0.000231 2607 18397.000000
225.000000
4 24851 6.724278 0.000271 0.000231 3375 191237.000000
224.000000
5 24851 8.153951 0.000328 0.000275 3823 20535.000000
227.000000
6 24851 6.882307 0.000277 0.000275 4093 21495.000000
227.000000
7 24851 6.425859 0.000259 0.000231 3676 20949.000000
226.000000
8 24851 6.470958 0.000260 0.000231 3130 22156.000000
225.000000
9 24851 6.282352 0.000253 0.000232 4421 21469.000000
226.000000
Select
run 0 run 1 run 2 run 3 run 4 run 5
run 6 run 7 run 8 run 9 average
query rows time time time time time time
time time time time time
0 61 0.074916 0.072847 0.072966 0.072618 0.072627 0.072619
0.072647 0.072607 0.072610 0.072586 0.072904
1 7 0.049443 0.049423 0.049373 0.049351 0.049371 0.049354
0.049372 0.049349 0.049357 0.049351 0.049374
2 41 0.092653 0.092617 0.092579 0.092552 0.092536 0.092584
0.092576 0.092556 0.092562 0.092537 0.092575
3 9 0.048734 0.048697 0.048834 0.048660 0.048707 0.048675
0.048661 0.048660 0.048792 0.048672 0.048709
4 16 0.048296 0.048277 0.048280 0.048287 0.048277 0.048282
0.048263 0.048263 0.048268 0.048442 0.048293
5 62 0.092250 0.092204 0.092175 0.092157 0.092143 0.092190
0.092129 0.092192 0.092129 0.093665 0.092323
6 6 0.066074 0.066101 0.066061 0.066111 0.066087 0.066170
0.066072 0.066095 0.066123 0.066497 0.066139
7 1 0.123014 0.123101 0.122950 0.122984 0.122952 0.122979
0.122906 0.122879 0.123233 0.122876 0.122987
8 8 0.047967 0.047914 0.047902 0.047905 0.049032 0.047899
0.047914 0.047874 0.047927 0.047925 0.048026
9 4 0.113309 0.113234 0.113296 0.113226 0.116240 0.113273
0.113541 0.113248 0.113206 0.113224 0.113580
10 0 0.215568 0.215465 0.215478 0.215458 0.233277 0.215481
0.215375 0.215539 0.215344 0.215625 0.217261
11 0 0.140931 0.141002 0.140924 0.140928 0.141657 0.140951
0.141049 0.140876 0.140869 0.140872 0.141006
12 1 0.250533 0.250500 0.251514 0.250477 0.250623 0.250553
0.250457 0.250825 0.250445 0.250462 0.250639
13 0 0.123744 0.123715 0.124173 0.123710 0.123893 0.123866
0.123662 0.123758 0.123657 0.123666 0.123784
14 0 0.134293 0.134303 0.134271 0.134346 0.134316 0.134277
0.134306 0.134235 0.134282 0.134286 0.134291
15 6 0.245146 0.245130 0.245171 0.245123 0.245227 0.245100
0.245139 0.245025 0.245034 0.245082 0.245118
16 0 0.074154 0.074260 0.074158 0.074192 0.074204 0.074155
0.074141 0.074125 0.074168 0.074160 0.074172
17 0 0.096158 0.096137 0.096129 0.096357 0.096171 0.096206
0.096148 0.096108 0.096173 0.096147 0.096173
18 1 0.126585 0.126651 0.126583 0.126572 0.126625 0.126568
0.126603 0.126524 0.126606 0.126578 0.126590
19 8 0.111515 0.110790 0.110945 0.110769 0.111060 0.110829
0.110755 0.110779 0.110728 0.110793 0.110896
Compiled (Pre-prepared)
Insert
run records time time time records time time
0 24851 6.474224 0.000261 0.000257 4597 21462.000000
207.000000
1 24851 6.523098 0.000262 0.000255 6049 23318.000000
210.000000
2 24851 6.386642 0.000257 0.000256 4449 23161.000000
207.000000
3 24851 6.519648 0.000262 0.000256 6229 19210.000000
248.000000
4 24851 7.719113 0.000311 0.000256 5180 24232.000000
206.000000
5 24851 6.161722 0.000248 0.000257 4259 19774.000000
206.000000
6 24851 5.987938 0.000241 0.000212 3898 23452.000000
206.000000
7 24851 6.327306 0.000255 0.000256 3534 24790.000000
205.000000
8 24851 6.091885 0.000245 0.000212 4112 24251.000000
207.000000
9 24851 6.382733 0.000257 0.000212 5787 191016.000000
206.000000
Select
run 0 run 1 run 2 run 3 run 4 run 5
run 6 run 7 run 8 run 9 average
query rows time time time time time time
time time time time time
0 0 0.437444 0.419829 0.412711 0.096162 0.085786 0.089553
0.415813 0.447224 0.412606 0.097838 0.291497
1 0 0.421069 0.420800 0.420568 0.068622 0.065719 0.086317
0.483824 0.133079 0.556397 0.098176 0.275457
2 0 0.421695 0.421387 0.422516 0.491819 0.246641 0.107930
0.560652 0.484094 0.555208 0.200201 0.391214
3 0 0.421287 0.421791 0.423138 0.090164 0.090360 0.067598
0.485454 0.134826 0.605345 0.617853 0.335782
4 0 0.420637 0.419801 0.421588 0.068673 0.065798 0.067577
0.411304 0.064665 0.552008 0.618335 0.311039
5 0 0.421718 0.420134 0.411883 0.491230 0.259388 0.107861
0.411920 0.477208 0.412143 0.201834 0.361532
6 0 0.420711 0.424738 0.412332 0.097632 0.087143 0.087326
0.412273 0.450799 0.411840 0.268889 0.307368
7 0 0.421789 0.424857 0.422870 0.515664 0.527633 0.129651
0.540812 0.705058 0.557240 0.457545 0.470312
8 0 0.421194 0.421585 0.423295 0.092068 0.105025 0.067555
0.485094 0.192330 0.558749 0.620728 0.338762
9 0 0.422031 0.419744 0.412516 0.492132 0.488680 0.129601
0.412837 0.480036 0.418996 0.220696 0.389727
10 0 0.426005 0.424792 0.451020 0.516837 0.553936 0.543235
0.575944 0.639704 0.714140 0.902019 0.574763
11 0 0.422444 0.429347 0.449292 0.572222 0.620788 0.298257
0.551548 0.754606 0.829995 0.737637 0.566614
12 0 0.424097 0.432708 0.454663 0.578973 0.617020 0.571019
0.563856 0.767397 0.843719 0.884417 0.613787
13 0 0.422312 0.423519 0.426041 0.518890 0.534378 0.232511
0.495625 0.564376 0.565397 0.441198 0.462425
14 0 0.421741 0.422967 0.412499 0.521565 0.537638 0.161451
0.412316 0.447149 0.412858 0.439183 0.418937
15 0 0.424952 0.430311 0.441126 0.556725 0.591733 0.554715
0.566439 0.661365 0.692906 0.892472 0.581274
16 0 0.422370 0.423990 0.453605 0.117189 0.138630 0.218314
0.499172 0.563484 0.570311 0.235178 0.364224
17 0 0.422323 0.419337 0.418111 0.492441 0.249981 0.129617
0.412780 0.447589 0.412502 0.199785 0.360447
18 0 0.421743 0.420457 0.427702 0.498457 0.499763 0.186529
0.412596 0.571030 0.413459 0.437185 0.428892
19 0 0.422239 0.432789 0.415143 0.561170 0.544531 0.141262
0.515882 0.450903 0.591474 0.633315 0.470871
Kevin Cowley
R&D
Tel: 0118 902 9099 (direct line)
Email: [email protected]
Web: http://www.alchemetrics.co.uk
-----Original Message-----
From: Sinisa Milivojevic [mailto:[email protected]]
Sent: 05 May 2004 13:29
To: [email protected]
Cc: [email protected]
Subject: Re: Servere performance issue with compiled (pre-prepared) queries
- they're 50% slower than parsed queries
Hi!
We have done some measurements with 4.1.2 and prepared statements were
a bit faster. 4.1.2 should be out within a week or two.
We would be gratefull if you could repeat your measurements with
4.1.2.
Regarding result set, that is not a bad idea. This would however
require a new set of functions. Probably most current application
would continue to use current API.
**************************************************************************************
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel: +44 (0) 118 902 9000 Fax: +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must
not use, copy, disclose, otherwise disseminate or take any action based on
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception
and unauthorised amendment. Alchemetrics does not accept any liability for
any such corruption, interception, amendment or the consequences thereof.
**************************************************************************************
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/[email protected]