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

Reply via email to