Am 12.01.2010 16:30, schrieb Peter Russ:
Please enable also slow queries log in Mysql as there might be other
queries stopping the system.
Peter.
Ok, I've enabled the slow query log and it just happened again. I've
extracted the time frame in question and attached the
"mysqldumpslow -l" (-l == "don't subtract lock time from total time")
of that part of the log.
In this case, the longest taking query is definitely this one:
Count: 1 Time=876.00s (876s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
UPDATE pages
SET
SYS_LASTCHANGED='S'
WHERE
uid=N
Which, given that I did the magical alter pages to InnoDB, should never
ever take that long should it? An update of a single primary key
referenced row in an InnoDB table. And the accessed page is a seldomly
accessed one so I don't even think that it got accessed during the
timeframe in question.
Some notes on what we did exactly before this problem occured:
1)
I manually cleared the large cache tables in case we would get the slow
DELETE * FROM ... queries from the page update
-> truncate table cache_hash;
-> truncate table cache_pages;
2) We logged in to the backend and changed a standard tt_content text
element (which has a translation into a second language) and pressed
"Save".
--> The browser never returned from "saving" since the database locked.
3) After about 5 Minutes, I stopped apache and a couple of minutes
later, the db was cleared up and running again.
What I furthermore find interesting is, that saving just after something
like that happened does too take some time, but tends to not lock the
database. The same update with sys_lastchanged query took (when saving
the same page 20 minutes later) 15 seconds.
Still too long but at least not locking.
Does anyone get any insight from this information?
Thanks a lot,
Christopher
Count: 1 Time=876.00s (876s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
UPDATE pages
SET
SYS_LASTCHANGED='S'
WHERE
uid=N
Count: 1 Time=851.00s (851s) Lock=0.00s (0s) Rows=1.0 (1),
site[si...@localhost
SELECT
pid,uid,t3ver_oid,t3ver_wsid,t3ver_state,t3ver_swapmode,title,alias,nav_title,media,layout,hidden,starttime,endtime,fe_group,extendToSubpages,doktype,TSconfig,storage_pid,is_siteroot,mount_pid,mount_pid_ol,fe_login_mode,tx_templavoila_ds,tx_templavoila_to,tx_templavoila_next_ds,tx_templavoila_next_to
FROM pages
WHERE
uid=N AND pages.deleted=N AND pages.doktype!=N
Count: 1 Time=836.00s (836s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
UPDATE be_sessions
SET
ses_tstamp='S'
WHERE
ses_id='S'
AND ses_name='S'
Count: 2 Time=826.50s (1653s) Lock=0.00s (0s) Rows=1.0 (2),
site[si...@localhost
SELECT ses_userid
FROM fe_sessions
WHERE
ses_id='S'
Count: 47 Time=822.60s (38662s) Lock=0.00s (0s) Rows=1.0 (47),
site[si...@localhost
SELECT ses_id, ses_userid
FROM fe_sessions
WHERE
ses_id='S'
Count: 64 Time=820.88s (52536s) Lock=0.00s (0s) Rows=0.7 (44),
site[si...@localhost
SELECT *
FROM fe_sessions
WHERE
ses_id = 'S' AND ses_name = 'S'
Count: 1 Time=816.00s (816s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
UPDATE fe_users
SET
is_online='S'
WHERE
uid=N
Count: 5 Time=807.40s (4037s) Lock=0.00s (0s) Rows=1.0 (5),
site[si...@localhost
SELECT ses_userid
FROM fe_sessions
WHERE
ses_id='S'
LIMIT N
Count: 7 Time=623.57s (4365s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
INSERT INTO fe_sessions
(
ses_id,
ses_name,
ses_iplock,
ses_hashlock,
ses_userid,
ses_tstamp,
ses_permanent
) VALUES (
'S',
'S',
'S',
'S',
'S',
'S',
'S'
)
Count: 6 Time=561.50s (3369s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
DELETE FROM fe_sessions
WHERE
ses_id = 'S'
AND ses_name = 'S'
Count: 19 Time=457.79s (8698s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
INSERT DELAYED INTO tx_supgames_funnelevents
(
tstampFirstVisit,
session,
userID,
eventID,
tstamp,
pid,
currentModuleID,
referrerModuleID,
referrerSubModuleID,
lang,
browser,
paymentFunnelState,
loggedIn
) VALUES (
'S',
'S',
'S',
'S',
'S',
'S',
'S',
'S',
'S',
'S',
'S',
'S',
'S'
)
Count: 2 Time=439.00s (878s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
INSERT INTO tx_supgames_userlog
(
ip,
time,
uid
) VALUES (
'S',
UNIX_TIMESTAMP(),
'S'
) ON DUPLICATE KEY UPDATE ip='S', time=UNIX_TIMESTAMP(), uid='S'
Count: 2 Time=437.50s (875s) Lock=0.00s (0s) Rows=0.0 (0),
root[ro...@localhost
UPDATE ultuserlogins
SET
numLogins=numLogins+N
WHERE
gameID=N AND siteUserID=N
Count: 2 Time=436.50s (873s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
INSERT INTO tx_supgames_uservotes
(
userID,
siteID,
lastVote,
lastReward,
) VALUES (
'S',
'S',
'S',
'S'
) ON DUPLICATE KEY UPDATE userID='S', siteID='S', lastVote='S', lastReward='S'
Count: 16 Time=116.00s (1856s) Lock=0.00s (0s) Rows=0.0 (0),
site[si...@localhost
UPDATE fe_sessions
SET
ses_tstamp='S'
WHERE
ses_id='S'
AND ses_name='S'
Count: 1 Time=12.00s (12s) Lock=0.00s (0s) Rows=0.0 (0), site[si...@localhost
UPDATE tt_content
SET
bodytext='S',
tstamp='S'
WHERE
uid=N
Count: 1 Time=8.00s (8s) Lock=0.00s (0s) Rows=0.0 (0), site[si...@localhost
SET insert_id=N;
INSERT INTO tx_mmforum_postsread
(
pid,
topic_id,
user,
tstamp,
crdate
) VALUES (
'S',
'S',
'S',
'S',
'S'
)
Count: 3 Time=6.00s (18s) Lock=0.00s (0s) Rows=0.0 (0), site[si...@localhost
INSERT INTO cache_hash
(
hash,
content,
ident,
tstamp
) VALUES (
'S',
'S',
'S',
'S'
)
Count: 10 Time=4.10s (41s) Lock=0.10s (1s) Rows=10223.7 (102237),
site[si...@localhost
SELECT distinct(topic_id)
FROM tx_mmforum_posts a
WHERE
deleted = N AND crdate > N AND a.topic_id NOT IN (SELECT topic_id FROM
tx_mmforum_postsread WHERE user=N AND pid='S')
Count: 1 Time=3.00s (3s) Lock=0.00s (0s) Rows=0.0 (0), site[si...@localhost
SET insert_id=N;
INSERT INTO cache_pages
(
hash,
page_id,
HTML,
temp_content,
cache_data,
expires,
tstamp
) VALUES (
'S',
'S',
'S',
'S',
'S',
'S',
'S'
)
_______________________________________________
TYPO3-english mailing list
[email protected]
http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english