[SOLVED] Huge cache size

klub

Joined: 2010-07-15
Posts: 8

Posted: Wed, 2010-09-15 14:17

Hi,

I am running Gallery3 on a shared hosting service. My database is highly limited: 50 MB.

I discovered today that I am using ~ 50% of my database: I don't feel safe, other websites depends on the same database. After a quich research, it appears that the gallery_caches table size is ~ 20 MB.

Is it normal? I did not find any way to control the cache size from the admin console. Maybe from a php config file?

inposure

Joined: 2010-04-23
Posts: 304

Posted: Wed, 2010-09-15 15:31

Small! My caches table is 56.8 MiB with 643 records. That's 89% of total DB size…

I have also thought about how to keep it (terribly much) smaller, like 1 MB or so.

--
http://inposure.se/

nivekiam

Joined: 2002-12-10
Posts: 16503

Posted: Wed, 2010-09-15 17:27

That's an issue that should be fixed:
http://sourceforge.net/apps/trac/gallery/ticket/1027

First make sure you are using the latest experimental code, not RC (there's a link on the front page of this site to that) and then run the upgrader. Then you can safely truncate (empty) that table, then watch to see if that grows still.
____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here

inposure

Joined: 2010-04-23
Posts: 304

Posted: Wed, 2010-09-15 19:32

Thing is, you shouldn't have to do anything "manually" to keep the database small. Gallery should do this on its own.

As it is, Gallery doesn't even empty deleted comments from the database, you still have to do that manually with phpmyadmin, no matter how many days (moths) have passed since deletion. But I guess you guys all just have small test installations that never receive any spam comments and that work fine with 4 images.

--
http://inposure.se/

floridave

Joined: 2003-12-22
Posts: 25965

Posted: Thu, 2010-09-16 03:17

inposure,
Please file feature requests for these two issues and we will address them.

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team

klub

Joined: 2010-07-15
Posts: 8

Posted: Thu, 2010-09-16 07:43

I should have looked more closely the tickets. Good thing if it is fixed in the latest release!

Thanks

bharat

Joined: 2002-05-21
Posts: 7985

Posted: Fri, 2010-09-17 02:24

Can you guys tell me what's in your cache table? That'll help. Run this SQL:

mysql> SELECT DISTINCT(tags), COUNT(*) FROM caches GROUP BY tags;
+---------------------+----------+
| tags                | count(*) |
+---------------------+----------+
| <css>               |        5 |
| <css>,<gzip>        |        5 |
| <javascript>        |        5 |
| <javascript>,<gzip> |        5 |
| <translation>       |        1 |
+---------------------+----------+

You will have to add your table prefix to the table name above. And I included my own sample output so that you can see what to expect.

Right now we expire old spam when you go to Admin > Contents > Comments, so if you don't go there very often then yeah we don't expire stuff.

---
Problems? Check gallery3/var/logs
bugs/feature req's | upgrade to the latest code | use git

klub

Joined: 2010-07-15
Posts: 8

Posted: Fri, 2010-09-17 07:27

I truncated the table yesterday...

inposure

Joined: 2010-04-23
Posts: 304

Posted: Fri, 2010-09-17 14:32

"Right now we expire old spam when you go to Admin > Contents > Comments, so if you don't go there very often then yeah we don't expire stuff."

No, you don't. It remains in the deletion queue FOREVER.

This is way too WIN-DOS for my taste anyway. If I press DELETE it should be GONE as in GONE FOREVER immediately, not relying on some stupid 7 day waiting period. I really do not appreciate seeing those Viagra ads in the admin panel, but perhaps you do. Otherwise you would see it my way and do something about this obvious-bug-that-is-so-obvious-it-doesnt-need-a-bug-report.

Don't get me wrong, Gallery is excellent software, but it is in a few of these small details that you wonder WTF is going on…

--
http://inposure.se/

tkott

Joined: 2010-06-07
Posts: 225

Posted: Fri, 2010-09-17 15:11

or, it's really a bug that nobody had cared to report because they thought it was so obvious.

----
Publish on Gallery 3 (WLPG Plugin) | XMP Module

floridave

Joined: 2003-12-22
Posts: 25965

Posted: Sat, 2010-09-18 05:17

Quote:

No, you don't. It remains in the deletion queue FOREVER.

Please file a bug for this issue.

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team

bharat

Joined: 2002-05-21
Posts: 7985

Posted: Sat, 2010-09-18 21:33

yo @inposure, chill out. Yes, you're right, there's a bug in the expiration code. Thanks for finding it, and I've fixed it. But please keep a constructive outlook here and don't get pissed at us for the decisions we're making. We're balancing the needs for a lot of users other than you and I personally am not in the mood to take a lot of shit about it.

I too get an unbelievable amount of spam in my Gallery. I'm sympathetic. But we can't just immediately remove comments that you delete, else you're going to delete the wrong comment by accident and not get it back. If we were to go that route, we'd need a confirmation dialog on every delete, which is ridiculously slow.

There are no bugs that are so obvious that they shouldn't get reported. And if anybody else is seeing massive cache tables, please run the query I provided above and give me some idea what's in your cache table -- thanks!
---
Problems? Check gallery3/var/logs
bugs/feature req's | upgrade to the latest code | use git

Retroguy

Joined: 2010-12-29
Posts: 27

Posted: Sat, 2011-01-01 10:41

Hi all,

Just wondering the retro fix is for this issue?

G3 just crashed on my dev box, and while in the process of trying to fix it, I attempted to restore my DB from a backup.

I was surprised to discover that my DB is 56.1 MB in size. So big, in fact, that phpmyadmin choked on the upload.

The site has only been in operation for 4 days and there are only 400ish images in it. The biggest table, by far, is the caches table - currently 37MB

I've run the query you suggested upthread and these are my results

  
tags  COUNT( * )  
<css> 235 
<css>,<gzip> 235 
<javascript> 27 
<javascript>,<gzip> 26 
<task>,<log>,<import> 5 
<translation> 1 
<vars> 1

TIA and kind regards,

Life through a rear view mirror

MarkRH

Joined: 2007-05-25
Posts: 214

Posted: Sun, 2011-01-02 12:08

All you really need is the caches table's structure. Might see if you can do the restore without the caches table's contents included.

Thanks,
Mark H.

Using Gallery 3.0+(git) - gallery.markheadrick.com

Retroguy

Joined: 2010-12-29
Posts: 27

Posted: Sun, 2011-01-02 14:43

Thanks Mark,

I did a clean install and used the photos from my var/albums folder to rebuild.

I now ensure I clear the caches table at the end of every session. That table can get very big very quickly when conducting a lot of CSS/JS edits.

regards,

----

http://retroguy.org

mamouneyya

Joined: 2009-11-02
Posts: 337

Posted: Fri, 2011-04-08 22:02

bharat wrote:

I too get an unbelievable amount of spam in my Gallery. I'm sympathetic. But we can't just immediately remove comments that you delete, else you're going to delete the wrong comment by accident and not get it back. If we were to go that route, we'd need a confirmation dialog on every delete, which is ridiculously slow.

I agree with that, but still find an ability to permanently delete a comment is important. So I'm thinking of putting a button to permanently delete the comment after it already gets deleted, something can be used only in the deleted comments section. This way, the one have to delete the comment, go to the deleted comments section and permanently delete it from there. Another idea to reduce the time consume is to add another button along with the normal delete button for this function, maybe this specifically will have a confirmation dialog then (e.g. Delete and Permanently Delete buttons). What do you think?

nivekiam

Joined: 2002-12-10
Posts: 16503

Posted: Fri, 2011-04-08 22:08

What's wrong with the current system of them being removed after 7 days?
____________________________________________
Like Gallery? Like the support? Donate now!

inposure

Joined: 2010-04-23
Posts: 304

Posted: Fri, 2011-04-08 23:25

What's wrong with your stinky garbage remaining in the hallway for seven days? Maybe you will change your mind and want to recover that used tuna can?

I don't know of any software that has such a grace period. For concistency, delete should really mean delete. People have managed to handle how to empty desktop trash cans since the first Mac in 1984, this is nothing very new to users (and people at such a level shouldn't use this software anyway).

The user should be in control, not the system.

--
http://inposure.se/

nivekiam

Joined: 2002-12-10
Posts: 16503

Posted: Fri, 2011-04-08 23:40

Quote:

What's wrong with your stinky garbage remaining in the hallway for seven days?

It doesn't sit in my hallway for 7 days, it sits in my garbage can in my garage for 7 days, then the garbage company comes and collects it.
____________________________________________
Like Gallery? Like the support? Donate now!

floridave

Joined: 2003-12-22
Posts: 25965

Posted: Sat, 2011-04-09 00:38

bharat wrote:

yo @inposure, chill out........We're balancing the needs for a lot of users other than you and I personally am not in the mood to take a lot of shit about it.

Oh and thanks for the reminder...I sending my kids to the ally with my trash.

Dave

_____________________________________________
Blog & G2 || floridave - Gallery Team

nivekiam

Joined: 2002-12-10
Posts: 16503

Posted: Sat, 2011-04-09 00:49

Quote:

I don't know of any software that has such a grace period

Gmail, Windows, Mac, Outlook, Thunderbird, etc

Yes, you can go into their respective "trash bins" and delete the items personally. And yes, we don't have a friendly button to do that in G3, but you can go into the DB to do that or create a button to do that. I don't see what we do in G3 any different than the little known software I've just mentioned, where by default, delete puts stuff into a "trash bin" and then you can either delete it permanently or like in Gmail, just wait 30 days...

If you feel so inclined either code an "empty trash" feature and put it into git so it can possibly be pulled into the code or create a feature request on trac.
http://sourceforge.net/apps/trac/gallery/

____________________________________________
Like Gallery? Like the support? Donate now!

klub Joined: 2010-07-15 Posts: 8	Posted: Wed, 2010-09-15 14:17
	Hi, I am running Gallery3 on a shared hosting service. My database is highly limited: 50 MB. I discovered today that I am using ~ 50% of my database: I don't feel safe, other websites depends on the same database. After a quich research, it appears that the gallery_caches table size is ~ 20 MB. Is it normal? I did not find any way to control the cache size from the admin console. Maybe from a php config file?
	XUser login Username: * Password: * Create new account Request new password
inposure Joined: 2010-04-23 Posts: 304	Posted: Wed, 2010-09-15 15:31
	Small! My caches table is 56.8 MiB with 643 records. That's 89% of total DB size… I have also thought about how to keep it (terribly much) smaller, like 1 MB or so. -- http://inposure.se/

nivekiam Joined: 2002-12-10 Posts: 16503	Posted: Wed, 2010-09-15 17:27
	That's an issue that should be fixed: http://sourceforge.net/apps/trac/gallery/ticket/1027 First make sure you are using the latest experimental code, not RC (there's a link on the front page of this site to that) and then run the upgrader. Then you can safely truncate (empty) that table, then watch to see if that grows still. ____________________________________________ Like Gallery? Like the support? Donate now!!! See G2 live here

inposure Joined: 2010-04-23 Posts: 304	Posted: Wed, 2010-09-15 19:32
	Thing is, you shouldn't have to do anything "manually" to keep the database small. Gallery should do this on its own. As it is, Gallery doesn't even empty deleted comments from the database, you still have to do that manually with phpmyadmin, no matter how many days (moths) have passed since deletion. But I guess you guys all just have small test installations that never receive any spam comments and that work fine with 4 images. -- http://inposure.se/

floridave Joined: 2003-12-22 Posts: 25965	Posted: Thu, 2010-09-16 03:17
	inposure, Please file feature requests for these two issues and we will address them. Dave _____________________________________________ Blog & G2 \|\| floridave - Gallery Team

klub Joined: 2010-07-15 Posts: 8	Posted: Thu, 2010-09-16 07:43
	I should have looked more closely the tickets. Good thing if it is fixed in the latest release! Thanks

bharat Joined: 2002-05-21 Posts: 7985	Posted: Fri, 2010-09-17 02:24
	Can you guys tell me what's in your cache table? That'll help. Run this SQL: mysql> SELECT DISTINCT(tags), COUNT() FROM caches GROUP BY tags; +---------------------+----------+ \| tags \| count() \| +---------------------+----------+ \| <css> \| 5 \| \| <css>,<gzip> \| 5 \| \| <javascript> \| 5 \| \| <javascript>,<gzip> \| 5 \| \| <translation> \| 1 \| +---------------------+----------+ You will have to add your table prefix to the table name above. And I included my own sample output so that you can see what to expect. Right now we expire old spam when you go to Admin > Contents > Comments, so if you don't go there very often then yeah we don't expire stuff. --- Problems? Check gallery3/var/logs bugs/feature req's \| upgrade to the latest code \| use git

klub Joined: 2010-07-15 Posts: 8	Posted: Fri, 2010-09-17 07:27
	I truncated the table yesterday...

inposure Joined: 2010-04-23 Posts: 304	Posted: Fri, 2010-09-17 14:32
	"Right now we expire old spam when you go to Admin > Contents > Comments, so if you don't go there very often then yeah we don't expire stuff." No, you don't. It remains in the deletion queue FOREVER. This is way too WIN-DOS for my taste anyway. If I press DELETE it should be GONE as in GONE FOREVER immediately, not relying on some stupid 7 day waiting period. I really do not appreciate seeing those Viagra ads in the admin panel, but perhaps you do. Otherwise you would see it my way and do something about this obvious-bug-that-is-so-obvious-it-doesnt-need-a-bug-report. Don't get me wrong, Gallery is excellent software, but it is in a few of these small details that you wonder WTF is going on… -- http://inposure.se/

tkott Joined: 2010-06-07 Posts: 225	Posted: Fri, 2010-09-17 15:11
	or, it's really a bug that nobody had cared to report because they thought it was so obvious. ---- Publish on Gallery 3 (WLPG Plugin) \| XMP Module

floridave Joined: 2003-12-22 Posts: 25965	Posted: Sat, 2010-09-18 05:17
	Quote: No, you don't. It remains in the deletion queue FOREVER. Please file a bug for this issue. Dave _____________________________________________ Blog & G2 \|\| floridave - Gallery Team

bharat Joined: 2002-05-21 Posts: 7985	Posted: Sat, 2010-09-18 21:33
	yo @inposure, chill out. Yes, you're right, there's a bug in the expiration code. Thanks for finding it, and I've fixed it. But please keep a constructive outlook here and don't get pissed at us for the decisions we're making. We're balancing the needs for a lot of users other than you and I personally am not in the mood to take a lot of shit about it. I too get an unbelievable amount of spam in my Gallery. I'm sympathetic. But we can't just immediately remove comments that you delete, else you're going to delete the wrong comment by accident and not get it back. If we were to go that route, we'd need a confirmation dialog on every delete, which is ridiculously slow. There are no bugs that are so obvious that they shouldn't get reported. And if anybody else is seeing massive cache tables, please run the query I provided above and give me some idea what's in your cache table -- thanks! --- Problems? Check gallery3/var/logs bugs/feature req's \| upgrade to the latest code \| use git

Retroguy Joined: 2010-12-29 Posts: 27	Posted: Sat, 2011-01-01 10:41
	Hi all, Just wondering the retro fix is for this issue? G3 just crashed on my dev box, and while in the process of trying to fix it, I attempted to restore my DB from a backup. I was surprised to discover that my DB is 56.1 MB in size. So big, in fact, that phpmyadmin choked on the upload. The site has only been in operation for 4 days and there are only 400ish images in it. The biggest table, by far, is the caches table - currently 37MB I've run the query you suggested upthread and these are my results tags COUNT( * ) <css> 235 <css>,<gzip> 235 <javascript> 27 <javascript>,<gzip> 26 <task>,<log>,<import> 5 <translation> 1 <vars> 1 TIA and kind regards, Life through a rear view mirror

MarkRH Joined: 2007-05-25 Posts: 214	Posted: Sun, 2011-01-02 12:08
	All you really need is the caches table's structure. Might see if you can do the restore without the caches table's contents included. Thanks, Mark H. Using Gallery 3.0+(git) - gallery.markheadrick.com

Retroguy Joined: 2010-12-29 Posts: 27	Posted: Sun, 2011-01-02 14:43
	Thanks Mark, I did a clean install and used the photos from my var/albums folder to rebuild. I now ensure I clear the caches table at the end of every session. That table can get very big very quickly when conducting a lot of CSS/JS edits. regards, ---- http://retroguy.org

mamouneyya Joined: 2009-11-02 Posts: 337	Posted: Fri, 2011-04-08 22:02
	bharat wrote: I too get an unbelievable amount of spam in my Gallery. I'm sympathetic. But we can't just immediately remove comments that you delete, else you're going to delete the wrong comment by accident and not get it back. If we were to go that route, we'd need a confirmation dialog on every delete, which is ridiculously slow. I agree with that, but still find an ability to permanently delete a comment is important. So I'm thinking of putting a button to permanently delete the comment after it already gets deleted, something can be used only in the deleted comments section. This way, the one have to delete the comment, go to the deleted comments section and permanently delete it from there. Another idea to reduce the time consume is to add another button along with the normal delete button for this function, maybe this specifically will have a confirmation dialog then (e.g. Delete and Permanently Delete buttons). What do you think?

nivekiam Joined: 2002-12-10 Posts: 16503	Posted: Fri, 2011-04-08 22:08
	What's wrong with the current system of them being removed after 7 days? ____________________________________________ Like Gallery? Like the support? Donate now!

inposure Joined: 2010-04-23 Posts: 304	Posted: Fri, 2011-04-08 23:25
	What's wrong with your stinky garbage remaining in the hallway for seven days? Maybe you will change your mind and want to recover that used tuna can? I don't know of any software that has such a grace period. For concistency, delete should really mean delete. People have managed to handle how to empty desktop trash cans since the first Mac in 1984, this is nothing very new to users (and people at such a level shouldn't use this software anyway). The user should be in control, not the system. -- http://inposure.se/

nivekiam Joined: 2002-12-10 Posts: 16503	Posted: Fri, 2011-04-08 23:40
	Quote: What's wrong with your stinky garbage remaining in the hallway for seven days? It doesn't sit in my hallway for 7 days, it sits in my garbage can in my garage for 7 days, then the garbage company comes and collects it. ____________________________________________ Like Gallery? Like the support? Donate now!

floridave Joined: 2003-12-22 Posts: 25965	Posted: Sat, 2011-04-09 00:38
	bharat wrote: yo @inposure, chill out........We're balancing the needs for a lot of users other than you and I personally am not in the mood to take a lot of shit about it. Oh and thanks for the reminder...I sending my kids to the ally with my trash. Dave _____________________________________________ Blog & G2 \|\| floridave - Gallery Team

nivekiam Joined: 2002-12-10 Posts: 16503	Posted: Sat, 2011-04-09 00:49
	Quote: I don't know of any software that has such a grace period Gmail, Windows, Mac, Outlook, Thunderbird, etc Yes, you can go into their respective "trash bins" and delete the items personally. And yes, we don't have a friendly button to do that in G3, but you can go into the DB to do that or create a button to do that. I don't see what we do in G3 any different than the little known software I've just mentioned, where by default, delete puts stuff into a "trash bin" and then you can either delete it permanently or like in Gmail, just wait 30 days... If you feel so inclined either code an "empty trash" feature and put it into git so it can possibly be pulled into the code or create a feature request on trac. http://sourceforge.net/apps/trac/gallery/ ____________________________________________ Like Gallery? Like the support? Donate now!

[SOLVED] Huge cache size

XUser login