#39471
02/02/2004 2:32 PM
|
Joined: Jan 2004
Posts: 31
newbie
|
newbie
Joined: Jan 2004
Posts: 31 |
Since I upgraded our Threads from 6.3.2 to 6.4.1, it has been painfully slow. Initially we were consistently seeing sub-second query times, but as the user load escalates (around 150 users on up), it is just dead - 40 seconds to generate the main page (56 queries). We see 25-40 secs to generate pages with <10queries. And some times, we see sub-second response.
I have optimized tables, tweaked my.cnf to maximize use of our 1GB of RAM. No other changes were made to the server since the upgrade, and the server load has been consistent since prior to the upgrade.
I would appreciate any other suggestions.
|
|
|
#39472
02/02/2004 2:52 PM
|
Joined: Dec 2003
Posts: 611
Former Developer
|
Former Developer
Joined: Dec 2003
Posts: 611 |
When logged in as an admin, visit this URL: http://www.4x4wire.com/forums/ubbthreads.php?debug=1You will see a list of the queries made to generate the page normally. Which of those take the longest to execute?
Charles Capps Former UBB.classic Maintainer
|
|
|
#39473
02/02/2004 4:01 PM
|
Joined: Jan 2004
Posts: 31
newbie
|
newbie
Joined: Jan 2004
Posts: 31 |
Hi Charles, nice to hear from you and thanks for the help.
I did this several times and got several sub-second page generation times. Then I got this:
Generated in 10.337 seconds in which 8.99 seconds were spent on a total of 24 queries. Zlib compression enabled.
Longest queries:
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 2 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.214 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 75 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.166 seconds.
Those two are identical? multiple instances of the same query?
The first one above is always the slowest, whenever we have the slowdown (which is happening a lot this afternoon).
|
|
|
#39474
02/02/2004 4:39 PM
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
Hmm, both of those queries were taking under a quarter of a second. Since it said the total mysql execution time was over 8 seconds are you sure there were no queries that took longer than that?
The queries aren't quite identical. Each of those queries grabs the forum information for forums within a certain category.
|
|
|
#39475
02/02/2004 5:04 PM
|
Joined: Jan 2004
Posts: 31
newbie
|
newbie
Joined: Jan 2004
Posts: 31 |
Hi Rick, here is a complete example. See the very last line. <img src="https://www.ubbcentral.com/boards/images/graemlins/confused.gif" alt="" />
Query: REPLACE INTO w3t_Online (O_Username , O_Uid , O_Last , O_What , O_Board , O_Extra , O_Read , O_Type) VALUES ('little_joe' , '87' , 'xxxxxxxxxxx' , 'ubbthreads' , '' , '' , '' , 'r') Query took a total of 0.039 seconds.
Query: SELECT t1.Mod_Board , t1.Mod_Uid , t2.U_Username FROM w3t_Moderators AS t1 , w3t_Users AS t2 WHERE t1.Mod_Uid = t2.U_Number table type possible_keys key key_len ref rows Extra t1 index Modindx1 Modindx1 104 66 Using index t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Mod_Uid 1 Query took a total of 0.048 seconds.
Query: SELECT C_Users , C_NewUsername , C_NewUid , C_Users_timestamp , C_Birthdays , C_Birthdays_timestamp , C_MaxOnline , C_MaxOnline_timestamp FROM w3t_Cache table type possible_keys key key_len ref rows Extra w3t_Cache system 1 Query took a total of 0.039 seconds.
Query: SELECT L_Board , L_Last FROM w3t_Last WHERE L_Uid = '87' table type possible_keys key key_len ref rows Extra w3t_Last ref PRIMARY PRIMARY 4 const 23 where used Query took a total of 0.016 seconds.
Query: SELECT O_Board , COUNT(*) , O_Type FROM w3t_Online WHERE O_Last > '1075757440' GROUP BY O_Board , O_Type table type possible_keys key key_len ref rows Extra w3t_Online ALL Oindx2 221 where used; Using temporary Query took a total of 0.013 seconds.
Query: SELECT DISTINCT Cat_Title , Cat_Number , Cat_Description FROM w3t_Category WHERE Cat_Type = 'forum' ORDER BY Cat_Number table type possible_keys key key_len ref rows Extra w3t_Category ALL type_ndx 17 where used; Using temporary Query took a total of 0.109 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 2 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.077 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 5 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.135 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 10 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 7 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.045 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 15 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 4 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 20 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 25 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.011 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 30 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 35 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 40 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.001 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 45 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 50 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 55 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 60 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 65 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.001 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 70 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.001 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 75 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 100 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter table type possible_keys key key_len ref rows Extra t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1 t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1 Query took a total of 0.002 seconds.
4x4Wire.com | Contact Us | Privacy statement
UBB.threads™ 6.4.1
Generated in 21.559 seconds in which 19.261 seconds were spent on a total of 24 queries. Zlib compression enabled.
|
|
|
#39476
02/02/2004 10:32 PM
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
Strange, all of those individual query times don't add up to the full mysql execution time. What you might try is during a slow period you can run the following from the sql command prompt:
SHOW PROCESSLIST
That will give you a list of the queries being run at that time and if any of them are locking the table. I'm guessing it could be related to the search which has some improvements coming in 6.5.
|
|
|
#39477
02/03/2004 9:49 AM
|
Joined: Jan 2004
Posts: 31
newbie
|
newbie
Joined: Jan 2004
Posts: 31 |
Hey Rick, I'll try that later on today. I was on our board at 6am today, there were 28 users, and it was dead slow ie 25 secs, then sped up to sub-second page generation. This is really frustrating. <img src="https://www.ubbcentral.com/boards/images/graemlins/frown.gif" alt="" />
|
|
|
0 members (),
1,182
guests, and
197
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|