6.2 Stable + Mysql 5.0 poor performance
Cheffo
cheffo at FreeBSD-BG.org
Mon May 14 10:10:18 UTC 2007
Hello,
Michael K. Smith - Adhost wrote:
> Hello Taseff:
>
>> -----Original Message-----
>> From: owner-freebsd-performance at freebsd.org [mailto:owner-freebsd-
>> performance at freebsd.org] On Behalf Of ivo tasev
>> Sent: Friday, May 11, 2007 2:02 AM
>> To: freebsd-performance at freebsd.org
>> Subject: 6.2 Stable + Mysql 5.0 poor performance
>>
>> Hi all,
>> i have machine with 2 x Quad Core Xeon cpu`s 2G ram and 4 3ware disks
>> in
>> raid10. There is 6.2 Stable on it and mysql 5.0 from the ports.
>>
>> The mysql is installed with the following build options:
>> WITH_XCHARSET=all WITH_OPENSSL=yes BUILD_OPTIMIZED=yes
> WITH_ARCHIVE=yes
>> WITH_FEDERATED=yes WITH_NDB=yes
>>
>> I`m using libmap.conf with this in it:
>>
>> [mysqld]
>> libpthread.so.2 libthr.so.2
>>
>> This is what I have in my.cnf :
>>
>> set-variable = key_buffer=1024M
>> set-variable = max_allowed_packet=64M
>> set-variable = thread_stack=1024K
>> set-variable = read_buffer_size=2M
>> set-variable = read_buffer_size=2M
>>
>> set-variable = max_connections=350
>> set-variable = interactive_timeout=100
>> set-variable = wait_timeout=120
>> set-variable = max_user_connections=340
>>
>> set-variable = query_cache_limit=1M
>> set-variable = query_cache_size=32M
>> set-variable = query_cache_type=1
>>
>> set-variable = table_cache=1024
>> set-variable = thread_cache=128
>> set-variable = thread_cache_size=40
>> set-variable = thread_concurrency=16
>>
>> I`m performing the following test with super-smack:
>> :~# time super-smack -d mysql
>> /usr/local/share/super-smack/select-key.smack 10 10000
>>
>> the results are:Query Barrel Report for client smacker1
>> connect: max=1ms min=0ms avg= 0ms from 10 clients
>> Query_type num_queries max_time min_time
> q_per_s
>> select_index 200000 0 0 18599.71
>>
>> I can achieve better performance even on my colleague`s notebook with
>> 6.2 stable !?
>>
>> I`ll be very thankful if someone can give me any ideas:)
>>
> Here are some system tweaks that may help.
>
> /etc/sysctl.conf
>
> kern.threads.max_groups_per_proc=40000
> kern.threads.max_threads_per_proc=40000
> kern.maxfiles=65535
> kern.maxfilesperproc=65535
>
> /etc/libmap.conf
>
> [mysqld]
> libpthread.so.2 libthr.so.2
> libpthread.so libthr.so
>
> /boot/loader.conf
>
> kern.maxdsiz="1073741824" # 1GB
> kern.dfldsiz="1073741824" # 1GB
> kern.maxssiz="134217728" # 128MB
>
> Mike
This doesn't change a thing for me.
What I can't understand is how this is possible:
On my laptop - CPU: Intel(R) Core(TM)2 CPU T7200 @ 2.00GHz
(1995.02-MHz K8-class CPU)
avail memory = 1024991232 (977 MB)
FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
ad4: 76319MB <Seagate ST980825AS 3.12> at ata2-master SATA150
FreeBSD hater.cmotd.com 6.2-STABLE FreeBSD 6.2-STABLE #6: Wed Apr 25
14:27:07 EEST 2007
root at hater.cmotd.com:/usr/obj/usr/src/sys/CORE64-SMP amd64
I have started KDE and thunderbird mail client during tests:
Test :
for i in 1 2 3 4 5; do super-smack -d mysql
/usr/local/share/super-smack/select-key.smack 10 1000 | grep
select_index; done
1)Default settings:
select_index 20000 0 0 12895.14
select_index 20000 0 0 12782.88
select_index 20000 0 0 12769.19
select_index 20000 1 0 12645.70
select_index 20000 0 0 12772.67
2)kern.timecounter.hardware: ACPI-fast -> TSC
select_index 20000 0 0 14629.49
select_index 20000 1 0 14826.03
select_index 20000 1 0 14789.27
select_index 20000 0 0 15157.29
select_index 20000 0 0 14494.76
3)Changing /etc/libmap.conf to:
[mysqld]
libpthread.so.2 libthr.so.2
libpthread.so libthr.so
select_index 20000 0 0 19290.25
select_index 20000 0 0 18872.48
select_index 20000 0 0 19399.30
select_index 20000 0 0 19038.62
select_index 20000 0 0 19480.00
4)cp /usr/local/share/mysql/my-large.cnf /var/db/mysql/my.cnf
select_index 20000 0 0 35112.42
select_index 20000 0 0 33295.93
select_index 20000 0 0 33616.33
select_index 20000 1 0 33415.43
select_index 20000 0 0 33200.31
So the results seems ok for me and quite impressive :) but look at this:
CPU: Intel(R) Xeon(R) CPU 5130 @ 2.00GHz (2002.99-MHz
K8-class CPU)
avail memory = 8265285632 (7882 MB)
FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs
da0 at arcmsr0 bus 0 target 0 lun 0
da0: <Areca ARC-1110-VOL#00 R001> Fixed Direct Access SCSI-5 device
da0: 166.666MB/s transfers (83.333MHz, offset 32, 16bit), Tagged
Queueing Enabled
da0: 610351MB (1249999872 512 byte sectors: 255H 63S/T 77808C)
(RAID 10 4x160GB SATA)
FreeBSD tiger.cmotd.com 6.2-STABLE FreeBSD 6.2-STABLE #5: Mon May 14
17:12:39 EEST 2007
root at tiger.cmotd.com:/usr/obj/usr/src/sys/CORE64-SMP amd64
1)Default settings:
select_index 20000 1 0 8075.95
select_index 20000 1 0 8111.38
select_index 20000 1 0 8329.87
select_index 20000 1 0 8105.15
select_index 20000 1 0 8108.48
2)kern.timecounter.hardware: HPET -> TSC
select_index 20000 1 0 8927.00
select_index 20000 1 0 8978.22
select_index 20000 1 0 9076.20
select_index 20000 1 0 9043.15
select_index 20000 1 0 9158.74
3)Changing /etc/libmap.conf
select_index 20000 0 0 35888.48
select_index 20000 0 0 38044.08
select_index 20000 0 0 37984.83
select_index 20000 0 0 37763.64
select_index 20000 0 0 37799.25
4)cp /usr/local/share/mysql/my-large.cnf /var/db/mysql/my.cnf
select_index 20000 0 0 40816.66
select_index 20000 0 0 39808.36
select_index 20000 0 0 39508.28
select_index 20000 0 0 38147.48
select_index 20000 0 0 38002.08
As you can see in 1) & 2) my laptop outperform server that should be at
least twice more powerful.
and the final results 4) - ~33K tps vs ~39 tps ... is little
disappointing ( have in mind on my laptop during benchmarks I have
working Xorg, KDE, Thunderbird, skype and few others not so greedy apps)
When I have little more time I'll rerun the bench without X started.
And here is the diff -urN between 2 kernels:
--- CORE64-SMP Mon May 14 12:52:41 2007 (laptop 2 cores)
+++ CORE64-SMP-TIGER Mon May 14 12:52:43 2007 (4 cores)
-options ACCEPT_FILTER_HTTP
-options ACCEPT_FILTER_DATA
-options COMPAT_FREEBSD4 # Compatible with FreeBSD4
-options COMPAT_FREEBSD5 # Compatible with FreeBSD5
-device ataraid # ATA RAID drives
-device scbus # SCSI bus (required for SCSI)
-device da # Direct Access (disks)
-device cd # CD
-device pass # Passthrough device (direct SCSI access)
-device ses # SCSI Environmental Services (and SAF-TE)
+device scbus # SCSI bus (required for SCSI)
+device da # Direct Access (disks)
+device pass # Passthrough device (direct SCSI access)
+device arcmsr # Areca SATA II RAID
-device cbb # cardbus (yenta) bridge
-device pccard # PC Card (16-bit) bus
-device cardbus # CardBus (32-bit) bus
-device miibus # MII bus support
-device bge # Broadcom BCM570xx Gigabit Ethernet
-device wlan # 802.11 support
-device wlan_wep # 802.11 WEP support
-device wlan_ccmp # 802.11 CCMP support
-device wlan_tkip # 802.11 TKIP support
-device ath # Atheros pci/cardbus NIC's
-device ath_hal # Atheros HAL (Hardware Access Layer)
-device ath_rate_sample # SampleRate tx rate control for ath
-device firmware
+device em # Intel PRO/1000 adapter Gigabit -device
-device sbp # SCSI over FireWire (Requires
scbus and da)
-device fwe # Ethernet over FireWire (non-standard!)
#addons
-device pf
-device pflog
-device pfsync
-device carp
-options IPSTEALTH
-options NETSMB
-options NETSMBCRYPTO
-options SMBFS
-options LIBMCHAIN
-options LIBICONV
+device drm
+device radeondrm
Do you need more info?
--
Best Wishes,
Stefan Lambrev
ICQ# 24134177
More information about the freebsd-performance
mailing list