Locked state in MySQL Processes

[SecondSight]

Hello ! :)

I've found out that there are many "locked state" in my board's mysql processes. These lasted some time (I updated it every second and posted the result below) :



The following didn't last long :


Is it possible to find out the reason for the locked tables, looking at what I've posted here ?

Thank you ! :)

[Solo Drakban]

I've been having the same issues and have been trying to track them down. My investigation seems to indicate there is deadlocking occurring on the queries that alter the sessions table but I haven't had much of a chance to track it down further. If anybody at IPB is interested I can supply a 'show engine innodb status' report showing the deadlocks and queries.

[Robulosity2]

I *think* you can safely run _sessions as a table in memory but just make sure you have enough memory allocated to mysql etc

[SecondSight]

I *think* you can safely run _sessions as a table in memory but just make sure you have enough memory allocated to mysql etc

I've already heard about this solution, but I don't know how to achieve that. Can you tell us more about it ?

Thank you ! :)

[Robulosity2]

Well first step is back up your database/table and I'm going to assume MySQL has the storage engine for MEMORY enabled but here' the general steps in PHP My Admin


- Select your database
- Select your table by clicking its name
- Go to the Operations Tab
- Under Table Options change Storage Engine from MyISAM to "MEMORY"


Depending on how large your site is, you need to be mindful of your MySQL (my.cnf) configuration as well as the memory limits of your server it self (be it VPS, Shared or Dedicated) since running tables in memory has
its own inherent risks... like if your server goes BOOM and crashes any session data in that table would be lost... the ibf_sessions table really shouldnt be a game stopper if it gets wiped as long as it can re-generate the columns etc

[SecondSight]

Thank you for the explanations you gave us.:)

As for me, I don't know what's really going on on my server, and I would like to find out.

At the top of the process list, I generally have an update query like these :

update INSERT INTO ibf_content_cache_posts (`cache_content_id`,`cache_content`,`cache_updated`) VALUES(

update INSERT INTO ibf_topic_views (`views_tid`) VALUES(247934)

update INSERT INTO ibf_posts (`author_id`,`use_sig`,`use_emo`,`ip_address`,`post_date`,`icon_id

update INSERT INTO ibf_core_item_markers_storage (`item_member_id`,`item_markers`,`item_last_saved`) VALUES

They are followed by many locked queries, like these :

Locked SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_

Locked SELECT * FROM ibf_sessions WHERE id='bf47c5fab8af25647b9485e294768f6d'

Locked SELECT pid FROM ibf_posts WHERE topic_id=247934 AND queued=0 ORDER BY pid as

Locked SELECT pid FROM ibf_posts WHERE topic_id=30088 AND queued=0 ORDER BY pid asc LIMIT 50,10

I also have found "system lock" status, for instance :
System lock SELECT m.*, m.member_id as my_member_id,p.*,pp.*,g.*,im.*,ccb.cache_content FROM ibf_members m LEFT

I've tried optimizing the server configuration and other people tried to do it too, with more succes than me and made things working better. But the problem is still here and not fixed.

I don't know how to track it down. Is there a way to find out what's is going wrong ? If yes, how should I proceed ?

Thank you ! :)

[SecondSight]

I forgot to say that the queries are locked even when the server load is low (for instance at 1.5).

[Solo Drakban]

Are your tables InnoDB or MyISAM?

[SecondSight]

All my tables are MyISAM.

[SecondSight]

I'm adding a few screen shots for support. If someone has an idea, or pieces of advice... :)

[cargelock]

What value do you have for --skip-external-locking? (used to be called skip-locking)

It's in your my.cnf or you can view it from variables tab in phpmyadmin

[SecondSight]

Hello ! :)

In my my.cnf, I still have skip-locking. I believe I will have to replace it with skip-external-locking (my MySQL version is 5.0.91-community-log).

I've had a look in mysql and I get this :

mysql> show variables like '%locking%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| skip_external_locking | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

I've had a look in MySqlAdmin and it says :

skip external locking ON

I've had the ibf_sessions table placed in memory. Now I'm waiting to see if it's doing better. :)

By the way, now that the ibf_sessions table is placed in memory, what will happen if I restart mysql ? Will members be logged out from my board ?

Thank you ! :)

[AlexJ]

Members will not log out but I think there session will be terminated. So let's say before restart if it shows on forum 500 users are browsing forum and after MySQL restart it will say 0-10 users browsing forums and then it will increase again slowly.

[SecondSight]

Thank you for the explanations ! :)

Having ibf_sessions placed in memory significantly reduced the server load. I no longer see locked queries with ibf_sessions.

Now I'm wondering about ibf_core_item_markers_storage :

=> Having "Enable Topic Marking Tracking ?" (in "CPU Savings and optimization") set to "No" makes a server load of about 0.50
=> But if I set it to "Yes", it creates a server load between 10 and 20 (and it constantly goes up and down).

Anyway, I still have locked queries...


What should I do with ibf_core_item_markers_storage in your opinion ?

Thank you ! :)

[AlexJ]

What's the value of your 'concurrent_insert' ?

[SecondSight]

Here it is : concurrent_insert=2

I also have : low_priority_updates=1

[cthree]

Your tables are locked because you are using MyISAM tables. Once you reach a certain size you cannot use MyISAM reliably anymore, you will deadlock on table locks. Switch to using InnoDB to solve your table lock problem.

[cthree]

ibf_core_item_markers_storage is a massive bottleneck. It's horrible and should be in cache. Caching in IPB kinda sucks ass.

[SecondSight]

I was told I should use InnoDB for the item markers table to fix this problem...

Should I use InnoDB for all tables or just for ibf_core_item_markers_storage or just for a few tables ? What's best according to you ?

I don't know anything about InnoDB (I'm going to make a search about it). What should I care for ? :)

Thank you ! :)

[cthree]

You can't use it for tables with fulltext indexes -or- use sphinx for search and go full innodb