Date: Fri, 21 Feb 2003 07:10:10 -0800 (PST) From: "HiTech Creations Support" <htsupp@hitechcreations.com> To: freebsd-bugs@FreeBSD.org Subject: Re: kern/39878: mysqld process suddenly runs at 99% CPU without load, and a restart of mysqld is required. Message-ID: <200302211510.h1LFAApA032727@freefall.freebsd.org>
next in thread | raw e-mail | index | archive | help
The following reply was made to PR kern/39878; it has been noted by GNATS. From: "HiTech Creations Support" <htsupp@hitechcreations.com> To: <freebsd-gnats-submit@FreeBSD.org>, <paul@vms-keytrade.com> Cc: Subject: Re: kern/39878: mysqld process suddenly runs at 99% CPU without load, and a restart of mysqld is required. Date: Fri, 21 Feb 2003 09:08:18 -0600 We have the same problem, using either MySQL versions 3.23.52 or 3.23.55, running on 4.6-STABLE. Our run-away MySQL process occurs under high load conditions. Here are some details on our configuration: Hardware Intel E7500 based motherboard Dual Xeon P4's @ 2.4Ghz w/HyperThreading 2GB Memory (4x256MB DDR DIMM modules) Dual Intel 82555 Ethernet controllers Adaptec 3940N SCSI Host Adapter Seagate 72GB Cheetah LVD SCSI Drive Plextor Wide SCSI drive ATI RageXL video controller Our kernel is compiled with the following configuration file: # # GENERIC -- Generic kernel configuration file for FreeBSD/i386 # # For more information on this file, please read the handbook section on # Kernel Configuration Files: # # http://www.FreeBSD.org/handbook/kernelconfig-config.html # # The handbook is also available locally in /usr/share/doc/handbook # if you've installed the doc distribution, otherwise always see the # FreeBSD World Wide Web server (http://www.FreeBSD.org/) for the # latest information. # # An exhaustive list of options and more detailed explanations of the # device lines is also present in the ./LINT configuration file. If you are # in doubt as to the purpose or necessity of a line, check first in LINT. # # $FreeBSD: src/sys/i386/conf/GENERIC,v 1.246.2.43 2002/05/23 17:04:01 obrien Exp $ machine i386 # cpu I386_CPU # cpu I486_CPU # cpu I586_CPU cpu I686_CPU ident MYKERNEL maxusers 0 makeoptions CONF_CFLAGS=-fno-builtin #Don't allow use of memcmp, etc. #makeoptions DEBUG=-g #Build kernel with gdb(1) debug symbols options CPU_ENABLE_SSE #enables SSE/MMX2 instructions support. options CPU_FASTER_5X86_FPU #enables faster FPU exception handler. options NO_F00F_HACK #disables the hack that Pentiums have options INET #InterNETworking options FFS #Berkeley Fast Filesystem options FFS_ROOT #FFS usable as root device [keep this!] options SOFTUPDATES #Enable FFS soft updates support options UFS_DIRHASH #Improve performance on big directories options MFS #Memory Filesystem options MD_ROOT #MD is a potential root device options CD9660 #ISO 9660 Filesystem options CD9660_ROOT #CD-ROM usable as root, CD9660 required options PROCFS #Process filesystem options COMPAT_43 #Compatible with BSD 4.3 [KEEP THIS!] options SCSI_DELAY=5000 #Delay (in ms) before probing SCSI options UCONSOLE #Allow users to grab the console options USERCONFIG #boot -c editor options VISUAL_USERCONFIG #visual boot -c editor options KTRACE #ktrace(1) support options SYSVSHM #SYSV-style shared memory options SYSVMSG #SYSV-style message queues options SYSVSEM #SYSV-style semaphores options P1003_1B #Posix P1003_1B real-time extensions options _KPOSIX_PRIORITY_SCHEDULING options ICMP_BANDLIM #Rate limit bad replies options KBD_INSTALL_CDEV # install a CDEV entry in /dev # To make an SMP kernel, the next two are needed options SMP # Symmetric MultiProcessor Kernel options APIC_IO # Symmetric (APIC) I/O # options SCSI_DELAY=15000 #Delay (in ms) before probing SCSI # options MATH_EMULATE #Support for x87 emulation # options INET6 #IPv6 communications protocols # options NFS #Network Filesystem # options NFS_ROOT #NFS usable as root device, NFS required # options MSDOSFS #MSDOS Filesystem device isa # device eisa device pci # Floppy drives device fdc0 at isa? port IO_FD1 irq 6 drq 2 device fd0 at fdc0 drive 0 device fd1 at fdc0 drive 1 # # If you have a Toshiba Libretto with its Y-E Data PCMCIA floppy, # don't use the above line for fdc0 but the following one: #device fdc0 # ATA and ATAPI devices # device ata0 at isa? port IO_WD1 irq 14 # device ata1 at isa? port IO_WD2 irq 15 # device ata # device atadisk # ATA disk drives # device atapicd # ATAPI CDROM drives # device atapifd # ATAPI floppy drives # device atapist # ATAPI tape drives options ATA_STATIC_ID #Static device numbering # SCSI Controllers # device ahb # EISA AHA1742 family device ahc # AHA2940 and onboard AIC7xxx devices # device amd # AMD 53C974 (Tekram DC-390(T)) # device isp # Qlogic family # device ncr # NCR/Symbios Logic # device sym # NCR/Symbios Logic (newer chipsets) # options SYM_SETUP_LP_PROBE_MAP=0x40 # Allow ncr to attach legacy NCR devices when # both sym and ncr are configured # device adv0 at isa? # device adw # device bt0 at isa? # device aha0 at isa? # device aic0 at isa? # device ncv # NCR 53C500 # device nsp # Workbit Ninja SCSI-3 # device stg # TMC 18C30/18C50 # SCSI peripherals device scbus # SCSI bus (required) device da # Direct Access (disks) # device sa # Sequential Access (tape etc) device cd # CD device pass # Passthrough device (direct SCSI access) # RAID controllers interfaced to the SCSI subsystem # device asr # DPT SmartRAID V, VI and Adaptec SCSI RAID # device dpt # DPT Smartcache - See LINT for options! # device iir # Intel Integrated RAID # device mly # Mylex AcceleRAID/eXtremeRAID # device ciss # Compaq SmartRAID 5* series # RAID controllers # device aac # Adaptec FSA RAID, Dell PERC2/PERC3 # device aacp # SCSI passthrough for aac (requires CAM) # device ida # Compaq Smart RAID # device amr # AMI MegaRAID # device mlx # Mylex DAC960 family # device twe # 3ware Escalade # atkbdc0 controls both the keyboard and the PS/2 mouse device atkbdc0 at isa? port IO_KBD device atkbd0 at atkbdc? irq 1 flags 0x1 # device psm0 at atkbdc? irq 12 device vga0 at isa? # splash screen/screen saver pseudo-device splash # syscons is the default console driver, resembling an SCO console device sc0 at isa? flags 0x100 # Enable this and PCVT_FREEBSD for pcvt vt220 compatible console driver #device vt0 at isa? #options XSERVER # support for X server on a vt console #options FAT_CURSOR # start with block cursor # If you have a ThinkPAD, uncomment this along with the rest of the PCVT lines #options PCVT_SCANSET=2 # IBM keyboards are non-std # Floating point support - do not disable. device npx0 at nexus? port IO_NPX irq 13 # Power management support (see LINT for more options) # device apm0 at nexus? disable flags 0x20 # Advanced Power Management # PCCARD (PCMCIA) support # device card # device pcic0 at isa? irq 0 port 0x3e0 iomem 0xd0000 # device pcic1 at isa? irq 0 port 0x3e2 iomem 0xd4000 disable # Serial (COM) ports device sio0 at isa? port IO_COM1 flags 0x10 irq 4 device sio1 at isa? port IO_COM2 irq 3 # device sio2 at isa? disable port IO_COM3 irq 5 # device sio3 at isa? disable port IO_COM4 irq 9 # Parallel port # device ppc0 at isa? irq 7 # device ppbus # Parallel port bus (required) # device lpt # Printer # device plip # TCP/IP over parallel # device ppi # Parallel port interface device #device vpo # Requires scbus and da # PCI Ethernet NICs. # device de # DEC/Intel DC21x4x (``Tulip'') # device em # Intel PRO/1000 adapter Gigabit Ethernet Card (``Wiseman'') # device txp # 3Com 3cR990 (``Typhoon'') # device vx # 3Com 3c590, 3c595 (``Vortex'') # PCI Ethernet NICs that use the common MII bus controller code. # NOTE: Be sure to keep the 'device miibus' line in order to use these NICs! device miibus # MII bus support # device dc # DEC/Intel 21143 and various workalikes device fxp # Intel EtherExpress PRO/100B (82557, 82558) # device pcn # AMD Am79C97x PCI 10/100 NICs # device rl # RealTek 8129/8139 # device sf # Adaptec AIC-6915 (``Starfire'') # device sis # Silicon Integrated Systems SiS 900/SiS 7016 # device ste # Sundance ST201 (D-Link DFE-550TX) # device tl # Texas Instruments ThunderLAN # device tx # SMC EtherPower II (83c170 ``EPIC'') # device vr # VIA Rhine, Rhine II # device wb # Winbond W89C840F # device xl # 3Com 3c90x (``Boomerang'', ``Cyclone'') # device bge # Broadcom BCM570x (``Tigon III'') # ISA Ethernet NICs. # 'device ed' requires 'device miibus' # device ed0 at isa? port 0x280 irq 10 iomem 0xd8000 # device ex # device ep # device fe0 at isa? port 0x300 # Xircom Ethernet # device xe # PRISM I IEEE 802.11b wireless NIC. # device awi # WaveLAN/IEEE 802.11 wireless NICs. Note: the WaveLAN/IEEE really # exists only as a PCMCIA device, so there is no ISA attachment needed # and resources will always be dynamically assigned by the pccard code. # device wi # Aironet 4500/4800 802.11 wireless NICs. Note: the declaration below will # work for PCMCIA and PCI cards, as well as ISA cards set to ISA PnP # mode (the factory default). If you set the switches on your ISA # card for a manually chosen I/O address and IRQ, you must specify # those parameters here. # device an # The probe order of these is presently determined by i386/isa/isa_compat.c. # device ie0 at isa? port 0x300 irq 10 iomem 0xd0000 #device le0 at isa? port 0x300 irq 5 iomem 0xd0000 # device lnc0 at isa? port 0x280 irq 10 drq 0 # device cs0 at isa? port 0x300 # device sn0 at isa? port 0x300 irq 10 # Pseudo devices - the number indicates how many units to allocate. pseudo-device loop # Network loopback pseudo-device ether # Ethernet support # pseudo-device sl 1 # Kernel SLIP # pseudo-device ppp 1 # Kernel PPP # pseudo-device tun # Packet tunnel. pseudo-device pty # Pseudo-ttys (telnet etc) pseudo-device md # Memory "disks" # pseudo-device gif # IPv6 and IPv4 tunneling # pseudo-device faith 1 # IPv6-to-IPv4 relaying (translation) # The `bpf' pseudo-device enables the Berkeley Packet Filter. # Be aware of the administrative consequences of enabling this! # pseudo-device bpf #Berkeley packet filter # USB support # device uhci # UHCI PCI->USB interface # device ohci # OHCI PCI->USB interface # device usb # USB Bus (required) # device ugen # Generic # device uhid # "Human Interface Devices" # device ukbd # Keyboard # device ulpt # Printer # device umass # Disks/Mass storage - Requires scbus and da # device ums # Mouse # device uscanner # Scanners # device urio # Diamond Rio MP3 Player # USB Ethernet, requires mii # device aue # ADMtek USB ethernet # device cue # CATC USB ethernet # device kue # Kawasaki LSI USB ethernet #options IPFIREWALL #firewall #options IPFIREWALL_VERBOSE #enable logging to syslogd(8) #options IPFIREWALL_FORWARD #enable transparent proxy support #options IPFIREWALL_VERBOSE_LIMIT=100 #limit verbosity #options IPFIREWALL_DEFAULT_TO_ACCEPT #allow everything by default #options IPV6FIREWALL #firewall for IPv6 #options IPV6FIREWALL_VERBOSE #options IPV6FIREWALL_VERBOSE_LIMIT=100 #options IPV6FIREWALL_DEFAULT_TO_ACCEPT #options IPDIVERT #divert sockets #options IPFILTER #ipfilter support #options IPFILTER_LOG #ipfilter logging #options IPFILTER_DEFAULT_BLOCK #block all packets by default #options IPSTEALTH #support for stealth forwarding #options TCPDEBUG # # These three options provide support for System V Interface # Definition-style interprocess communication, in the form of shared # memory, semaphores, and message queues, respectively. # # System V shared memory and tunable parameters options SYSVSHM # include support for shared memory options SHMMAXPGS=16385 # max amount of shared memory pages (4k on i386) options SHMALL=31250000 # max amount of shared memory (bytes) #options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)" # max shared memory segment size (bytes) options SHMMIN=200 # min shared memory segment size (bytes) options SHMMNI=200 # max number of shared memory identifiers options SHMSEG=200 # max shared memory segments per process options ICMP_BANDLIM ##################################################################### # CLOCK OPTIONS # The granularity of operation is controlled by the kernel option HZ whose # default value (100) means a granularity of 10ms (1s/HZ). # Some subsystems, such as DUMMYNET or DEVICE_POLLING, might benefit from # a smaller granularity such as 1ms or less. # Consider, however, that reducing the granularity too much might # cause excessive overhead in clock interrupt processing, # potentially causing ticks to be missed and thus actually reducing # the accuracy of operation. # options HZ=100 default options HZ=1000 # DEVICE_POLLING adds support for mixed interrupt-polling handling # of network device drivers, which has significant benefits in terms # of robustness to overloads and responsivity, as well as permitting # accurate scheduling of the CPU time between kernel network processing # and other activities. The drawback is a moderate (up to 1/HZ seconds) # potential increase in response times. # It is strongly recommended to use HZ=1000 or 2000 with DEVICE_POLLING # to achieve smoother behaviour. # Additionally, you can enable/disable polling at runtime with the # sysctl variable kern.polling.enable (defaults off), and select # the CPU fraction reserved to userland with the sysctl variable # kern.polling.user_frac (default 50, range 0..100). # # Only the "dc" "fxp" and "sis" devices support this mode of operation at # the time of this writing. # not compatible with SMP operations # options DEVICE_POLLING # Set the size of the mbuf KVM reservation, in clusters. This is scaled # by approximately 2048 bytes. The system will auto-size the mbuf area # to (512 + maxusers*16) if this option is not specified. # maxusers is in turn computed at boot time depending on available memory # or set to the value specified by "options MAXUSERS=x" (x=0 means # autoscaling). # So, to take advantage of autoscaling, you have to remove both # NMBCLUSTERS and MAXUSERS (and NMBUFS) from your kernel config. # #options NMBCLUSTERS=1024 default options NMBCLUSTERS=16384 #options NMBUFS=4096 default options NMBUFS=65536 Our MySQL build file for 3.23.55 is: CC=gcc CFLAGS="-O2 -fno-strength-reduce -mpentiumpro -march=pentiumpro" \ CXX=gcc CXXFLAGS="-O2 -mpentiumpro -march=pentiumpro -fno-rtti -fno-exceptions -feli de-constructors -fno-strength-reduce" \ ./configure --prefix=/usr/xxx/mysql --exec-prefix=/usr/local --without-debug --enable-assembler#!/bin/sh We start MySQL as follows from a startup script. /usr/local/bin/safe_mysqld -O max_connections=900 -O key_buffer=256M -O max_allowed_packet=1M -O table_cache=256 -O sort_buffer=1M -O record_buffer=1M -O myisam_sort_buffer_size=64M -O thread_cache=8 -O thread_concurrency=8 & In our environment we run MySQL with the bulk of the connections running on localhost. In our high load conditions, we reach about 600-700 socket connections to MySQL. We have tried on different servers of the same configuration and still have the same problem. Typically the run-away MySQL process will happen during our high peak hour (around 10:00pm CST). MySQL still continues to operate, but the bottleneck is very severe. The only fix we have to to reset MySQL, which resets all our clients. We have tried running optimizations on the database, which made no difference. The extended-status from MySQL appears quite normal. We are not running out of socket nor file descriptors and have not loaded the server enough to cause any swapping. Our clients connecting to the MySQL server do use a high amount of floating point operations and conversions. But the data itself, is very small. Once the client connects, we hold the connection open for the duration of the link. So this yeilds one open/close per client. At this point in time, we do not have any idea where the problem may lie, and are willing to entertain any help/suggestions. To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-bugs" in the body of the message
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200302211510.h1LFAApA032727>