Re: Problems with system locking
Hi Brian,
sorry, I must have missed your mentioning of Windows before. The problem here is that I know near to nothing about Windows behaviour. But I will try to answer as much as I can:
I agree with your explanations regarding the 'Locked' state.
Again 'System lock' includes file locking, which is disabled by default, and key cache flushing. You should not normally see this state as it should be very short. I have no good explanation why you do see it.
I agree with your calculations regarding table size. The limit is not always a hard limit. But there is a hard limit. To save space in the index file we try to keep the pointer into the data file as short as possible (myisam_data_pointer_size). By default we configure it as small as possible but so large that the requested table size is possible. So you can extend beyond the limit you calculated, but only up to the size that can be expressed by the data pointer size. This size can also be viewed with myisamchk -dv.
I understand that you are worried to use myisamchk on a live server. I won't press you to try it. But I don't believe that it would block the server with the option -d. It means 'description'. As far as I understand the MyISAM code, it won't even try to lock the table. But even if it would, the srever ignores the lock, as you explained correctly twice. And even if it would block it, the description finishes very quickly. It reads the index file header (usually less than 1024 bytes), writes the requested information and exits. No data or indexes are read. Let alone written. But, OTOH, there is a small chance that an undetected bug might step in. So decide yourself...
You are right with skip-external-locking. I just don't see it in the code. There seems to be some miracle...
Regards
Ingo Strüwing, Senior Software Developer - Storage Engines
MySQL AB, www.mysql.com