 | | | Subject: Re: corrupted query cache? | Subject: Re: corrupted query cache? 2007-11-02 - By Baron Schwartz
Back
Hi Max,
Max Thayer wrote:
>> What's your question?
>
> Heh, yah that might be important...
> Has anyone experienced this? Does it look like anything in the my.cnf
> could be altered to alleviate or assist me in diagnosing the problem? I
> do not get an indication of any problems in the log files or the
> bin-logs.
> How might I efficiently trouble shooting this, where might I begin? I
> can't figure out how to replicate it, it seems to be quite sporadic.
> What would corrupt query results, temporarily fixed with a mysqld
> restart?
If it's the query cache, there are two relatively simple things you can
do: when you get a corrupt result, change the query slightly so it's not
a cache hit, and retry. You can also flush or disable the query cache.
I don't see anything wrong with the config, though I'm curious about
SERIALIZABLE isolation level; it's not something I see often. It
shouldn't have anything to do with this.
Baron
>
> 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)
|
|
|