Posted by Ian Holsman
Wed, 05 Apr 2006 22:13:00 GMT
this is the output of
dtrace -n 'pid$target:::entry{ @[probefunc] = count() }'
when I run mysqlslap -c 20 -i 20
pthread_getspecific 365
malloc_internal 377
my_malloc 377
free 378
malloc 378
my_wc_mb_filename 399
my_no_flags_free 420
strmake 444
pthread_self 468
memset 582
alloc_root 630
strmov 651
strlen 655
memcpy 897
mutex_trylock 936
my_pthread_fastmutex_lock 936
mutex_lock 1133
mutex_lock_impl 1133
my_utf8_uni 1365
mutex_unlock 2069
I’m about to prepare a more formal analysis (and benchmark) about this. but in order for me not to compete with colm I’m going to wait until the 24th to submit it.
I switched to using sysbench as I thought there might be a bug in actual mysqlslap code, but it is also showing the same results.
those being mysql runs 10x slower on the sunfire than on a linux x86-64 machine.
If you are from sun, mysql, or a apache member, and want to check out the configuration to see how I screwed up, send an email from your @sun.com/@mysql.com/@apache.org email address with a public key and I’ll mail you the IP#/account details (ian at holsman.net).
the machine is only available while I’m awake (I live in Australia so it probably means your nighttime), as I haven’t put it in a colo (until I win it of course) and my ‘server room’ is my walk in robe.
Posted in General | Tags mysql, sun, sunfire, t2000 | 5 comments | no trackbacks
Posted by Ian Holsman
Mon, 03 Apr 2006 00:56:00 GMT
ok.. now this is getting stranger.
I have 2 windows open, in one I get
$ mysqlslap --use-threads -psunfire -c 90 -i 500
Benchmark
Average number of seconds to run all queries: 2.180 seconds
Minimum number of seconds to run all queries: 0.779 seconds
Maximum number of seconds to run all queries: 4.497 seconds
Number of clients running queries: 90
Average number of queries per client: 0
and in the other i get
$ mysqlslap --use-threads -psunfire -c 90 -i 500
Benchmark
Average number of seconds to run all queries: 0.012 seconds
Minimum number of seconds to run all queries: 0.002 seconds
Maximum number of seconds to run all queries: 0.015 seconds
Number of clients running queries: 90
Average number of queries per client: 0
same machine, same user-id, same binary. the only difference is that mysql started in the ‘fast’ window.
Posted in Development | Tags mysql, sun, sunfire, t2000 | no comments | 1 trackback
Posted by Ian Holsman
Fri, 31 Mar 2006 13:02:00 GMT
and doesn’t seem to take it too nicely :-(
I decided to install the mysql beta on my laptop (Mac Intel @2G ram and 2GHZdual core) and my sunfire (@8G ram and 16 1GHZ Ultrasparcs).
(I have it installed on a x86-64 box as well, but that machine is busy at the moment)..
so for a quick sanity check I ran mysqlslap to see how it performs.
I chose to simulate a 20 concurrent users
sunfire$ mysqlslap -psunfire -c 20 -i 20
Benchmark
Average number of seconds to run all queries: 1.714 seconds
Minimum number of seconds to run all queries: 0.941 seconds
Maximum number of seconds to run all queries: 2.539 seconds
Number of clients running queries: 20
Average number of queries per client: 0
vs
laptop$ mysqlslap -i 20 -c 20
Benchmark
Average number of seconds to run all queries: 1.201 seconds
Minimum number of seconds to run all queries: 1.072 seconds
Maximum number of seconds to run all queries: 1.569 seconds
Number of clients running queries: 20
Average number of queries per client: 0
now.. before you get all pissy at me, these installs are both from mysql.com, are BETA release using ‘default’ setups..
I know they need tuning.
so I decided to run the mysqlslap against blastwave’s mysql5 (5.0.19) which has a bit better default setup (IMHO)
sunfire $ mysqlslap -c 20 -i 20
Benchmark
Average number of seconds to run all queries: 1.647 seconds
Minimum number of seconds to run all queries: 0.882 seconds
Maximum number of seconds to run all queries: 2.783 seconds
Number of clients running queries: 20
Average number of queries per client: 0
still the same kind of numbers.
one thing I did find interesting was that I only saw one connection in the SHOW PROCESS LIST command.
any mysql guru’s want to tell me what I’m doing wrong on the test here?
update: this is what I get on a x86-64 machine.
$ bin/mysqlslap -c 20 -i 20 -a
Benchmark
Average number of seconds to run all queries: 0.002 seconds
Minimum number of seconds to run all queries: 0.002 seconds
Maximum number of seconds to run all queries: 0.003 seconds
Number of clients running queries: 20
Average number of queries per client: 0
same deal.. no tweaking of configurations, just download the tarball from mysql.com and fire it up.
anyone have any ideas?
Posted in hardware | Tags mysql, sun, sunfire, t2000 | no comments | no trackbacks
Posted by Ian Holsman
Thu, 16 Mar 2006 22:22:00 GMT
In anticipation of getting my sunfire , and doing some real benchamrks with it, I thought I’d do a quick, test to see if I can still do them..kind of like a warm up excerise before you do a 1500m swim.
So I grabbed 2 idle machines I have access too, and see how mysql 5.1 performed on both.
I took Brian’s mysqlslap tool and gave it a little whirl.
the 3 boxes I had in my aresenal are
- dual x86-64 @2.4G with 8G of ram
a
- 280R with 2 Ultrasparc III+ processors and 4G of ram.
and my new macbook
- a Intel Dual processor shiny thing
now in their time, both of these boxes were considered pretty sweet. (the 280R was purchased in 2002 I think, and for financial people just nearing the end of it’s lifecycle so you will still see these for a couple more years as dev boxes and file servers).
the test:
$ mysqlslap -a -c 20 -i 200
Maximum number of seconds to run all queries: 0.003 seconds
vs
Maximum number of seconds to run all queries: 1.621 seconds
and the laptop came in at about 0.012 seconds… not too bad
This is the main reason I got out of doing benchmark’s and performance tuning in my previous job.
In 4 years (2-3 lifetimes) the speed increase is about 500 times.
The answer for a lot of questions which used to be “design it better” is now “buy another box”...
I plan on running the same test on my sunfire when it arrives (bit with a lot more attention to detail).
If you are benchmarking a sunfire (in the hopes of getting it for free) feel free to stop by on sunfirefan.com and tell us about your experiences.
Posted in Development | Tags 280R, mysql, sun, sunfire | no comments | no trackbacks
Posted by Ian Holsman
Mon, 06 Mar 2006 02:07:00 GMT
With the introduction of mysql 5.1, they have introduced full text plugins which allows people to write their own parsers.
Now I’m not an expert in Lucene or this new feature, but it seems that both of these products are heading for the same point,
One of the benefits I have often heard touted was that lucene had better relevancy, while mysql was faster. (no I don’t have any numbers to back this up.. maybe when my Sun Fire comes I’ll try loading this up as my ‘benchmark’). So it will be interesting to actually do some experimentation to see if you could get the best of both worlds.
maybe someone can sponsor me to re-tool nutch to work with a pure mysql back-end (Brian/Zack ?? are you reading this? does MySQL want to give me a grant? :-)
Posted in Development | Tags lucene, mysql | no comments | no trackbacks
Posted by Ian Holsman
Mon, 12 Dec 2005 19:09:00 GMT
for most ORM’s the typical access path is
single table selects either for:
- a primary key (for a single record)
- a list of related records (ie.. a foreign key)
- and possibly a ‘%XXX%’ to match a name or something
Is the HANDLER functionality a better (ie faster) method than using SQL syntax in this case?
it seems like a more natural ‘fit’ for the ORM type behavior.
Posted in Development | Tags mysql | no comments | no trackbacks
Posted by Ian Holsman
Sat, 12 Nov 2005 18:32:00 GMT
Brad Fitzpatrick has a nice discussion over what people should be using for their ‘id’ keys in a distributed environment. and puts a couple of points forward about why it isn’t such a good idea for his application/architecture. which has a ‘few’ central machines handing out ID’s.
The is a central problem with sequences that i’ve seen in most DB platforms. That and their 32-bit size. (I think mysql is 32-bit, i’m sure the geeks who read this will correct me)
The actual question has (If I am reading this correctly) is not about UUID’s vs local-sequences, but how do I move a ‘tree’ of records from one cluster to another, and not have a ID-clash. as if each cluster was independant, it wouldnt be a concern. you would have a central USER->cluster mapping algorithm/server which might be UUID or 64-bit based and then all future requests could be directed to the correct cluster, where locally-generated sequences would be fine.
Another approach would be to increase the size of the ID to 64-bits, with the top n-bits (say 8) designating the cluster which created the sequence. when a new cluster/allocater comes online it would register itself and it’s cluster-ID# would be incremented. then all sequences generated by this cluster allocater would just start at X. this would give you 56bits of uniqueness per cluster. but it would still require some kind of central co-ordination at cluster creation time.
This would increase the key size from 4 bytes to 8.
But personally, I would see how I can avoid this whole issue complelty, and try to make each cluster fully independant and NOT have to merge/move users between clusters… with the price of today’s hardware about $5k/box.. the issue of excess capacity and non utilization is less important I think.
Posted in Development | Tags db, design, mysql | no comments | no trackbacks
Posted by Ian Holsman
Wed, 12 Oct 2005 16:29:00 GMT
I actually think it will be business as usual for MySQL. and that oracle will continue to licence it to them for the long term.
Why?
well.. there are other technologies which are available to MySQL to choose from.
They have a year or so they have before their existing licence runs out in which they can adapt the codebases of the offerings to fit their model, and make the improvments required to match the features currently offered in InnoDB. (or even get it to do 80% of the features with a promise of future innovations)
Where can they look?
- Berkely DB (yes I know they have this as an option already)
- Postgres
- FireBird
- NuSphere’s Gemini
- CA’s Ingres
- Gigabase
I’m sure there are more.. I don’t follow the space that closely.
Brian & Monty are smart guys, I’d even hazard a guess that they have some handler hidden away in case of this eventuality.
So I belive the lesson to learn is to make sure you have more than one supplier for any critical component, and enough of a buffer zone that the threat to switch is credible.
So why did Oracle buy it? not for this reason.
or if they did, I don’t think they did their due-dilligence properly and underestimated how easy it would be to switch out InnoDB with another technology. A year is a long time in the development world when you have ‘hungry’ developers who know their shit and are stuck in a hard place.
Posted in Business Related | Tags mysql, oracle | 1 comment | no trackbacks