Hello,
I have reviewed the slow query log and checked some of the tables in mylibarc_mylibar
# Time: 150428 9:56:57
# User@Host: mylibarc_nenoze[mylibarc_nenoze] @ localhost [] # Query_time: 30.139146 Lock_time: 0.000230 Rows_sent: 1 Rows_examined: 54404820 use mylibarc_mylibar; SET timestamp=1430215017; SELECT COUNT(*) AS userplays, SUM(s.timefinish - s.timestart) AS userspent, SUM(d.gameid IS NOT NULL) AS user_trophies FROM dbtech_vbarcade_session AS s INNER JOIN dbtech_vbarcade_game AS g ON (g.gameid = s.gameid) LEFT JOIN dbtech_vbarcade_data AS d ON (d.local_winner = s.sessionid) WHERE g.enabled = 1 AND s.record > 2 AND s.userid = 2; # Time: 150428 9:58:39 # User@Host: mylibarc_nenoze[mylibarc_nenoze] @ localhost [] # Query_time: 102.021577 Lock_time: 0.000263 Rows_sent: 1 Rows_examined: 180934443 SET timestamp=1430215119; SELECT COUNT(*) AS myplays, SUM(s.timefinish - s.timestart) AS myspent, SUM(d.gameid IS NOT NULL) AS my_trophies FROM dbtech_vbarcade_session AS s INNER JOIN dbtech_vbarcade_game AS g ON (g.gameid = s.gameid) LEFT JOIN dbtech_vbarcade_data AS d ON (d.local_winner = s.sessionid) WHERE g.enabled = 1 AND s.record > 2 AND s.userid = 7; # Time: 150428 16:45:16 # User@Host: mylibarc_nenoze[mylibarc_nenoze] @ localhost [] # Query_time: 10.674630 Lock_time: 0.000072 Rows_sent: 0 Rows_examined: 0 SET timestamp=1430239516; insert into cometchat_status (userid,lastactivity) values ('111','1430239505') on duplicate key update lastactivity = '1430239505'; # User@Host: mylibarc_nenoze[mylibarc_nenoze] @ localhost [] # Query_time: 10.318205 Lock_time: 0.000078 Rows_sent: 0 Rows_examined: 0 SET timestamp=1430239516; insert into cometchat_status (userid,lastactivity) values ('1','1430239506') on duplicate key update la
You should be able to improve performance by deleting rows in
-rw-rw---- 1 mysql mysql 18M Apr 28 18:30 dbtech_vbactivity_pointslog.ibd
-rw-rw---- 1 mysql mysql 18M Apr 28 18:34 dbtech_vbarcade_session.ibd
-rw-rw---- 1 mysql mysql 16M Apr 27 16:50 dbtech_registration_redirect_log.ibd
-rw-rw---- 1 mysql mysql 14M Apr 28 03:32 dbtech_registration_tracking.MYD
-rw-rw---- 1 mysql mysql 10M Apr 28 17:57 dbtech_vbarcade_data.ibd
-rw-rw---- 1 mysql mysql 9.0M Apr 28 17:51 dbtech_thanks_entrycache.ibd
those tables that are more than a few weeks old. Timestamps for
dbtech_vbarcade_session
go back to early 2013
As these tables grow, the longer it will take to do the queries involving selecting and joining these tables.
If there is a way to clear the data from the vBulletin admin area, I encourage you to do so.
Otherwise, with your permission, we can delete rows from dbtech_vbactivity_pointslog dbtech_vbarcade_session dbtech_registration_redirect_log dbtech_registration_tracking
that are more than 3 weeks old.
Thanks,
Steve O.
VPS/Dedicated Administrator
