MySQL Forums
Forum List  »  MyISAM

Re: Problems with system locking
Posted by: Brian O'Donnell
Date: May 02, 2006 07:49AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
9426
April 26, 2006 05:37AM
9939
April 27, 2006 04:36AM
Re: Problems with system locking
3350
May 02, 2006 07:49AM
4119
June 13, 2006 09:59PM
2993
June 14, 2006 10:22AM
3225
June 15, 2006 11:18AM
3112
July 14, 2006 03:52PM
2965
August 22, 2006 11:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.