Re: Problems with system locking
Hi again Ingo
Thanks for the further reply...
On Sunday we swapped the current database for a new, clean database, to try and avoid running into the problems again. We plan to do this every week but understandably, this isn't the ideal solution!
We have executed myisamchk -dv against some of the previous copies of the database, and the output is included below. I think it shows that we have not run into size/space issues, but you know more than me on that one! :)
The general impression we get is that the occurence of system locks increases over time, certainly we never see them on this new, empty database. If system locks are indicated in SHOW PROCESSLIST when the system is flushing its key cache, and there are multiple threads trying to write to the same table (as is the case in our setup) then are they explained as contentions between the various INSERT statements? ie: one INSERT ends and starts flushing the key cache but while that happens, the next INSERT is trying to execute but can't because the key cache is being flushed??? However if this is the case, I still don't see why the locks should cause a significant degradation in performance, which appears to be the case.
Anyway, here are the output traces from myisamchk...
Cheers
Brian
H:\MySQLData>cd emptyDB
[New empty database, used as template for swapping over db's to avoid problems]
H:\MySQLData\emptyDB>myisamchk.exe -dv mytable.myi
MyISAM file: mytable.myi
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-03-30 16:39:03
Recover time: 2006-03-30 16:39:03
Status: checked,analyzed,optimized keys
Data records: 0 Deleted blocks: 0
Datafile parts: 0 Deleted data: 0
Datafile pointer (bytes): 5 Keyfile pointer (bytes): 4
Datafile length: 0 Keyfile length: 1024
Max datafile length: 1099511627774 Max keyfile length: 4398046510079
Recordlength: 127
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique long 0 1024
6 4 long 0
34 8 longlong 1
2 93 32 multip. char packed stripped NULL 0 1024
3 26 8 multip. longlong 0 1024
4 44 2 multip. short 0 1024
5 42 2 multip. short 0 1024
6 10 8 multip. longlong 0 1024
7 34 8 multip. longlong 0 1024
H:\MySQLData>cd liveDB
[Current live database running since 2am on Sunday]
H:\MySQLData\liveDB>myisamchk.exe" -dv mytable.myi
MyISAM file: mytable.myi
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-03-30 16:39:03
Recover time: 2006-04-30 7:01:19
Status: open,changed
Data records: 96529103 Deleted blocks: 0
Datafile parts: 96529103 Deleted data: 0
Datafile pointer (bytes): 5 Keyfile pointer (bytes): 4
Datafile length: 48190484280 Keyfile length: 9953320960
Max datafile length: 1099511627774 Max keyfile length: 4398046510079
Recordlength: 127
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique long 0 6938351616 1024
6 4 long 0
34 8 longlong 1
2 93 32 multip. char packed stripped NULL 1053 2000731136 1024
3 26 8 multip. longlong 3 711139328 1024
4 44 2 multip. short 12704 6734525440 1024
5 42 2 multip. short 9390 7088035840 1024
6 10 8 multip. longlong 90 1018160128 1024
7 34 8 multip. longlong 11 955856896 1024
H:\MySQLData>cd last
[Previous Live Database was showing many system locks when busy, about 10 days' data]
H:\MySQLData\last>myisamchk.exe -dv mytable.myi
MyISAM file: mytable.myi
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-03-30 16:39:03
Recover time: 2006-03-30 16:39:03
Status: changed
Data records: 432050417 Deleted blocks: 0
Datafile parts: 432050417 Deleted data: 0
Datafile pointer (bytes): 5 Keyfile pointer (bytes): 4
Datafile length: 215634416176 Keyfile length: 44155475968
Max datafile length: 1099511627774 Max keyfile length: 4398046510079
Recordlength: 127
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique long 0 6775956480 1024
6 4 long 0
34 8 longlong 1
2 93 32 multip. char packed stripped NULL 0 2304719872 1024
3 26 8 multip. longlong 0 27447254016 1024
4 44 2 multip. short 0 7025213440 1024
5 42 2 multip. short 0 7099728896 1024
6 10 8 multip. longlong 0 1008464896 1024
7 34 8 multip. longlong 0 984189952 1024