MySQL Performance 6.0rc1
Michael VInce
mv at roq.com
Fri Oct 28 01:13:47 PDT 2005
Marian Hettwer wrote:
> Hi Robert,
>
> Robert Watson wrote:
>
>>
>> On Wed, 26 Oct 2005, Marian Hettwer wrote:
>>
>>> I'm using RC1 on some of my test servers and workstations. So far,
>>> it runs very good (as in stable and fast) :) However, I thought of
>>> doing a small benchmark of MySQL in comparison to Linux 2.6.8
>>> (Debian Sarge) and the results are kinda... shocking.
>>>
>>> On FreeBSD I took a pretty much generic kernel, I just removed
>>> debugging options, added SMP and changed the scheduler from BSD to
>>> ULE. I used super-smack to do a select-key test. MySQL 4.1 was build
>>> from ports with BUILD_OPTIMIZED and BUILD_STATIC
>>
>>
>>
>> Could you:
>>
>> - Switch back to 4BSD
>>
> yep.
> by the way, back in the days of FreeBSD 4.x and 5.x, after I did a
> config MYKERNEL, it doesn't said I need to do a "make cleandepend"
> before doing a "make depend"... why that? Seems like this additional
> step in compiling the Kernel is around since 6-CURRENT, right?
>
> Here we go with a super-smack with 4BSD (but unchanged libmap.conf)...
> Kernel is apart from scheduler still the same like the on uploaded at
> http://unixoid.de/rabauke/freebsd/
> I did the select-smack 4 times. This is now: 4BSD, MySQL 4.1.14,
> BUILD_STATIC, BUILD_OPTIMIZED:
>
> select_index 200000 2 0 3776.34
> select_index 200000 2 0 3802.04
> select_index 200000 2 0 3809.38
> select_index 200000 2 0 3796.41
>
> Numbers are quite the same with 4BSD...
>
>
>> - Use libmap to configure the mysql server to use libthr instead of
>> libpthread
>>
> uh. Last time I fiddled around with libmap.conf was back in FreeBSD
> 5.1 ... could you enlighten me a wee bit in regards to libmap.conf ? :)
> If not, I'll stress google a bit and hopefully find something useful...
>
>
>> And see what that does to performance using otherwise identical
>> configurations?
>>
> my.cnf is pretty much my-medium.cnf on Debian and FreeBSD. Although,
> Debian has MySQL 4.1.11 and FreeBSD Ports has MySQL 4.1.14.
> As mentioned before, this benchmark here is a quick shot. However, the
> difference shouldn't be _that_ large (3600 vs. 14000).
>
>
I have run the exact same test and have had much higher benchmarks
(close to double) using my-large.cnf or my-huge.cnf.
I am able to get 23,626 compared to your 3,800 on the 5.4 and I am sure
with out a doubt I could get higher on 6.0.
The most important thing to remember about this benchmark is that its
not really a good test for MySQL performance for a lot of database
setups as its testing many queries at the same time performance, the
services I run largely have only 1 or maybe 2 connected constantly
querying clients. So its much more important for a single query to just
be able to get its data fast more them having 50 different clients doing
a queries at the same time for me and a lot of other db setups out there.
Here is a copy and paste of a post I did a while ago
I compiled 4.0 MySQL with some optimizations
portupgrade -RN -m 'BUILD_STATIC=yes BUILD_OPTIMIZED=yes' /usr/ports/databases/mysql40-server/
Copied the default large my.cnf profile cp /usr/local/share/mysql/my-large.cnf /var/db/mysql/my.cnf
# super-smack /usr/share/smacks/select-key.smack 50 1000
Query Barrel Report for client smacker1
connect: max=44ms min=11ms avg= 20ms from 50 clients
Query_type num_queries max_time min_time q_per_s
select_index 100000 3 0 23626.76
If I delete the /var/db/mysql/my.cnf and restart mysql so I get default
configuration, I get 13398.76
# super-smack /usr/share/smacks/select-key.smack 50 1000
Query Barrel Report for client smacker1
connect: max=29ms min=7ms avg= 17ms from 50 clients
Query_type num_queries max_time min_time q_per_s
select_index 100000 7 0 13398.76
Mike
More information about the freebsd-current
mailing list