My caches and sessions tables are always too big

mcemrn

Joined: 2013-02-17
Posts: 20
Posted: Fri, 2013-08-09 17:04

Hi everybody, i am an happy user of your fantastic software Gallery version 3.
I have a problem, the problem is that the tables "caches" and "sessions" in my mysql database visible using phpmyadmin software are always too big.I know that that appens because of huge traffic of search engines spiders, so i found http://galleryproject.org/node/104870 this topic and
1)I installed the module "session explorer",
2)I identified the most active spiders,
3)I added them as spiders on the advice of user bharat in file user_agents.php,
4)I truncated through PhpMyAdmin the tables "caches" and "sessions".

But after one or two days these two tables are always again very big....

Is there a method to deactivate totally cache and session store ?

Sorry for my english and i hope that you help me, thanks and love your work and software !

 
mcemrn

Joined: 2013-02-17
Posts: 20
Posted: Sat, 2013-08-24 14:43

Sorry for the double post... no one can help me ? Is not possible to "totally deactivate" the session table storing data ?

 
floridave
floridave's picture

Joined: 2003-12-22
Posts: 27300
Posted: Mon, 2013-08-26 03:25
 
mcemrn

Joined: 2013-02-17
Posts: 20
Posted: Thu, 2013-08-29 08:57

Thanks for the help floridave but that module doesn't solve my problem... because every two days my cache and session tables are again very big...

And i have to open my phpmyadmin control panel and truncate these two tables again and again...(the main problem is the sessions table)

I am not very expert in php and mysql languages but i heard somewhere that is possible to create a cron job in phpmyadmin control panel, if that is possible can i create a cron job that, for example, truncate cache and session tables avery 24 hours ?
Is possible ?

 
jnash
jnash's picture

Joined: 2004-08-02
Posts: 814
Posted: Thu, 2013-08-29 13:06

How 'big' is 'big' ?

I have mysql databases in the terabyte ranges with no issues...

Is there a problem being caused by the size?

 
tempg

Joined: 2005-12-17
Posts: 1857
Posted: Thu, 2013-08-29 19:56
mcemrn wrote:
Is possible ?

Short answer: Yes, it's possible; would take me a while to figure it out though.

And this may not be your biggest issue; if crawlers are beating down your website (and causing this issue in the first place), you'd do better (imho) to deal with the crawlers. Make sure that you've "disallowed" them in the robots file; you can also bar them with htaccess (user agents and/or ip addresses), but should probably handle it at the server level if they disobey the robots file.

 
mcemrn

Joined: 2013-02-17
Posts: 20
Posted: Thu, 2013-08-29 20:45

Ok, the problem is that my hosting company make me pay an annual amount every 100Mb of mysql database space...so for me is important to use less space as possible... i have also installed a forum, a CMS end others that eat mysql space...

My Gallery 3.0.9 installation have about 4.000 images and occupy about 7Mb of mysql space, plus the cache table about 5~10Mb and the sessions table 22~25Mb now...

I used module "sessions explorer"http://codex.galleryproject.org/Gallery3:Modules:session_explorer to identify about 3 spiders and i added them as spiders on the advice of user bharat in file user_agents.php, but i can't identify the others because i can't identify the "names" and i don't know how to add them to the list

 
inposure
inposure's picture

Joined: 2010-04-23
Posts: 304
Posted: Sun, 2013-10-13 10:39

.

 
tempg

Joined: 2005-12-17
Posts: 1857
Posted: Sat, 2013-08-31 16:09

@mcemrn:

1st. I think there are better ways to handle this issue.

2nd. This would require testing that I can't do right now, but a quick search online gives tons of info. Just back up everything before you try anything. (If you screw up your db and don't have a backup, you'll have to rebuild your Gallery over again. Files without corresponding db aren't useful, except for re-importing with serveradd.) Sample results:
a. http://forums.cpanel.net/f189/cron-run-every-day-optimize-mysql-290371-print.html
b. http://www.webhostingtalk.com/showthread.php?t=866018
c. http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
You'll obviously have to fine tune to do exactly what you want done. (And if you don't know how to establish a crontab, you'll need to look that up too.)

mcemrn wrote:
i can't identify the others because i can't identify the "names" and i don't know how to add them to the list

If you send me a few lines from your access file (of the spiders you want to ban) I'll help you with that--but it may be a few days; lots going on right now.

 
mcemrn

Joined: 2013-02-17
Posts: 20
Posted: Sun, 2013-10-06 09:05

OK thanks all for the help, i notice that the main problem is not the cache table but is the sessions table that is even bigger, i have truncated today and was 34Mb... in meantime that i solve the problem i truncate this table periodically...

I don't want to block spiders, they will index my content, i only want to identify them and add to the list so they won't save sessions table data anymore...

 
tempg

Joined: 2005-12-17
Posts: 1857
Posted: Wed, 2013-10-09 16:52

@mcemrn: Not all spiders/crawlers are helping generate traffic; there are TONS that just scrape data, use bandwidth, etc.

Other than that, thanks for pointing to the sessions table as the source.

This was reported as a previous issue, but corrected: http://galleryproject.org/node/98426
Maybe there's still room to look back at that? (I haven't really looked at that table much; maybe I should do that.)

Same holds true (as in my last post in this thread) about IDing the other robots/scrapers.

 
ofrpic
ofrpic's picture

Joined: 2014-01-25
Posts: 4
Posted: Sat, 2014-02-01 02:52

The best solution is - create a php code to truncate both caches and sessions tables and execute that code as cron job once a day. I implemented for my website and it's working superb.

Praveen K
--------------------------------
Cool Photo Gallery || Radio Directory to listen non-stop music

 
slart

Joined: 2013-11-11
Posts: 112
Posted: Tue, 2014-02-25 13:44

ofrpic, what is the exactly name from the database tables, what can i clean?
"caches"
"sessions"
only this both, or more?

my "caches" tables has current ~50 rows. The module don't work. Browser is broken. No Info Pop-up.
my "sessions" tables has 989 rows.

 
mcemrn

Joined: 2013-02-17
Posts: 20
Posted: Sun, 2014-03-02 15:52

Wow my session table was today 130Mb+ (caches was 7,8Mb)and i truncated it again...
Please, is there a method so that the session table will always remain 0Mb and doesn't save in it any data ?

Thanks