Hi, I'm running MySQL 3.22.32 on Slackware Linux (not sure about the exact configuration or kernel - don't have telnet access to the machine at the moment; apologies). I have three tables: * domains - a large monolithic table with 1 primary key and 3 other keys, 43 fields in total, 1344 bytes plus 11 date fields (can't remember how long they are at the moment). There are slightly over 500,000 rows in this table. It's fine. * services - a much smaller table (108 bytes, plus a date and an enum) with 3 keys and just over 1 million rows * servicedata - a slightly larger table (256 bytes) with one key and about 4.5 millions rows. (It used to have two keys, but when we rebuilt the indices we only rebuilt one of them) A common key "domain", a varchar(100), is used to join all three of these services; in addition, a second key service (mediumint(8) unsigned) joins services and servicedata. As I don't have access to the machine, I can't tell you how big the indexes actually get. As mentioned above, I don't know the Linux kernel version either, although I'm pretty sure it's not a multi-processor box. I'll try and get additional details soon. Edited highlights of show variables: +----------------------------+---------------------------------+ | delayed_queue_size | 1000 | | join_buffer | 131072 | | flush_time | 0 | | key_buffer | 33550336 | | log | ON | | log_update | ON | | long_query_time | 10 | | low_priority_updates | OFF | | max_allowed_packet | 1048576 | | max_connections | 200 | | max_connect_errors | 10 | | max_delayed_insert_threads | 20 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_write_lock_count | 4294967295 | | net_buffer_length | 16384 | | protocol_version | 10 | | record_buffer | 2093056 | | skip_locking | ON | | skip_networking | OFF | | sort_buffer | 8388600 | | table_cache | 512 | | thread_stack | 65536 | | tmp_table_size | 1048576 | | version | 3.22.32-log | +----------------------------+---------------------------------+ We started seeing corruption in the indexes last weekend, after weeks of using these tables in their current format, but suspiciously close to an extra index being added to the domains table. The symptoms were as follows: * If I queried the domains table on one of the keys (e.g. select * from domains where domain=blah), I got nothing. If, however, I queried on another non- indexed key, I could find the data perfectly fine. A simple count(*) produced the expected number of records (roughly - we get about 300-400 extra records every day and the numbers looked right). * Some queries joining two tables returned nothing at all. isamchk -r seemed to fix that - for a while * Slightly afterwards, a query that joined domains and servicedata produced significantly fewer records than expected - 30,000 rather than 40,000. Tests revealed that the same join, but between domains and services, produced the right results, so something was obviously wrong with servicedata We've run isamchk -r a few times, and that seems to fix things temporarily, but not for long. We ran isamchk -o last night on servicedata (but not services), and later on dropped the index on servicedata and rebuilt it; this unfortunately caused the database machine to thrash like a mad thing (load of 100 or so) and use up all the memory. Today, I noticed the odd example (perhaps 1% of the time at most) of a record in the services table being retrieved by one index key but not another. isamchk -o also produced the following error (on servicedata) Found block with impossible length 3021071631 at 200151825; Skipped Any ideas on what's going on? And how to fix it? The machine has, I believe, 1Gb RAM, of which only 50-100Mb is normally being used (not sure about peaks); would increasing the size of the join, sort or others buffers do any good? Sam -- Home page: http://www.illuminated.co.uk/ Convert ZIPs to Mac format: http://www.illuminated.co.uk/macifyzip/ In Nomine Cookbook: http://www.illuminated.co.uk/innomine/ Chances are if your parents didn't have children then you won't either. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
