UBB.threads PHP Forum Software Community Forums UBB.threads 6 Version 6 Support MySQL Processes Locking Up

I've had trouble with MySQL dying in the wee hours of the morning. Today, it was 5:41am. When I checked the processes, there were 56 locked processes tied to my problem Threads database. I have repaired and optimized this database in the last week or two, but the problem persists. I have another instance of Threads on this server, but it's less-active database did NOT show any locked processes.

I've been told that MySQL can become zombie if there are a lot of locked processes - that sounds like my problem.

Any chance you can post the process list?

Here's the process list as copied from phpMyAdmin. Notice that nearly every process is locked. This was copied while MySQL was not responding.

76134
root
localhost
ubb-realtree
Query
567
Locked
SELECT t1.Bo_Title, t1.Bo_Description, t1.Bo_Keyword, t1.Bo_Total, t1.Bo_Last, t1.Bo_Number, t1.Bo_Moderat

76151
root
localhost
ubb-realtree
Query
581
Locked
SELECT t1.Bo_Title, t1.Bo_Description, t1.Bo_Keyword, t1.Bo_Total, t1.Bo_Last, t1.Bo_Number, t1.Bo_Moderat

76157
root
localhost
ubb-realtree
Query
582
Locked
SELECT t1.Bo_Title, t1.Bo_Description, t1.Bo_Keyword, t1.Bo_Total, t1.Bo_Last, t1.Bo_Number, t1.Bo_Moderat

76158
root
localhost
ubb-realtree
Query
644
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76160
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76161
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76162
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76164
root
localhost
ubb-realtree
Query
644
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76165
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76166
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76167
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76168
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76169
root
localhost
ubb-realtree
Query
598
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76170
root
localhost
ubb-realtree
Query
644
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76172
root
localhost
ubb-realtree
Query
644
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76177
root
localhost
ubb-realtree
Query
644
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76180
root
localhost
ubb-realtree
Query
644
Sorting result
SELECT U_Username, U_Number
FROM w3t_Users
WHERE U_Approved = 'yes'
ORDER BY U_Number DESC

76181
root
localhost
ubb-realtree
Query
513
Locked
SELECT COUNT( * )
FROM w3t_Users
WHERE U_Approved = 'yes'

76183
root
localhost
ubb-realtree
Query
518
Locked
SELECT COUNT( * )
FROM w3t_Users
WHERE U_Approved = 'yes'

76186
root
localhost
ubb-realtree
Query
513
Locked
SELECT COUNT( * )
FROM w3t_Users
WHERE U_Approved = 'yes'

76188
root
localhost
ubb-realtree
Query
511
Locked
SELECT COUNT( * )
FROM w3t_Users
WHERE U_Approved = 'yes'

76189
root
localhost
ubb-realtree
Query
509
Locked
SELECT COUNT( * )
FROM w3t_Users
WHERE U_Approved = 'yes'

76190
root
localhost
ubb-realtree
Query
503
Locked
SELECT COUNT( * )
FROM w3t_Users
WHERE U_Approved = 'yes'

76191
root
localhost
ubb-realtree
Query
517
Locked
SELECT COUNT( * )
FROM w3t_Users
WHERE U_Approved = 'yes'

76193
root
localhost
ubb-realtree
Query
586
Locked
UPDATE w3t_Users SET U_SessionId = 'ff49cc40a8890e6a60f40ff3026d2730'WHE

76194
root
localhost
ubb-realtree
Query
565
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76195
root
localhost
ubb-realtree
Query
562
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76196
root
localhost
ubb-realtree
Query
546
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76198
root
localhost
ubb-realtree
Query
533
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76199
root
localhost
ubb-realtree
Query
525
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76200
root
localhost
ubb-realtree
Query
512
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76201
root
localhost
ubb-realtree
Query
483
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76202
root
localhost
ubb-realtree
Query
440
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76203
root
localhost
ubb-realtree
Query
427
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76204
root
localhost
ubb-realtree
Query
413
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76205
root
localhost
ubb-realtree
Query
383
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76206
root
localhost
ubb-realtree
Query
380
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76207
root
localhost
ubb-realtree
Query
356
Locked
SELECT U_Display, U_Groups, U_Sort, U_View, U_PostsPer, U_TempRead, U_FlatPosts, U_TimeOffset, U_Acti

76208
root
localhost
ubb-realtree
Query
348
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76210
root
localhost
ubb-realtree
Query
309
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76211
root
localhost
ubb-realtree
Query
307
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76212
root
localhost
ubb-realtree
Query
305
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76214
root
localhost
ubb-realtree
Query
285
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76217
root
localhost
ubb-realtree
Query
261
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76219
root
localhost
ubb-realtree
Query
247
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76220
root
localhost
ubb-realtree
Query
239
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76221
root
localhost
ubb-realtree
Query
236
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76222
root
localhost
ubb-realtree
Query
229
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76224
root
localhost
ubb-realtree
Query
211
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76225
root
localhost
ubb-realtree
Query
206
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76226
root
localhost
ubb-realtree
Query
202
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76228
root
localhost
ubb-realtree
Query
187
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76230
root
localhost
ubb-realtree
Query
182
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76232
root
localhost
ubb-realtree
Query
163
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76233
root
localhost
ubb-realtree
Query
153
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76234
root
localhost
ubb-realtree
Query
135
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76235
root
localhost
ubb-realtree
Query
135
Locked
SELECT U_Display, U_Groups, U_PostsPer, U_PicturePosts, U_FlatPosts, U_TempRead, U_TimeOffset, U_Show

76243
root
localhost
ubb-realtree
Query
110
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76245
root
localhost
ubb-realtree
Query
105
Locked
SELECT U_Groups, U_TimeOffset, U_Display, U_Username, U_Password, U_SessionId, U_StyleSheet, U_Status, U_

76246
root
localhost
ubb-realtree
Query
98
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76247
root
localhost
ubb-realtree
Query
97
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76248
root
localhost
ubb-realtree
Query
82
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76249
root
localhost
ubb-realtree
Query
79
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76250
root
localhost
ubb-realtree
Query
76
Locked
SELECT U_Display, U_Groups, U_PostsPer, U_PicturePosts, U_FlatPosts, U_TempRead, U_TimeOffset, U_Show

76251
root
localhost
ubb-realtree
Query
67
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76253
root
localhost
ubb-realtree
Query
53
Locked
SELECT U_Display, U_Groups, U_PostsPer, U_PicturePosts, U_FlatPosts, U_TempRead, U_TimeOffset, U_Show

76255
root
localhost
ubb-realtree
Query
36
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76259
root
localhost
ubb-realtree
Query
29
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76260
root
localhost
ubb-realtree
Query
22
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76261
root
localhost
ubb-realtree
Query
18
Locked
SELECT U_FrontPage, U_Groups, U_TimeOffset, U_Display, U_Favorites, U_WhichForums, U_Categories, U_Userna

76267
root
localhost
mysql
Query
0

Is the process list above helpful for anyone that might be able to diagnose this problem?

Try using this one as a SQL command in the threads.

SHOW PROCESSLIST

Should this be done now or when MySQL stops responding again?

Yes if possible, perhaps youc an have a second browser window open to the admin panel and the sql command, so you can process it. BUT, if the server is bogged down, you may be unable or add to the existing load by processing another query. Kinda a catch 22 scenario.

When MySQL crapped out this morning, I tried to access the admin section of the board to run the query, but I couldn't get to it. It appears that the entire board is inaccessible when the database gets locked up.

Someone offline suggested upgrading from PHP 4.3.2 to 4.3.4 and MySQL 4.0.13 to 4.0.17. Would either of these upgrades help with this issue?

The mysql upgrade would definitely be something to look into. Lots and lots of bug fixes between 4.0.13 and 4.0.17

I'm working on the upgrades, but I wanted to post another detail in the meantime...

When UBB.Threads installation #1 becomes inaccessible, installation #2 is fine. I can also access other MySQL databases and phpMyAdmin.

I don't know if/how this impacts the discussion and troubleshooting, but I thought it might be useful.

That is very helpful. It means something is locking one table on this installation, not the entire MySQL server itself. That is good information.

[]That is very helpful. It means something is locking one table on this installation, not the entire MySQL server itself. That is good information.[/]Can you provide any more detailed troubleshooting information beyond the upgrades listed previously? I'm desperate to resolve this issue, but I have very little experience with MySQL.

The best place for MySQL troubleshooting is from the MySQL folks themselves. They make it, that have excellent support, better than we could do.

That said -

There are some excellent tuning guides posted at threadsdev.com here, here, and here.

Great reads, all three.

I noticed that in the processlist you posted that all of the waits and locks seemed to be in your Users table. Have you optimized it recently? That can help. Close the board, backup that table, then optimize it. Optimze all of your tables regularly if you can, it helps.

If you're having continual problems in the Users table then you might want to prune. Have members that haven't logged in to the board in the last year? Last 6 months? pune them, then optimize the table after they have been pruned.

Give those tuning threads a thorough reading and give them a try. I know that they have helped a lot of people.

I am having the EXACT same problem as this person is having. However I'm on MySQL v3.23.58. I have tried to downgrade to earlier versions but not to 4.x yet. We're running UBBThreads v6.2.2.

Our problems started when we had to switch to a new server. We were on Redhat 7.x and the new server is Redhat 9. However the database version is the same on both. We're using all the stock redhat binaries, but I did try a custom built MySQL (both older and the same versions) with the exact same result.

Using the advice I learned here I pruned our users table, which removed 10k plus users. But that didn't solve it, we still very often have the db lock and the processlist shows tons of LOCKED tables (all on the users table). Speaking of that, is there any way to bring those users back? I did a dump of the database prior to pruning it. Would re-importing the users table work, or would that break other things?

Here's what I've tried so far:
I have optimized the table(s)
I have run repairs and myisamchk's many times
I have completely dumped and re-imported the data
I have pruned the users table
I have tried multiple versions of mysql
I have read through the mysql tuning and troubleshooting documentation and made many configuration changes and followed their advice

None of these has fixed the problem. Does anyone have any idea what's going on and how to fix it? I was so relieved to read that I wasn't alone in this and it's given me hope that perhaps someone has figured this out and has a fix.

ANY help would be most appreciated!

Thanks!

Cheers,
John

One thing you might want to check in your mysql.cnf is if you have it set to log all queries. Look for log-bin. This can really slow things down. Just received a notification from one of our users that this was turned on in his mysql config and after turning it off it made a huge difference.

Nope, logging is not on, just the error log, which incidentally is completely uninformative. This is a far more serious problem than a config issue I think.

Hmm. This isn't looking good. :-) Kernel-Panic, did you ever figure out the problem on your end?

Cheers,
John

Since it seems this is related to the server move it's hard to say for sure what's causing it. Do you have a copy of the old mysql.cnf that was used on the old server? Possibly doing a comparison between what you had and what is being used now might help to see if there are config differences causing the excessive locks.

Any big differences in memory, cpu, etc between your old and current server?

Hi Rick,

I should have clarified that it wasn't a server move so much, but a hard drive failure. They replaced our hard drive with a new one, and the image was Redhat 9, not 7.x. The physical machine didn't change. I did have backups and yes the my.cnf is the same. I've since made some changes, but I did check that out.

I'm really at a loss to explain it.

I don't know exactly what the relation is - but I was talking to Jeremy the other day. We both have had trouble on servers with MySQL and Red Hat 9. Problems seemed to popup that werent' there in 7.3.

[]I don't know exactly what the relation is - but I was talking to Jeremy the other day. We both have had trouble on servers with MySQL and Red Hat 9. Problems seemed to popup that werent' there in 7.3. [/] Well that's been our experience as well. We do plan to upgrade to RHEL soon but wanted to work through this problem just in case it was unrelated.

John

I had a RedHat Enterprise edition for a few weeks. Not much better luck. <img src="https://www.ubbcentral.com/boards/images/graemlins/crazy.gif" alt="" />

Finally have settled on Fedora - and it's working well. <img src="https://www.ubbcentral.com/boards/images/graemlins/smile.gif" alt="" />