Re: MySQL Performance 6.0rc1

Michael VInce wrote:

> 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

I realized I read your email wrong and noticed you used 10 clients and 
200,000 queries, I have re-run the test and I scored 24,138 this is on a 
4.1 MySQL install, to me it seems like there is something really wrong 
with your box.
This is a Dual Pentium 4 box on 6.0RC1 generic SMP kernel.

# super-smack /usr/share/smacks/select-key.smack 10 10000
Query Barrel Report for client smacker1
connect: max=4ms  min=2ms avg= 2ms from 10 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    200000  0       0       24138.77


My results from timer.c

       null function: 0.00339
            getpid(): 0.29227
              time(): 1.14151
      gettimeofday(): 1.14168