 | | | Subject: Re: corrupted query cache? | Subject: Re: corrupted query cache? 2007-11-01 - By Baron Schwartz
Back
What's your question?
Max Thayer wrote:
> We are experiencing a certain anomaly here on our db server. The
> problem had occurred about 3 weeks ago.
> We ran diagnostics on the hardware over a 48+hour period with no
> failures or indications of problems with hardware. ( at least the
> memory.) We rebooted the server, and had not experienced anymore issues
> until yesterday, it repeated itself.
>
> The issue is as follows....
> We run a series of views, sp's and user defined functions for a period
> of time. During application development we run these from command line,
> from MySQL query browser and from a series of PHP and Perl Scripts (of
> which some are executed from web apps others from cli ).
>
> At some point, the data returned to the user appears corrupted. Bogus
> data is returned, whether it be from a query to the actual table or to a
> view. Upon rebooting the server the data is returned to normal (ie. the
> data tables written to hdd are fine, it appears only the cached results
> are fubar).
>
> I can point out also if it matters that all my tables are INNODB....
>
> We are currently developing on a semi-production DB....( i know...hands
> need to be slapped )
>
> We are using Apache 2.2 on a similar BSD server. Server specs follow...
>
> ************************************************************************
> *************
> FreeBSD 6.1 Release #0
> mysql-server-5 (See http://ver-5.ora-code.com).0.45_1
> mysql-client-5 (See http://ent-5.ora-code.com).0.45_1
>
> ************************************************************************
> *************
> Hardware:
>
> Copyright (c) 1992-2006 The FreeBSD Project.
> Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994
> The Regents of the University of California. All rights
> reserved.
> FreeBSD 6.1-RELEASE #0: Sun May 7 04:42:56 UTC 2006
> root@(protected):/usr/obj/usr/src/sys/SMP
> Timecounter "i8254" frequency 1193182 Hz quality 0
> CPU: Intel(R) Xeon(TM) CPU 3.06GHz (3065.81-MHz 686-class CPU)
> Origin = "GenuineIntel" Id = 0xf29 Stepping = 9
>
> Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE
> ,MCA
> ,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
> Features2=0x4400<CNTX-ID,<b14>>
> Logical CPUs per core: 2
> real memory = 2146959360 (2047 MB)
> avail memory = 2095874048 (1998 MB)
> ACPI APIC Table: <PTLTD APIC >
> FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs cpu0 (BSP): APIC
> ID: 0
> cpu1 (AP): APIC ID: 1
> cpu2 (AP): APIC ID: 6
> cpu3 (AP): APIC ID: 7
> ioapic0 <Version 2.0> irqs 0-23 on motherboard
> ioapic1 <Version 2.0> irqs 24-47 on motherboard
> ioapic2 <Version 2.0> irqs 48-71 on motherboard
> ioapic3 <Version 2.0> irqs 72-95 on motherboard
> ioapic4 <Version 2.0> irqs 96-119 on motherboard
> kbd1 at kbdmux0
> acpi0: <PTLTD RSDT> on motherboard
> acpi0: Power Button (fixed)
> Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000
> acpi_timer0: <24-bit timer at 3.579545MHz> port 0x1008-0x100b on acpi0
> cpu0: <ACPI CPU> on acpi0
> cpu1: <ACPI CPU> on acpi0
> cpu2: <ACPI CPU> on acpi0
> cpu3: <ACPI CPU> on acpi0
> pcib0: <ACPI Host-PCI bridge> port 0xcf8-0xcff on acpi0
> pci0: <ACPI PCI bus> on pcib0
> pci0: <unknown> at device 0.1 (no driver attached)
> pcib1: <ACPI PCI-PCI bridge> at device 2.0 on pci0
> pci1: <ACPI PCI bus> on pcib1
> pci1: <base peripheral, interrupt controller> at device 28.0 (no driver
> attached)
> pcib2: <ACPI PCI-PCI bridge> at device 29.0 on pci1
> pci2: <ACPI PCI bus> on pcib2
> pci1: <base peripheral, interrupt controller> at device 30.0 (no driver
> attached)
> pcib3: <ACPI PCI-PCI bridge> at device 31.0 on pci1
> pci3: <ACPI PCI bus> on pcib3
> em0: <Intel(R) PRO/1000 Network Connection Version - 3.2.18> port
> 0x3000-0x303f mem 0xf0200000-0xf021ffff irq 28 at device 2.0 on pci3
> em0: Ethernet address: 00:30:48:2f:13:7c
> em1: <Intel(R) PRO/1000 Network Connection Version - 3.2.18> port
> 0x3040-0x307f mem 0xf0220000-0xf023ffff irq 29 at device 2.1 on pci3
> em1: Ethernet address: 00:30:48:2f:13:7d
> pcib4: <ACPI PCI-PCI bridge> at device 3.0 on pci0
> pci4: <ACPI PCI bus> on pcib4
> pci4: <base peripheral, interrupt controller> at device 28.0 (no driver
> attached)
> pcib5: <ACPI PCI-PCI bridge> at device 29.0 on pci4
> pci5: <ACPI PCI bus> on pcib5
> pci4: <base peripheral, interrupt controller> at device 30.0 (no driver
> attached)
> pcib6: <ACPI PCI-PCI bridge> at device 31.0 on pci4
> pci6: <ACPI PCI bus> on pcib6
> aac0: <Adaptec SCSI RAID 2020ZCR> mem
> 0xf1000000-0xf1ffffff,0xf0400000-0xf05fffff,0xf8000000-0xfbffffff irq 72
> at device 1.0 on pci6
> aac0: New comm. interface enabled
> aac0: Adaptec Raid Controller 2.0.0-1
> aacp0: <SCSI Passthrough Bus> on aac0
> aacp1: <SCSI Passthrough Bus> on aac0
> uhci0: <Intel 82801CA/CAM (ICH3) USB controller USB-A> port
> 0x2000-0x201f irq 16 at device 29.0 on pci0
> uhci0: [GIANT-LOCKED]
> usb0: <Intel 82801CA/CAM (ICH3) USB controller USB-A> on uhci0
> usb0: USB revision 1.0
> uhub0: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1
> uhub0: 2 ports with 2 removable, self powered
> uhci1: <Intel 82801CA/CAM (ICH3) USB controller USB-B> port
> 0x2020-0x203f irq 19 at device 29.1 on pci0
> uhci1: [GIANT-LOCKED]
> usb1: <Intel 82801CA/CAM (ICH3) USB controller USB-B> on uhci1
> usb1: USB revision 1.0
> uhub1: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1
> uhub1: 2 ports with 2 removable, self powered
> uhci2: <Intel 82801CA/CAM (ICH3) USB controller USB-C> port
> 0x2040-0x205f irq 18 at device 29.2 on pci0
> uhci2: [GIANT-LOCKED]
> usb2: <Intel 82801CA/CAM (ICH3) USB controller USB-C> on uhci2
> usb2: USB revision 1.0
> uhub2: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1
> uhub2: 2 ports with 2 removable, self powered
> pcib7: <ACPI PCI-PCI bridge> at device 30.0 on pci0
> pci7: <ACPI PCI bus> on pcib7
> pci7: <display, VGA> at device 1.0 (no driver attached)
> isab0: <PCI-ISA bridge> at device 31.0 on pci0
> isa0: <ISA bus> on isab0
> atapci0: <Intel ICH3 UDMA100 controller> port
> 0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0x2060-0x206f at device 31.1 on pci0
> ata0: <ATA channel 0> on atapci0
> ata1: <ATA channel 1> on atapci0
> pci0: <serial bus, SMBus> at device 31.3 (no driver attached)
> acpi_button0: <Power Button> on acpi0
> atkbdc0: <Keyboard controller (i8042)> port 0x60,0x64 irq 1 on acpi0
> atkbd0: <AT Keyboard> irq 1 on atkbdc0
> kbd0 at atkbd0
> atkbd0: [GIANT-LOCKED]
> psm0: <PS/2 Mouse> irq 12 on atkbdc0
> psm0: [GIANT-LOCKED]
> psm0: model IntelliMouse Explorer, device ID 4
> sio0: <16550A-compatible COM port> port 0x3f8-0x3ff irq 4 flags 0x10 on
> acpi0
> sio0: type 16550A
> sio1: <16550A-compatible COM port> port 0x2f8-0x2ff irq 3 on acpi0
> sio1: type 16550A
> fdc0: <floppy drive controller> port 0x3f0-0x3f5,0x3f7 irq 6 drq 2 on
> acpi0
> fdc0: [FAST]
> fd0: <1440-KB 3.5" drive> on fdc0 drive 0
> ppc0: <ECP parallel printer port> port 0x378-0x37f,0x778-0x77f irq 7 drq
> 3 on acpi0
> ppc0: SMC-like chipset (ECP/EPP/PS2/NIBBLE) in COMPATIBLE mode
> ppc0: FIFO with 16/16/9 bytes threshold
> ppbus0: <Parallel port bus> on ppc0
> plip0: <PLIP network interface> on ppbus0
> lpt0: <Printer> on ppbus0
> lpt0: Interrupt-driven port
> ppi0: <Parallel I/O> on ppbus0
> pmtimer0 on isa0
> orm0: <ISA Option ROMs> at iomem
> 0xc0000-0xc7fff,0xc8000-0xc8fff,0xc9000-0xcd7ff,0xe0000-0xe3fff on isa0
> sc0: <System console> at flags 0x100 on isa0
> sc0: VGA <16 virtual consoles, flags=0x300>
> vga0: <Generic ISA VGA> at port 0x3c0-0x3df iomem 0xa0000-0xbffff on
> isa0 Timecounters tick every 1.000 msec
> acd0: CDROM <CD-224E-N/1.AA> at ata1-master UDMA33
> aacd0: <RAID 1 (Mirror)> on aac0
> aacd0: 69974MB (143307008 sectors)
> ses0 at aacp0 bus 0 target 6 lun 0
> ses0: <SUPER GEM318 0> Fixed unknown SCSI-2 (See http://CSI-2.ora-code.com) device
> ses0: 3.300MB/s transfers
> ses0: SAF-TE Compliant Device
> pass0 at aacp0 bus 0 target 0 lun 0
> pass0: <SEAGATE ST373454LC 0003> Fixed unknown SCSI-3 (See http://CSI-3.ora-code.com) device
> pass0: 3.300MB/s transfers
> pass1 at aacp0 bus 0 target 1 lun 0
> pass1: <SEAGATE ST373454LC 0003> Fixed unknown SCSI-3 (See http://CSI-3.ora-code.com) device
> pass1: 3.300MB/s transfers
> SMP: AP CPU #3 Launched!
> SMP: AP CPU #1 Launched!
> SMP: AP CPU #2 Launched!
> em0: link state changed to UP
>
> ************************************************************************
> ******************
> my.cnf
> ************************************************************************
> ******************
> [mysqld]
> port = 3306
> socket = /tmp/mysql.sock
> back_log = 50
> #skip-networking
> max_connections = 200
> max_connect_errors = 10
> table_cache = 2048
> #external-locking
> max_allowed_packet = 16M
> binlog_cache_size = 1M
> max_heap_table_size = 64M
> sort_buffer_size = 8M
> join_buffer_size = 8M
> thread_cache_size = 8
> thread_concurrency = 8
> query_cache_size = 64M
> query_cache_limit = 2M
> ft_min_word_len = 4
> #memlock
> default_table_type = INNODB
> thread_stack = 192K
> transaction_isolation = SERIALIZABLE
> tmp_table_size = 64M
> log-bin=mysql-bin
> #log_slave_updates
> #log
> #log_warnings
> log_slow_queries
> long_query_time = 2
> log_long_format
> #tmpdir = /tmp
> server-id = 1
> #server-id = 2
> #master-host = <hostname>
> #master-user = <username>
> #master-password = <password>
> #master-port = <port>
> #read_only
> key_buffer_size = 32M
> read_buffer_size = 2M
> read_rnd_buffer_size = 16M
> bulk_insert_buffer_size = 64M
> myisam_sort_buffer_size = 128M
> myisam_max_sort_file_size = 10G
> myisam_max_extra_sort_file_size = 10G
> myisam_repair_threads = 1
> myisam_recover
>
> skip-bdb
>
> innodb_additional_mem_pool_size = 16M
> innodb_buffer_pool_size = 1G
> innodb_data_file_path = ibdata1:10M:autoextend
> #innodb_data_home_dir = /usr/local/mysql/
> innodb_file_io_threads = 4
> #innodb_force_recovery=1
> innodb_thread_concurrency = 16
> innodb_flush_log_at_trx_commit = 1
> #innodb_fast_shutdown
> innodb_log_buffer_size = 8M
> innodb_log_file_size = 256M
> innodb_log_files_in_group = 3
> #innodb_log_group_home_dir
> innodb_max_dirty_pages_pct = 90
> #innodb_flush_method=O_DSYNC
> innodb_lock_wait_timeout = 120
>
> [mysqldump]
> quick
> max_allowed_packet = 16M
> [mysql]
> no-auto-rehash
> [isamchk]
> key_buffer = 512M
> sort_buffer_size = 512M
> read_buffer = 8M
> write_buffer = 8M
> [myisamchk]
> key_buffer = 512M
> sort_buffer_size = 512M
> read_buffer = 8M
> write_buffer = 8M
> [mysqlhotcopy]
> interactive-timeout
> [mysqld_safe]
> open-files-limit = 8192
>
--
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|