locked tables

Print Thread

Rate Thread

locked tables

#269830 02/24/2004 12:56 PM

Joined: Oct 2002

Posts: 71

VWDerf

Power User

VWDerf

Power User

Joined: Oct 2002

Posts: 71

With my current instalation of 6.4 I seem to have a lot of locked tables. Processor usage is never too bad, but here is an example:

Code

  <br />+---------+------+----------------+------------------------------------------------------------------------------------------------------+    <br />| Command | Time | State          | Info                                                                                                 |    <br />+---------+------+----------------+------------------------------------------------------------------------------------------------------+    <br />| Sleep   | 384  |                |                                                                                                      |    <br />| Sleep   | 327  |                |                                                                                                      |    <br />| Query   | 10   | Sorting result | SELECT t1.B_Number,t1.B_Main,t3.U_Username,t1.B_Subject,t1.B_Posted,t1.B_Board,t3.U_Color,t1.B_Reged |    <br />| Query   | 7    | Locked         | UPDATE w3t_Posts                                                                                          <br />				         SET    B_Counter = B_Counter + 1                                                                     <br />				         WHERE  B_Number = 672808         |                                                                   <br />| Query   | 6    | Locked         | SELECT t1.B_Number,t1.B_Parent,t2.U_Username,t1.B_Posted,t1.B_Last_Post,t1.B_Subject,t1.B_Main,t1.B_ |    <br />| Query   | 6    | Locked         | SELECT B_Main,B_Board                                                                                     <br />				      FROM   w3t_Posts                                                                                        <br />				      WHERE  B_Number = 674709                          |                                                     <br />| Query   | 6    | Locked         | SELECT B_Main,B_Board                                                                                     <br />				      FROM   w3t_Posts                                                                                        <br />				      WHERE  B_Number = 358032                          |                                                     <br />| Query   | 5    | Locked         | SELECT t1.Bo_Title,t1.Bo_Description,t1.Bo_Keyword,t1.Bo_Total,t1.Bo_Last,t1.Bo_Number,t1.Bo_Moderat |    <br />| Query   | 5    | Locked         | SELECT B_Main,B_Board                                                                                     <br />				      FROM   w3t_Posts                                                                                        <br />				      WHERE  B_Number = 663570                          |                                                     <br />| Query   | 5    | Locked         | SELECT B_Main,B_Board                                                                                     <br />				      FROM   w3t_Posts                                                                                        <br />				      WHERE  B_Number = 120186                          |                                                     <br />| Query   | 5    | Locked         | SELECT B_Main, B_Last_Post,B_Subject,B_Rating,B_Rates,B_RealRating,B_Board                                <br />				      FROM   w3t_Posts                                                                                        <br />				   |                                                                                                          <br />| Query   | 3    | Locked         | SELECT w3t_Favorites.F_Thread,w3t_Posts.B_Subject,w3t_Favorites.F_Number,w3t_Posts.B_Board,w3t_Favor |    <br />| Query   | 3    | Locked         | SELECT B_Main,B_Board                                                                                     <br />				      FROM   w3t_Posts                                                                                        <br />				      WHERE  B_Number = 676228                          |                                                     <br />| Query   | 2    | Locked         | SELECT B_Main,B_Board                                                                                     <br />				      FROM   w3t_Posts                                                                                        <br />				      WHERE  B_Number = 673286                          |                                                     <br />| Query   | 1    | Locked         | SELECT t1.B_Number,t1.B_Parent,t2.U_Username,t1.B_Posted,t1.B_Last_Post,t1.B_Subject,t1.B_Main,t1.B_ |    <br />| Query   | 0    | Locked         | SELECT t1.B_Number,t1.B_Parent,t2.U_Username,t1.B_Posted,t1.B_Last_Post,t1.B_Subject,t1.B_Main,t1.B_ |    <br />| Query   | 0    | Locked         | SELECT B_Main,B_Board                                                                                     <br />				      FROM   w3t_Posts                                                                                        <br />				      WHERE  B_Number = 676106                          |                                                     <br />| Query   | 0    |                | show processlist                                                                                          <br />

Is it normal for so many locked processes? Could this be what is causing me all these slowdowns during peak times even though processor usage isn't that high?

Fred

Sponsored Links

Re: locked tables LCarter #269831 02/24/2004 8:34 PM
Joined: Oct 2001 Posts: 213 Cleveland, Ohio little_joe Member
little_joe Member Joined: Oct 2001 Posts: 213 Cleveland, Ohio	We are experiencing the exact same thing w/ 6.4.1! It's become extremely frustrating, and I have worked with Rick on it but don't have any success to report. His advise was to hang in there until 6.5. He didn't seem to know why the table-locks were occuring. We tweaked my.cnf to maximize memory usage; our disk and cpu util is very low. I have also removed some search features - unregistered/non-logged-in users cannot search; no-one can search all forums - from some advice here. This didn't do much on ours....... Otherwise, like us you are probably SOL. Combined w/ some other "oversights" (ie sticky post problem), 6.4.1 is a real stinker release. joe

Re: locked tables JoshPet #269832 02/25/2004 1:11 AM
Joined: Oct 2002 Posts: 71 VWDerf Power User
VWDerf Power User Joined: Oct 2002 Posts: 71	Well, I guess it's good to know that I'm not the only one. There were a few issues like this on 6.3 but not nearly as bad as 6.4. Hopefully 6.5 will fix this.... Can't wait (nor can my users). Fred

Re: locked tables LCarter #269833 02/26/2004 8:47 AM
Joined: Oct 2001 Posts: 213 Cleveland, Ohio little_joe Member
little_joe Member Joined: Oct 2001 Posts: 213 Cleveland, Ohio	Fred, check your my.cnf for MySQL. I just looked at ours again last night and we had an entry "log-bin". This was writing every single MySQL transaction to a flat file. Obviously this is additional overhead on the server; the www-bin.0xx files under /var/lib/mysql were enormous (600MB to 1GB). After deleting log-bin from my.cnf and restarting MySQL, our site immediately sped up - not just the forums, but the whole site. The lift on the forums has been very pleasant. This didn't eliminate the locked tables problem, but it helped a great deal. And I also freed up about 10GB of disk space.

Hop To

Link Copied to Clipboard

UBB.Developers Forums Community Development Programming MySQL and Server Configuration locked tables