| Bug #26759 | LOAD INDEX INTO CACHE breaks symlinks | ||
|---|---|---|---|
| Submitted: | 1 Mar 2007 18:10 | Modified: | 19 Jul 2007 16:12 |
| Reporter: | Klaus Halfmann | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.27 icc-compiled | OS: | Linux (SLES 10 - 64bit) |
| Assigned to: | CPU Architecture: | Any | |
[2 Mar 2007 12:16]
Sveta Smirnova
Thank you for the report. I can not repeat it in virtual environment. Please provide output of SHOW TABLE STATUS LIKE 'A_B', your configuration file and information about your hardware such as RAM, CPU.
[2 Mar 2007 12:25]
Klaus Halfmann
(It took me almost 24-Hours to fill the database with demodata
So it may feel a bit different now ....(
mysql> SHOW TABLE STATUS LIKE 'A_B' \G
*************************** 1. row ***************************
Name: A_B
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 107994840
Avg_row_length: 22
Data_length: 2375886480
Max_data_length: 6192449487634431
Index_length: 2051699712
Data_free: 0
Auto_increment: NULL
Create_time: 2007-03-01 18:24:54
Update_time: 2007-03-02 12:36:39
Check_time: 2007-03-01 18:33:56
Collation: latin1_bin
Checksum: NULL
Create_options: min_rows=10000000 pack_keys=1 delay_key_write=1
Comment:
1 row in set (0.00 sec)
# cat /proc/cpuinfo
processor : 0 .. 7
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU E5310 @ 1.60GHz
stepping : 7
cpu MHz : 1596.046
cache size : 4096 KB
physical id : 1
siblings : 4
core id : 3
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : 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 ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3192.20
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
cat /proc/meminfo
MemTotal: 4049160 kB
MemFree: 31252 kB
Buffers: 48096 kB
Cached: 1400356 kB
SwapCached: 0 kB
Active: 3312188 kB
Inactive: 587900 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 4049160 kB
LowFree: 31252 kB
SwapTotal: 0 kB
SwapFree: 0 kB
Dirty: 0 kB
Writeback: 0 kB
Mapped: 2463312 kB
Slab: 83948 kB
CommitLimit: 2024580 kB
Committed_AS: 4710656 kB
PageTables: 6112 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 277032 kB
VmallocChunk: 34359459783 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB
The my.cnf is a mess, since I'm in the process of optimizing it:
# cat /etc/my.cnf
# The MySQL server
[mysqld]
datadir=/var/mysql
tmpdir=/var/tmp
port = 3306
socket = /var/lib/mysql/mysql.sock
max_connections=100
back_log=50
max_allowed_packet= 1M
secure-auth = 1
default-character-set=latin1
default-storage-engine=InnoDB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
skip-external-locking
# skip-myisam Cannot do this ;-)
skip-bdb
# skip-merge
skip-ndbcluster
# skip-symbolic-links
# skip-ssl
skip-name-resolve
# Query Cahe does NOT work for ServerSide-PreparedStatemens ...
query_cache_size=1M
table_cache=32
tmp_table_size=1M
thread_cache_size=64
net_buffer_length =8K
# low-priority-updates = 1
# memlock=1
delayed_insert_limit = 100
delayed_queue_size = 1000
max_delayed_threads = 4
flush_time=0
join_buffer_size=13K
# Use less than 100% to divert from the plain LRU strategy
key_cache_division_limit=100
#*** Replication (Master)
log-bin = ucs
log-bin-trust-function-creators=0
log-bin-trust-routine-creators=0
server-id=1
# Not really replication but still belongs here
# auto_increment_increment=2
# auto_increment_offset=1
# For Second Master with Cross-Replication
# auto_increment_offset=2
expire_logs_days=1
max-relay-log-size=1G
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# *** InnoDB Specific options
# innodb_file_per_table=0
innodb_data_home_dir=/var/mysql1
# innodb_log_arch_dir=/var/mysql
innodb_log_group_home_dir=/var/mysql
innodb_data_file_path=ibdata1:10G:autoextend
innodb_autoextend_increment=10240
innodb_open_files = 20
innodb_buffer_pool_size=2048M
innodb_additional_mem_pool_size=128M
innodb_fast_shutdown=1
innodb_flush_log_at_trx_commit=0
# Use 50% instead of default 90%
innodb_max_dirty_pages_pct=50
innodb_support_xa=false
# as long as we use autommit ...
innodb_table_locks=false
# innodb_commit_concurrency=0
# innodb_concurrency_tickets=500
# innodb_doublewrite=true
# innodb_lock_wait_timeout=
# innodb_max_purge_lag=
# innodb_sync_spin_loops=20
# default 8, reasonable would be 2..4*(NumCPU+NumDisks)
innodb_thread_concurrency=32
innodb_flush_method=O_DIRECT
innodb_log_buffer_size = 16M
# innodb_log_file_size = 10485760
#*** MyISAM Specific options
# myisam_max_sort_file_size=100G
# myisam_max_extra_sort_file_size=100G
# myisam_sort_buffer_size=18M
# read_buffer_size=64K
# read_rnd_buffer_size=256K
# sort_buffer_size=1M
# delay-key-write=ON
# key_cache_age_threshold=300
# Leave a bit room for default cache.
key_buffer_size=64M
key_cache_block_size=1024
# Structured Variable for Flags Cache
flag_cache.key_buffer_size=2G
flag_cache.key_cache_block_size=10K
# myisam-recover=FORCE
myisam-recover=BACKUP,FORCE
# *** Debugging
# enable-pstack=1
log-queries-not-using-indexes=1
log-slow-admin-statements=1
log-slow-queries=1
log-warnings=1
long_query_time=1
# The safe_mysqld script
[safe_mysqld]
err-log=/var/log/mysqld.log
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=latin1
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
[15 Mar 2007 14:45]
Klaus Halfmann
OK, does not happen on Linux/Intel java.vm.version : 1.5.0_11-b03 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_11-b03 os.name : Linux os.version : null sun.management.compiler : HotSpot 64-Bit Server Compiler processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU E5310 @ 1.60GHz stepping : 7 cpu MHz : 1596.044 cache size : 4096 KB physical id : 0 siblings : 4 core id : 0 cpu cores : 4 fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : 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 ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm bogomips : 3195.29 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management:
[15 Mar 2007 14:48]
Klaus Halfmann
*** Please ignore my last comment *** It was intended as addenum for some other Bugreport. Sorry
[16 Mar 2007 14:12]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.37, and inform about the results.
[16 Apr 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[19 Jun 2007 7:26]
MySQL Verification Team
key_cache_block_size would have caused many corruptions in this old version. Please try again with latest 5.0.44 (or use default value of key_cache_block_size).
[19 Jul 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: I Create a MyISAM Tabe for a (ugly, huge) n-m relation via CREATE TABLE A_B ( USER DECIMAL(24) ZEROFILL NOT NULL, A_ID BIGINT UNSIGNED NOT NULL, -- REFEERENCES A.ID B_ID SMALLINT UNSIGNED NOT NULL, -- REFEERENCES B.ID CONSTRAINT PRIMARY KEY (USER, CONF_ID, FLAG_ID) ) ENGINE=MyISAM DELAY_KEY_WRITE = 1 PACK_KEYS=1 MIN_ROWS=10000000 INDEX DIRECTORY='/var/mysql2' DATA DIRECTORY='/var/mysql3' DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_bin; -- /var/mysql2 and 3 are different devices ... Filesystem Size Used Avail Use% Mounted on /dev/sdb1 69G 11G 59G 15% /var/mysql1 /dev/sdc1 69G 85M 69G 1% /var/mysql2 /dev/sdd1 69G 131M 69G 1% /var/mysql3 CACHE INDEX CONF_FLAG IN flag_cache; -- Where flag_cache is defined in my.cnf as: # Structured Variable for Flags Cache flag_cache.key_buffer_size=2G flag_cache.key_cache_block_size=10K -- I tried to analyze the table as follows -- while doing some heavy, delayed inserts LOAD INDEX INTO CACHE CONF_FLAG IGNORE LEAVES; ANALYZE TABLES CONF_FLAG; I get tons of message like: [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/var/mysql2/A_B.MYI'; try to repair it One restart reads as follows: 070301 18:33:46 mysqld started 070301 18:33:47 InnoDB: Started; log sequence number 0 1804100518 070301 18:33:47 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.0.27-max-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL) 070301 18:33:55 [ERROR] /usr/local/mysql/bin/mysqld: Table './ucs/CONF_FLAG' is marked as crashed and should be repaired 070301 18:33:55 [Warning] Checking table: './ucs/A_B' 070301 18:33:55 [Warning] Recovering table: './ucs/A_B' 070301 18:33:56 [Note] Found 295462 of 295635 rows when repairing './ucs/CONF_FLAG' How to repeat: When doing above it happend two times. I fear that LOAD INDEX INTO CACHE ... stumbles over the symlinks implied by INDEX DIRECTORY='/var/mysql2' DATA DIRECTORY='/var/mysql3' The problem can be fixed by restarting the server. As Im trying to load the Database Im not much motivated to repeat it. But in cas it happens agains I will refresh this report. Suggested fix: Check how LOAD INDEX INTO CACHE behaves with Symlinks ?