PDA

View Full Version : Clearing GAB stats?


KenDude
02-06-2008, 02:27 AM
Is there a way to clear GAB stats prior to a certain date/time. For example, clearing all stats from 12/31/07 and prior leaving only stats from 2008? My stats tables have grown huge with tracking impressions and I'd like to empty them out some please!

Morgan
02-07-2008, 06:22 PM
1199059200 is the GMT Unix timestamp for 12/31/07 00:00:00 so you could run a query:

# add your vB table prefix to the table name if you use one
DELETE FROM GAB_stats WHERE dateline < 1199059200;

But before doing so, how many rows does the GAB_stats table contain right after you run Compress Stats Table via the ACP?

KenDude
02-07-2008, 08:20 PM
1199059200 is the GMT Unix timestamp for 12/31/07 00:00:00 so you could run a query:

# add your vB table prefix to the table name if you use one
DELETE FROM GAB_stats WHERE dateline < 1199059200;

But before doing so, how many rows does the GAB_stats table contain right after you run Compress Stats Table via the ACP?

I ran the compress job, took about 8 minutes, and at the end just said something about squeezed down for maximum peformance and flashed off the screen, I didn't see anything about a row number. BTW, I didn't set up vbulletin initially, how will I know if I have a table prefix name? I haven't ever used the query tool in the admincp either so please bear with me. :)

Morgan
02-08-2008, 06:19 PM
To see the number of rows, you'd need to use phpMyAdmin, select the GAB_stats table, click the Browse tab, and it'll show you the number of rows. If you are using a vB table prefix, it'll be specified in your vB config.php file.

KenDude
02-08-2008, 08:30 PM
Ok this is going to sound like a total newbie question (because I am when it comes to direct MySQL manipulation) but where can I find my vbulletin database? I'm assuming I have to give phpMyAdmin some sort of URL to go look for the database at and then log into the database. I'm assuming my vbulletin config file will have a userid and password for logging into the database too right? Once in phpMyAdmin can I just kill rows of GAB stats manually or is it better to do that SQL query you were talking about?

Morgan
02-09-2008, 03:32 AM
There is a 'databases' link in phpMyAdmin, and yes, your vB config file contains the database name, database username, and database password. Your hosting control panel should have a link to phpMyAdmin, and your login information may or may not be the same as in the vB config file. You can manually kill rows, if you want to do it that way, but before using phpMyAdmin you might want to read some tutorials. You can also run queries via the ACP. See here (http://www.vbulletin.com/docs/html/main/maintenance_query) for how.

KenDude
02-10-2008, 04:24 AM
There is a 'databases' link in phpMyAdmin, and yes, your vB config file contains the database name, database username, and database password. Your hosting control panel should have a link to phpMyAdmin, and your login information may or may not be the same as in the vB config file. You can manually kill rows, if you want to do it that way, but before using phpMyAdmin you might want to read some tutorials. You can also run queries via the ACP. See here (http://www.vbulletin.com/docs/html/main/maintenance_query) for how.

My host set up the server for me, it is a dedicated server, but I don't have any hosting panel that I log into. They did give me terminal access and I used something like "xterm" I think it was for a little while but found that there wasn't much I could do with it anyway so I think my shareware version expired and I never used it again. I will probably have to do this via ACP so if you can help me with the syntax that would be great. I looked in my config.php file and there is no table prefix and I see the dbname as well as the username and password fr the database. Speaking of tables I was able to run a diagnostic in ACP that showed that my GAB_Stats table had over 1 million rows in fact here is a cut/paste from ACP:

GAB_stats MyISAM 9 Dynamic 1067973 38 41184232 4294967295 8770560 0 2008-02-09 20:22:46 2008-02-09 21:18:40 2008-02-09 20:22:56 latin1_swedish_ci


GAB_temp_stats MyISAM 9 Dynamic 1065219 42 45686420 4294967295 1024 0 2008-02-09 20:20:22 2008-02-09 20:22:46 latin1_swedish_ci

KenDude
02-10-2008, 04:51 AM
Hey I did the ACP command deleting all rows from GAB_stats (should I do GAB_temp_stats as well?) and it deleted 933,079 rows in about 30 seconds.

I go back into the GAB admin tools and I can stillsee all the stats since the beginning of time is that because I haven't done anything to GAB_temp_stats yet?

Morgan
02-11-2008, 04:23 PM
The GAB_ads table has two fields, views and hits, that hold the number of impressions and the number of clickthroughs, respectively.

KenDude
02-11-2008, 05:10 PM
Ok so then I can clean out GAB_temp_stats then as well too right and still retain the views and hits because those are in the Gab_ads table too?

Morgan
02-11-2008, 05:50 PM
Correct, emptying the GAB_temp_stats table does not affect the GAB_ads table. The 'views' and 'hits' fields in the GAB_ads table get updated when the vB cron job runs.

KenDude
02-16-2008, 08:04 PM
Morgan,
The reason I thought I needed to clean this out was because from admincp -> database backup -> backup to file on server

When I run that it starts off by doing
GAB_ads
GAB_cats

Then nothing. I can let it sit for a long long time and nothing... I figured it was because my table had grown to a really large size after a year plus, but deleting the old info out of the table as you suggested got the table size down to about 4-5mb, nothing huge, but yet the database backup still bombs out. I've tried using the database fix table tool several times just to make sure the tables are ok and they all are as far as I can tell.

6 months or more ago this used to work because I would do it once a week or so, then at some point months ago it quit working and I always assumed it was because that GAB_Stats table which is the next on the list had grown too large to backup in that method or something.

Fortunately my ISP does nightly backups and I could ask them to recover one of those, but I sort of liked doing my own backups through the admincp tool.

Any suggestions?

KenDude
02-21-2008, 05:35 PM
Morgan,
You still around? Everything OK?

Morgan
02-21-2008, 10:11 PM
Thanks for asking, yes I am doing better. Sorry but I had to be AFK for a bit. Understandably it upset a few people, and I'm not going to say anymore on it other than sometimes life is very difficult. As to your question, are you trying to make a backup of the entire database? If so, doing backups via the ACP is not really the way to go, especially for large forums. Please http://www.vbulletin.com/docs/html/manual_database_backup for backup options and http://www.vbulletin.com/forum/showpost.php?p=1154478&postcount=3 for a bit more information on why doing backups via the ACP may not work.