Jump to content

Locked Tables (mysql) And High Server Load: Help!

Guest Fast Lane!

By Guest Fast Lane!
in Classic self-hosted technical help

Recommended Posts

Fast Lane! Proficient

Fast Lane!

Posted
Posted

I notice that my server loads and memory have been spiking WAY high recently. I have php 4.4.2 and just upgraded to mysql 4.1.19. I have my my.cnf and httpd.conf tweaked and the mysqlreport program shows "a-ok" mysql performance.

The issue is when the load (loads of 20+) and memory spike way high I noticed that the mysql and tcp connections sky rocket as well sometimes exceeding my mysql limit (which stops ipb from working). Very quickly after loads and memory return to normal (loads of 2-3).

Here is a small portion of the output from the mysql command "show processlist;": (there are well over a hundred of these actually at a time)

| 108054 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 106 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22286 |


| 108057 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 103 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=302 |


| 108062 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 102 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=18224 |


| 108063 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 101 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=21877 |


| 108065 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 100 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22456 |


| 108066 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 99 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22448 |


| 108075 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 90 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22206 |


| 108082 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 85 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22315 |


| 108085 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 84 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22455 |


| 108088 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 84 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22446 |


| 108095 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 77 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22429 |


| 108096 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 78 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22433 |


| 108098 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 78 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22315 |


| 108101 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 77 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22458 |


| 108117 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 70 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=18817 |


| 108119 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 68 | Locked | UPDATE ibf_topics SET posts=12,topic_queuedposts=0,last_poster_id=14222,last_poster_name='arwensun19 |


| 108121 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 66 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22429 |


| 108124 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 65 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=6769 |


| 108129 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 63 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22206 |


| 108130 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 62 | Locked | UPDATE LOW_PRIORITY ibf_topics SET views=views+1 WHERE tid=22433



From the above it may indicate that ipb is updating the "viewed" stats for each forum, etc. This could be the issue but I am not sure. Here are other peopel with similar issues (one even on vbulletin):

http://forums.invisionize.com/index.php?sh...l=views=views+1
http://forums.invisionpower.com/index.php?showtopic=159722
http://www.vbulletin.com/forum/showthread.php?t=83379

Any suggestions?

When loads are normal the same command has this result:

mysql> show processlist;


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


| Id | User | Host | db | Command | Time | State | Info |


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


| 108643 | eximstats | localhost | eximstats | Sleep | 21 | | NULL |


| 110416 | root | localhost | NULL | Query | 0 | NULL | show processlist |


| 110605 | forumdb_forumdb2 | localhost | forumdb_forums2 | Sleep | 0 | | NULL |


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

Fast Lane! Proficient

Fast Lane!

Posted
Posted

Update:

I have changed the settings to only update the topic views periodiclyy as opposed to in real time. I changed the task manager task to run every 10 minutes so the views update every 10 minutes :). So far so good!

Fast Lane! Proficient

Fast Lane!

Posted
Posted

Still the same problem. Just happened again:

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



--------------------------------------------------------------------------------------------



--------+


| Id | User | Host | db | Command | Time | State |



Info



|


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



--------------------------------------------------------------------------------------------



--------+


| 119065 | eximstats | localhost | eximstats | Sleep | 57 | |





|


| 125729 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 173 | Sending data |



SELECT msg_id FROM ibf_message_text WHERE msg_deleted_count >= msg_sent_to_count



|


| 125757 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 11 | Sorting result |



SELECT * FROM ibf_posts WHERE topic_id=27 AND queued=0 AND post_date > 1153108805 ORDER BY



post_date |


| 125818 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 138 | Locked |



INSERT INTO ibf_message_text



(msg_date,msg_post,msg_cc_users,msg_sent_to_count,msg_post_key,msg_auth |


| 125874 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 75 | Locked |



UPDATE ibf_members SET posts=posts+1, last_post=1153267892 WHERE id=16431



|


| 125900 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 102 | Locked |



SELECT m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,


m.warn_lev |


| 125916 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 72 | Locked |



UPDATE ibf_members SET posts=posts+1, last_post=1153267895 WHERE id=18430



|


| 125925 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 91 | Locked |





|


| 125927 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 89 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125928 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 89 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125935 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 87 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125936 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 87 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125938 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 87 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125939 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 86 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125940 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 85 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125941 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 85 | Locked |



SELECT bday_day, bday_month, id, members_display_name FROM ibf_members WHERE bday_month=7



|


| 125942 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 83 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125943 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 82 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125944 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 80 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125946 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 79 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125948 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 79 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125952 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 78 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125954 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 77 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125955 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 77 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125956 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 77 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125957 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 77 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125958 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 76 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125959 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 75 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125960 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 74 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125961 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 72 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125962 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 71 | Locked |



SELECT u.id AS id, u.name AS username, u.member_login_key AS password, u.mgroup+100 AS



group_id FROM |


| 125965 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 71 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125966 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 70 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125967 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 70 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125969 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 69 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125970 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 68 | Locked |



SELECT p.*, m.members_display_name, m.mgroup


FROM ibf_posts p


LEFT JOIN ibf_members m ON ( |


| 125971 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 68 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125972 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 67 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125973 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 67 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125974 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 66 | Locked |



SELECT bday_day, bday_month, id, members_display_name FROM ibf_members WHERE bday_month=7



|


| 125975 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 66 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125976 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 66 | Locked |





|


| 125977 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 65 | Locked |



SELECT bday_day, bday_month, id, members_display_name FROM ibf_members WHERE bday_month=7



|


| 125978 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 65 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125979 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 65 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125980 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 64 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125981 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 64 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125983 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 62 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125984 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 62 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125985 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 61 | Locked |



SELECT m.*, me.*, s.location_1_id, s.location_2_id, s.location_1_type, s.location_2_type,



s.running_ |


| 125986 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 61 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125987 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 60 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125988 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 59 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125989 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 59 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 125990 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 58 | Locked |



UPDATE ibf_sessions SET member_name='girl



37',member_id=12800,member_group=3,login_type=1,running_ti |


| 125991 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 58 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='295a3ca4661211f5ac10c29bc9f |


| 125992 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 58 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='208343eea35a4439ed3ea2678df |


| 125993 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 57 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='a5f3779b18645d77efe1b023246 |


| 125994 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 57 | Locked |



SELECT m.*, me.*, s.location_1_id, s.location_2_id, s.location_1_type, s.location_2_type,



s.running_ |


| 125995 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 56 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='52998bf8f941ff59f8d8d989c3e |


| 125996 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 55 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='d53bd0826a1278be83ba61da487 |


| 125997 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 55 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='dd7f483a07ee3875d370e5c26dd |


| 125998 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 55 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 125999 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 54 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='30c1958c1685be43255b20c51ca |


| 126000 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 54 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='e2aebecb7a4ab2a7e67c6ec999b |


| 126001 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 53 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='1034bc2c9f2b670541ff9514636 |


| 126003 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 52 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='5912758c92b92923accb67b7b94 |


| 126004 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 52 | Locked |



SELECT u.id AS id, u.name AS username, u.member_login_key AS password, u.mgroup+100 AS



group_id FROM |


| 126005 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 52 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='ee8575934b2717c20f55f483001 |


| 126006 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 51 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='363cec66a7954c8f17a08f5a34a |


| 126007 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 51 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='208343eea35a4439ed3ea2678df |


| 126008 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 50 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='5f07c5ee1becbe30e628cae9a37 |


| 126009 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 50 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='94fa3775e646b020cde9c21cda2 |


| 126010 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 49 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='aa40802be24848abb4a869146c6 |


| 126011 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 49 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='822f72831b07b4563212e31bcbd |


| 126012 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 48 | Locked |



SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location,



s.running_time, t |


| 126013 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 47 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='5f07c5ee1becbe30e628cae9a37 |


| 126014 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 47 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 126015 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 45 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='c2da371e7b1f1683da7659ca053 |


| 126016 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 44 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 126017 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 44 | Locked |



SELECT u.id AS id, u.name AS username, u.member_login_key AS password, u.mgroup+100 AS



group_id FROM |


| 126018 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 44 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='091080925833c44ac81dddd2f44 |


| 126019 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 43 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 126020 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 42 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='d53bd0826a1278be83ba61da487 |


| 126021 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 41 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='355bee777c4cb47a6cadc0f8adb |


| 126022 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 39 | Locked |



SELECT u.id AS id, u.name AS username, u.member_login_key AS password, u.mgroup+100 AS



group_id FROM |


| 126023 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 39 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='6cfc810ed370cb5c304d48b07ac |


| 126024 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 39 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='e2aebecb7a4ab2a7e67c6ec999b |


| 126025 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 39 | Locked |



DELETE FROM ibf_sessions WHERE ip_address='24.117.249.205'



|


| 126026 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 39 | Locked |



SELECT p.*,


m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit,



m.last_activity,m.login |


| 126027 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 38 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='c2da371e7b1f1683da7659ca053 |


| 126028 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 37 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='fa4884dcf99ce6899e64557885a |


| 126029 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 37 | Locked |



DELETE FROM ibf_sessions WHERE ip_address='209.240.101.155'



|


| 126031 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 35 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='6141237c42b1014f86b223a8615 |


| 126032 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 35 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='fa4884dcf99ce6899e64557885a |


| 126033 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 33 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 126034 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 32 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='5a79971c35b9c490463ba3544b4 |


| 126035 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 28 | Locked |



SELECT id, name, mgroup, member_login_key, email, restrict_post, view_sigs, view_avs,



view_pop, view |


| 126036 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 28 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='71b916dbd6aa1a056f3e9b40fc8 |


| 126037 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 28 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='1b4ba138b5fce685efc3fab725e |


| 126038 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 27 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='aa40802be24848abb4a869146c6 |


| 126039 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 27 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='333dd446e32ac4f68a993382a60 |


| 126040 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 27 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='360351142f353cd4f517165983d |


| 126041 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 27 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='7cb06d3aeaee82d8313302955d6 |


| 126042 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 27 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='6796b9826495a175a95a50086c4 |


| 126043 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 27 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='c45c985ab211ba9d42f49bfbf6b |


| 126044 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 26 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='35df798e24f17e08e3849bfcde7 |


| 126045 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 25 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='962bd61bffb24bb69c9dac4e33d |


| 126046 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 25 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='57b5a3dea975f352eb7659f83d0 |


| 126048 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 24 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='38fff8736b675f8dae37ba5efd3 |


| 126049 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 23 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='d9d532ad80ea5738497515afef2 |


| 126050 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 21 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='71b916dbd6aa1a056f3e9b40fc8 |


| 126051 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 20 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='94fa3775e646b020cde9c21cda2 |


| 126052 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 20 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='70943630d903c6813a0fc2ccfa6 |


| 126053 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 20 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='baf7bf78f7902b7b0346c82816a |


| 126055 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 19 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='71b916dbd6aa1a056f3e9b40fc8 |


| 126057 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 19 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='52998bf8f941ff59f8d8d989c3e |


| 126059 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 18 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='add48d5e513dbaed1b767c34482 |


| 126060 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 18 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='8e8f945a80b03e40ee929fcd938 |


| 126061 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 17 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='d2ea6eebab3d1229bca012957a1 |


| 126062 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 17 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='6cfc810ed370cb5c304d48b07ac |


| 126063 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 17 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='21789fe05651c9b514cfc6d7b97 |


| 126064 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 17 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='d81aa179c15d51e55ba645dbf29 |


| 126065 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 15 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='6d50f2b93f8afe5aed96f41d648 |


| 126066 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 15 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='c2da371e7b1f1683da7659ca053 |


| 126067 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 14 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='962bd61bffb24bb69c9dac4e33d |


| 126068 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 13 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='e499df36975c9318f717604105e |


| 126069 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 13 | Locked |



SELECT id, member_id, member_name, login_type, running_time, member_group FROM ibf_sessions



WHERE ru |


| 126071 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 12 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='0dfa6702f74c066e7bddd23b153 |


| 126072 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 11 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='c2da371e7b1f1683da7659ca053 |


| 126073 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 11 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='e499df36975c9318f717604105e |


| 126075 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 10 | Locked |



DELETE FROM ibf_sessions WHERE ip_address='65.103.235.119'



|


| 126076 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 9 | Locked |



DELETE FROM ibf_sessions WHERE ip_address='66.108.168.142'



|


| 126077 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 9 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='70943630d903c6813a0fc2ccfa6 |


| 126078 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 9 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='71b916dbd6aa1a056f3e9b40fc8 |


| 126079 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 8 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='70943630d903c6813a0fc2ccfa6 |


| 126080 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 8 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='94fa3775e646b020cde9c21cda2 |


| 126081 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 5 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='e499df36975c9318f717604105e |


| 126082 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 5 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='b5d964e30c6267188288e191fd8 |


| 126083 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 4 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='464a3daedc736ccf94521122589 |


| 126084 | forumsdb_forumsdb2 | localhost | forumsdb_forums2 | Query | 2 | Locked |



SELECT id, member_id, running_time, location FROM ibf_sessions WHERE



id='71b916dbd6aa1a056f3e9b40fc8 |


| 126085 | root | localhost | | Query | 0 | |



show processlist



|


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



--------------------------------------------------------------------------------------------



--------+

bfarber Grand Master

bfarber

Posted
Posted

I'd submit a ticket - your first action resolved the first issue, but now your queries are getting locked like that trying to pull a single post it looks like. :blink: Any modifications? What kind of traffic when this happened? Looks like your max mysql connections is what, 500 or so - which should be sufficient. MyISAM tables I assume, otherwise they wouldn't lock (MyISAM being probably the better choice on a single mysql server in most situations).

I'd submit a ticket..

Fast Lane! Proficient

Fast Lane!

Posted
Posted

I'd submit a ticket - your first action resolved the first issue, but now your queries are getting locked like that trying to pull a single post it looks like. :blink: Any modifications? What kind of traffic when this happened? Looks like your max mysql connections is what, 500 or so - which should be sufficient. MyISAM tables I assume, otherwise they wouldn't lock (MyISAM being probably the better choice on a single mysql server in most situations).



I'd submit a ticket..



Yeah I just submitted a ticket. THe tables are indeed MyISAM. Only one small mod that has been there for ages (related topics). It has never caused an issue in the past. I prefer to only "skin" the forum as mods add instability and make upgrades a pain. At the time of the spike the load was average to below average ( < 1 - 2 ). When I was doing extra debuging I was able to reproduce a spike when I did a regular forums search. The search immediately jumped the load and locked tons of queries. I could not reproduce it every time but it is all I have to work on so far. It seems that if the search takes a long while to complete ("stalls") then the table remains locked and things pile up (my only guess).

Archived

This topic is now archived and is closed to further replies.

Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
Invision Community © 2024 IPS, Inc.
×
×
  • Create New...