Re: MySQL Performance 6.0rc1

Hej Xin, (or is it Li ?)


Xin LI wrote:
> Hi, Marian,
> 
> � 2005-10-26�� 11:14 +0200�Marian Hettwer���
> 
> 
> [...]
> 
>>Any Ideas where this huge difference comes from? Did I oversee some 
>>debugging features still active in RC1 ?
> 
> 
> There are many factors that can affect the performance of MySQL.  Would
> you please post the output of "dumpfs /var | head -n 21" on your server?
> (Replace /var with whatever mountpoint that your database is stored in).
>
as it's a default installation from ports, it's /var... and here we go:
rabauke_at_edgecrusher$ sudo dumpfs /var | head -n 21
magic   19540119 (UFS2) time    Wed Oct 26 13:12:16 2005
superblock location     65536   id      [ 4321ded2 d21e08fb ]
ncg     9       size    779776  blocks  753095
bsize   16384   shift   14      mask    0xffffc000
fsize   2048    shift   11      mask    0xfffff800
frag    8       shift   3       fsbtodb 2
minfree 8%      optim   time    symlinklen 120
maxbsize 16384  maxbpg  2048    maxcontig 8     contigsumsize 8
nbfree  90021   ndir    81      nifree  211666  nffree  272
bpg     11761   fpg     94088   ipg     23552
nindir  2048    inopb   64      maxfilesize     140806241583103
sbsize  2048    cgsize  16384   csaddr  3000    cssize  2048
sblkno  40      cblkno  48      iblkno  56      dblkno  3000
cgrotor 7       fmod    0       ronly   0       clean   0
avgfpdir 64     avgfilesize 16384
flags   soft-updates
fsmnt   /var
volname         swuid   0

cs[].cs_(nbfree,ndir,nifree,nffree):
         (11383,3,23547,20) (11389,12,23539,3) (11389,3,23546,2) 
(11388,14,23532,2)

If the output is too scrambled via Mail (guess I have to configure my 
thunderbird under Mac OS X...) take a look here:
http://unixoid.de/rabauke/freebsd/dumpfs.var.freebsd60rc1.txt

I guess rather download it. Somehow the Apache has a problem with this 
file, although file(1) tells me it's ASCII...



> Some observations that I think can affect MySQL performance:
>  - Our malloc(3) implementation might be sub-optimal for the workload.
>  - Lack of some POSIX system call which was implemented in other ways
> (e.g. we do not have fsyncdata yet)
>  - Some detailed implementation difference between our and Linux's
> threading library, or MySQL itself.
> 
The case of threading in FreeBSD vs. Linux was discussed in several 
MySQL Benchmark articles. Especially:
http://software.newsforge.com/article.pl?sid=04/12/27/1238216&tid=72
and
http://software.newsforge.com/article.pl?sid=04/12/27/1243207&from=rss

Just thought, maybe it's time to do this benchmark partly again, since 
6.0 shows at the door-step ;)

> I think David (davidxu_at_) had worked on performance of MySQL and he may
> have some suggestions.
> 
> BTW.  What file system are you using on your Debian test?  What's the
> mount options?
>
it was a plain Debian installation with just one mount point:
rabauke_at_edgecrusher:~$ mount
/dev/sdb1 on / type ext3 (rw,errors=remount-ro)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw)
usbfs on /proc/bus/usb type usbfs (rw)

thanks so far,
Marian