From owner-freebsd-performance@FreeBSD.ORG Mon May 14 10:10:18 2007 Return-Path: X-Original-To: freebsd-performance@freebsd.org Delivered-To: freebsd-performance@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 76C8D16A404 for ; Mon, 14 May 2007 10:10:18 +0000 (UTC) (envelope-from cheffo@FreeBSD-BG.org) Received: from blah.sun-fish.com (blah.sun-fish.com [217.18.249.150]) by mx1.freebsd.org (Postfix) with ESMTP id D552C13C4B0 for ; Mon, 14 May 2007 10:10:15 +0000 (UTC) (envelope-from cheffo@FreeBSD-BG.org) Received: from blah.sun-fish.com (localhost [127.0.0.1]) by blah.sun-fish.com (Postfix) with ESMTP id 616D21B10EA4; Mon, 14 May 2007 12:10:14 +0200 (CEST) Received: from hater.cmotd.com (hater.cmotd.com [192.168.3.125]) by blah.sun-fish.com (Postfix) with ESMTP id 4064E1B10C26; Mon, 14 May 2007 12:10:12 +0200 (CEST) Message-ID: <46483584.70608@FreeBSD-BG.org> Date: Mon, 14 May 2007 13:10:12 +0300 From: Cheffo User-Agent: Thunderbird 2.0.0.0 (X11/20070425) MIME-Version: 1.0 To: "Michael K. Smith - Adhost" References: <46443103.9010008@otel.net> <17838240D9A5544AAA5FF95F8D52031602018EFA@ad-exh01.adhost.lan> In-Reply-To: <17838240D9A5544AAA5FF95F8D52031602018EFA@ad-exh01.adhost.lan> Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP on BLAH Cc: freebsd-performance@freebsd.org, ivo tasev Subject: Re: 6.2 Stable + Mysql 5.0 poor performance X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 14 May 2007 10:10:18 -0000 Hello, Michael K. Smith - Adhost wrote: > Hello Taseff: > >> -----Original Message----- >> From: owner-freebsd-performance@freebsd.org [mailto:owner-freebsd- >> performance@freebsd.org] On Behalf Of ivo tasev >> Sent: Friday, May 11, 2007 2:02 AM >> To: freebsd-performance@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 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@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: 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@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