Can 'atime' cause table lock for pure-read tables?

Polyglot Projects LAMP Stack
1

I have MyISAM table that is never updated and is only read from. Still slow_query_log and pt-query-digest show that with total execution time of 278 sec lock times are around 277 sec ( about 4000 queries where executed). Is it possible that table is locked due to ‘atime’ attribute on mounted partition? Or can it be caused by something else?

Here’s output from pt-query-digest:

Query 1: 3.04 QPS, 0.21x concurrency, ID 0xFF760E36424DACA9 at byte 246469866# This item is included in the report because it matches --limit.# Scores: Apdex = 1.00 [1.0], V/M = 18.05# Query_time sparkline: | ^__ _|# Time range: 2012-03-22 17:54:47 to 18:16:32# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 0 3972# Exec time 14 278s 12us 29s 70ms 725us 1s 224us# Lock time 22 277s 0 29s 70ms 57us 1s 35us# Rows sent 1 11.62k 0 3 2.99 2.90 0.08 2.90# Rows examine 0 23.23k 0 6 5.99 5.75 0.15 5.75# Rows affecte 0 0 0 0 0 0 0 0# Rows read 0 5.23k 0 2 1.35 1.96 0.90 1.96# Bytes sent 0 1.54M 355 455 405.70 420.77 12.73 400.73# Merge passes 0 0 0 0 0 0 0 0# Tmp tables 0 0 0 0 0 0 0 0# Tmp disk tbl 0 0 0 0 0 0 0 0# Tmp tbl size 0 0 0 0 0 0 0 0# Query size 0 692.32k 175 179 178.48 174.84 0 174.84# InnoDB:# Boolean:# QC Hit 0% yes, 99% no# String:# Databases db1 (2982/75%), db2 (990/24%)# Hosts localhost# Last errno 0# Users db1 (2982/75%), db2 (990/24%)# Query_time distribution# 1us# 10us ## 100us ################################################################# 1ms ## 10ms ## 100ms ## 1s# 10s+ ## Tables

Regards,
Lucek

2

Hi,
You’ve mentioned that your MyISAM table is only read and never updated, therefore it would be wise to use ‘noatime’ mount option on your filesystem. This setting will eliminate the need by the system to make writes to the file system for files which are simply being read.
You can also refer to this article [URL]http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/[/URL] for other performance tuning on your database. You can also change your IO scheduler to noop to increase IO on your disks. Good if you have EXT3 filesystems. There is discussions about this from here [URL]http://www.mysqlperformanceblog.com/2009/01/30/linux-schedulers-in-tpcc-like-benchmark/[/URL].

3

Lucek,
The ‘atime’ attribute on filesystem level has nothing to do with MyISAM locking.
This sample query had maximum lock time ever at 29s, while average 70ms, 95% 57us, so it was just a single incident when this table was locked (for some reason, maybe backup script?).
277s is the total sum for all the query occurrences logged.