Tables locking with phpBB

[Arwen]

For the past week and a half, I've had the exact same problem with a mySQL database, which is used for a forum running phpBB 2.0.14. I receive a message saying that too many tables are locking, and that powweb is temporarily disabling the database to prevent a slowdown on the entire server. I'll then e-mail back, ask to have the database reactivated, and ask what I can do to make sure this doesn't happen again. The database gets reactivated, but I end up getting some vague answer like "Make sure all your scripts and tables are optimized" that never helps. I've called tech support three times now, and each time they've been able to reactivate the database but not help me prevent this from happening. And it's always the same: the next morning, I check my e-mail only to find out that the tables are locking, and the forum is down again.

This is the exact message I'm receiving:

Your dB (--my database's name, censored for privacy--) is creating locked tables on the mysql server. We have
temporarily disabled your database to prevent slowness on the entire mysql
server. Please find ways to optimize your queries within your script. Email us
back once you have done so and we can activate your db again. Thank you for your
understanding.


| 427570 | --database user-- | 10.0.98.32:1450 | --database name-- | Query
| 113 | Locked | SELECT *
FROM phpbb_users
WHERE user_id = 59
|
| 428067 | --database user-- | 10.0.98.31:3608 | --database name-- | Query
| 80 | Locked | SELECT *
FROM phpbb_users
WHERE user_id = 213
|
| 428155 | --database user-- | 10.0.98.36:4644 | --database name-- | Query
| 76 | Locked | SELECT *
FROM phpbb_users
WHERE user_id = 59
|
| 428334 | --database user-- | 10.0.98.31:2416 | --database name-- | Query
| 64 | Locked | SELECT *
FROM phpbb_users
WHERE user_id = -1
|
| 428361 | --database user-- | 10.0.98.35:1870 | --database name-- | Query
| 62 | Locked | SELECT *
FROM phpbb_users
WHERE user_id = -1
|
| 428586 | --database user-- | 10.0.98.34:3101 | --database name-- | Query
| 49 | Locked | SELECT *


This gives me no help whatsoever understanding what's going on. Yesterday I tried setting my flood limit higher and putting fewer threads per page, but evidently it didn't do any good. Advice to optimize the tables doesn't work either; I can optimize them five times per day and I still get the same message. All I can understand from this message is that there's a problem with a few users, but I don't know what the problem is or how to solve it. Please help!

Thank you.

[RTH10260]

I cannot give you any sound advice, cause I don't run this forum system not know details about it, not have I any indepth experience how Powweb is running their MySql servers. So take these questions as a point for further investigation. You will also want to ask your question in the support forum of the phpbb authors.

The listing above probably shows the sql statements that stall cause the table is locked, the statement that establishes the lock is likely something else.

It will be any of the sql statements that operate on the phpbb_users table, and it is likely one that updates the table.

Please check if after installation you have enabled all indexes. For the phpbb_users table this would include column user_id in any one index at first position.

As I have never read about any issue of pbpbb with mysql, the problem may arise with some nonstandard stuff: did you apply any own code, or did you apply a mod for some feature you wished to have ?

Are you sure you didn't specifiy an option 'use permanent connections', or something like this wording, in your configuration file ?

[Arwen]

The odd part is, I've had this forum for over six months with the exact same configuration, and it's only during the last week and a half that I've had any problems.

The MODs I have installed are: Advanced User Search, BB Code Strikethrough, Bottom Aligned Signature, FAQ Edit, Last post info, Mod CP, Multi BB Code, Global Moderator, Font Face, Link Username to Profile, Signature in Profile View, Yesterday-Today

All of these are quite simple and I didn't execute any SQL commands to install them. Besides, I've had them all for as long as I've had the forum, and none has caused problems before.

In the config file, besides the basic listing of database name, username, password, etc., all I have is this:

$table_prefix = 'phpbb_';

define('PHPBB_INSTALLED', true);

I've had this same config file as long as I've had the forum.

I can't look at the tables because since my database is disabled, I cannot login to phpmyadmin.

As far as I know, I haven't applied any code besides template styling.

Thanks for trying to help!

[RTH10260]

Hint for debugging: create an error page (simple html) explaining that your forum has been taken offline for maintenance. Then add a rewrite rule so that all regular references get diverted to this error page. Show this to Support, eg that you will have no activity but your own, and ask them to enable the database so that you can use phpmyadmin.

You said: "All of these are quite simple and I didn't execute any SQL commands to install them." That's not what I meant. I am suggesting that one of those additional mods are badly designed and working against you. I assume that the phpbb authors themselves are professionals, which isn't necessarily true for third party authors. Maybe you need to define additional indexes in support of one of the mods.

As I mentioned, only guess work on my behalf, sorry.

I hope the list of mods may bring others to your help.

[Arwen]

I understand that it's possible for some MODs to be badly designed, but as I've had them all installed for over six months now, I'm not sure why they'd suddenly be causing problems. I posted on the phpBB support forums, and someone suggested I disable forum pruning, as it's been known to cause problems. This is what the person said:

Last I checked, PHPBB contains no code which explicitly locks a table. There are some operations (pruning) which can cause one or more tables to be busy for a long period of time, which might show up as "locked". To test if this is a problem, you can disable pruning of all forums, and see if the "locks" go away.

On the other hand, why should the host be concerned if your application (PHPBB) locks your tables? A locked table only affects applications that are using it, by preventing them from having more than read access to the table. This should not affect any other databases on the server.

The person who responded to me also added this:

If a PHPBB site executed a write locked against ALL of its tables, that should not constitute "many" on a well-designed server. Our PHPBBs have MODs that bring the total to 45 tables per site; I think unmodified is 23. Even our modest server has hundreds of other tables, so a site full of locks isn't a lot. And, all it would do is queue up all connections for those tables that needed write permission, until the lock were released, or the request timed out.

Yes, it is possible to block a server this way, by putting in hundreds of requests for tables that are locked, but, since nothing in PHPBB asks for a table lock, any locks that are in place are because the server thought it was necessary...


(I should mention that none of the MODs I've installed required additional tables.)

This doesn't make a lot of sense to me now. If what this person says is true, and that phpBB doesn't ask for table locks, I don't know why/if PowWeb locks tables themselves, and how I'm expected to fix this. I'm going to call support to see if they can help, but if anyone on this forum has any thoughts/ideas on this issue, I'd appreciate hearing them!

[shaunj66]

I've got the same problem, with an Invision Power board.
We got our SQL account closed for the same reason ("your db is creating locked tables...")
We've already called tech support once in order to re-enable our account.
But now I don't know how to fix this...
For an unknown reason, a short while after the website got re-enabled, we got the following SQL error:
mySQL query error: SELECT * FROM ibf_cache_store WHERE cs_key IN ( 'settings', 'group_cache', 'systemvars', 'skin_id_cache', 'forum_cache', 'moderators', 'stats' )

mySQL error: User 'XXXXXX(removed)' has exceeded the 'max_questions' resource (current value: 72000)
mySQL error code:
Date: Saturday 15th of October 2005 10:38:07 AM


Here's what I was told by a friend:

[19:26] Costello: I think I HAVE actually fixed the problem, really
[19:26] Costello: i'll explain why:
[19:26] Costello: for some reason, IPB needed to lock the tables;
[19:26] Costello: that means, to make them unavailable for other users
[19:26] Costello: eg. useful when editing a post :
[19:27] Costello: two people shouldnt be editing the same post on the same time
[19:27] Costello: so,
[19:27] shaunj66: yeh
[19:27] Costello: lets assume the tables get locked
[19:28] Costello: these locked tables will be unavailable to anything, that is: nothing can be read , written in it
[19:28] Costello: so when some php script makes a SQL request, (eg. INSERT or SELECT)
[19:28] Costello: the request will either fail ,
[19:28] Costello: or
[19:28] Costello: loop
[19:28] Costello: until it succeeds
[19:28] Costello: BUT the tables dont seem to get unlocked !
[19:28] Costello: so the requests loop
[19:28] Costello: again and again

Do you think he's right?
We've disabled the "LOCK" privilege in our ops panel, hopefully IPB will not lock tables again!

But at the moment we keep getting that SQL error!

[RTH10260]

But now I don't know how to fix this...
For an unknown reason, a short while after the website got re-enabled, we got the following SQL error:
. . .
You are hitting some Powweb designated limits as outlined in the TOS. After your forum was offline, you experienced a peak in usage when it came back online. Did you already implement the three-username workaround to spread the load ? If not yet, time to do so. If already implemented, then this would be a serious hint to recognize that your forum has outgrown Powweb and you should be planning to move on very soon.

The explanation of the max numbers - http://forum.powweb.com/showthread.php?s=&threadid=23735
The solution explained - http://forum.powweb.com/showthread.php?s=&threadid=28309

Please check for some interesting threads on MySql in the sticky thread, for some unknown reason listed in the PHP forum section only.

[RTH10260]

First a disclaimer: I know nothing about the IPB forum system, so I can only make general observations.
Do you think he's right?
We've disabled the "LOCK" privilege in our ops panel, hopefully IPB will not lock tables again!
Warning - Dangerous:
Removing the Lock privilege from a username can endanger the forum system and in the worst case corrupt the database when it gets inconsistent. Removing this privilege should only be done when really understanding the forum code and its interaction with the database.

Btw a well designed software package will also be checking error codes on lock requests and abort processing when it cannot ensure locking.

[RTH10260]

I've got the same problem, with an Invision Power board.

Here's what I was told by a friend:
. . .
Do you think he's right?
We've disabled the "LOCK" privilege in our ops panel, hopefully IPB will not lock tables again!The database system will always lock tables to some extent when doing updates. It's an inherent feature of safe and efficient operation.

Using Lock Tables and Unlock Tables statements at the program level can be used to ensure efficient updating. The problem is, such locks should only be kept for a parts of a second, cause by their nature they block other transactions.

It's of essence that Unlock Table commands of Commit/Rollback statements are used as soon as possible. Mass updates should be broken down into smaller granularity. Locking also includes the proper handling of the autocommit feature.

What does the IPB documentation say about usage of database engines ? MyIsam does only have rudimentary locking support. You may need to use the BDB database engine for better transactional support (best would be the unavaiable InnoDB).

As mentioned earlier in the thread, I suspect one of the user mods and not the basic product to be the culprit. Look out for any that does some kind of mass updates as first priority, then for any that possibly does simple updating. You may have to disable/uninstall features to find your way along.

Good luck !

[shaunj66]

First, we've never had TOO MANY sql requests before. It's only when we recovered from the "locked tables" thingy that this showed up.

Second thing, we haven't added, modified, changed, deleted any of the present php scripts for MONTHS (we had never had any problem before -- nothing related to what is happening now) so I have absolutely no idea why it would start messing the database up just today.

Plus we don't have enough visitors to reach 72000 SQL request. Considering IPB is designed for minimum SQL requests and bandwidth usage, I'd estimate the number of SQL requests to 10 or 15 per visitor, per page.

What is even more weird is that the SQL error doesn't show up every time I load a page.
Check it: http://www.gbatemp.net
Sometimes you get the error, sometimes everything looks normal. .............?!?

[Arwen]

I'm still having the same problem - I ask PowWeb to re-enable the database, try to tweak the software as much as I know how, and every time, like clockwork, the database gets shut down again within 24 hours. I get no messages about a query maximum being reached; all I get is an e-mail saying my database is creating too many locked tables, nothing more. I've called tech support more than five times now, and I can never get more help than a reactivation of the database, which obviously doesn't last very long. I tried disabling pruning on my forum, as someone from the phpBB support forum suggested; apparently it didn't help at all.

The only other thing I can mention that could be remotely significant is that I've found when the forum is up (not much recently, as it is), sometimes when I try to post a message or delete a post and hit submit, I'll receive an error about phpbb_search_wordmatch (I don't remember the exact wording). When this would happen, I'd login to phpmyadmin and go to the list of tables, and that table would be labeled "in use." I'd use the repair command, and everything would work normally again. I don't know if this is relevant at all to the current problem, but I'm grasping at straws right now...

[RTH10260]

First, we've never had TOO MANY sql requests before. It's only when we recovered from the "locked tables" thingy that this showed up.Pure speculation, but I could think of situations where a server would not reset a usage counter correctly if it was blocked from execting to end (like it may have happened when the database was shut down in mid operation).

Did you try to run a Repair on all tables in the database ?
Plus we don't have enough visitors to reach 72000 SQL request. Considering IPB is designed for minimum SQL requests and bandwidth usage, I'd estimate the number of SQL requests to 10 or 15 per visitor, per page.

What is even more weird is that the SQL error doesn't show up every time I load a page.
Check it: http://www.gbatemp.net
Sometimes you get the error, sometimes everything looks normal. .............?!?The thing with the counter can be explained, it's a running total over the last hour. I once believed the resolution to be at one minute, so depending on the load sixty minutes back, one would get permission for a certain number of accesses and hit the limit and get blocked again.

Continuing with brainstorming:
I believe you see the number of visitors listed on the website and in the admin panels. But do you also see what ectivity they are performing ? Did you crosscheck with the webserver access log ? Are you sure non of the visitors is a bot that is hitting part of the site and driving traffic and load high ? If it's a bot, it may just be sitting there until your forum comes back and continues to plague the site.

[RTH10260]

The only other thing I can mention that could be remotely significant is that I've found when the forum is up (not much recently, as it is), sometimes when I try to post a message or delete a post and hit submit, I'll receive an error about phpbb_search_wordmatch (I don't remember the exact wording). When this would happen, I'd login to phpmyadmin and go to the list of tables, and that table would be labeled "in use." I'd use the repair command, and everything would work normally again. I don't know if this is relevant at all to the current problem, but I'm grasping at straws right now...
I have seen this issue happen over and over with phpbb, this problem is a regular in the posts since I read the forum. It must be some bad piece of code that get's regular hits. That a database file remains 'open' is for me an indication that a server process got killed and couldn't proceed to end and clean up neatly. Possibly that this one application functionality hits on some Powweb implementation limit of MySql.

[shaunj66]

Pure speculation, but I could think of situations where a server would not reset a usage counter correctly
I thought yeah, maybe the server doesn't reset the counter correctly... but there's nothing I can do for that.

Did you try to run a Repair on all tables in the database ?
yes, I've done that a LOT of times, like once a day for a few days (from the day we got our database re-enabled)

Now it *seems* we're not getting the error anymore.
I've tried to load 15 pages and they all worked fine (no more sql errors).

Well let's just hope the situation gets stable.

[Arwen]

I'm slowly trying to sort through my mess, and after doing a bit of research, I don't think it's accurate that PowWeb tech support is telling me that the reason I'm being shut down is that I'm going over the query limits. I believe I'm correct in this assumption because:

A) I've split my database over three users already
B) With an average of about 575 posts per day, and probably an average of 10-15 users online at once, I don't see this as anywhere near an extremely large forum; I believe I'm still in the category of "Most users will never reach these limits" that PowWeb states
C) I've never received an error message telling me I've reached the maximum queries, only that too many tables are locking
D) It is my understanding that if a user goes over a query limit, the database will go down for an hour and then go back up, which has never happened in my case

PowWeb tech support sent me over to the phpBB forums to look for ways to optimize queries, but looking at the situations of the people coming up with ways of optimization, I see that I'm nowhere NEAR close to the size of their forums. These people are talking about forums with up to 1000 users online at once, threads that have up to 72,000 replies, and up to 50,000 posts per day. I'm just nowhere near these figures. Yes, I have a few threads that stretch on for a couple hundred pages, but would these few really crash the whole database?

PowWeb support didn't seem to think this was a big issue, but I still see the 1016 error as related to my database being shut down. When I'd be around to observe this error, and be able to repair it in phpMyAdmin, the forum would stay up for a few hours. When I wouldn't be around to correct this error (in the late evening, early morning, etc.), that would be when I'd receive the e-mail saying that too many tables were locking and the database had been disabled. Obviously I can't stay on 24/7 to repair corrupted tables. From reading the phpBB support forums, it seems that there's a way that server administrators can perform a more intensive repair on tables than phpMyAdmin can. Is this possible?

I know that in the end, it's still a good idea for me to optimize my forum as much as possible, but I don't see this as the key to stopping my database going down all the time. Any thoughts, suggestions, ideas, etc.? Thanks!

[shaunj66]

ever since I disabled the "lock" privilege I've had no problem with locked tables (and havent had the "max sql requests" SQL errors either).

but now the servers are having hard time... often down or slow.
I guess its going to solve by itself