By alraben on

Hi,

I have Drupal 6.1 in my site and I've observed something that I dont know if it's normal or not.

Well... Drupal is running on Apache 2.2, PHP5.2.5 and postgresql 8.1 ok?. I have a line in a crontab to backup the postgresql database like that:

/usr/local/bin/pg_dump -U pgsql -h 127.0.0.1 database | gzip --best > /root/backup_pgsql/bd_backup_${fecha}.gz

It's executed all days at 23:00.

Well, Since 13th of October I observed that the .gz file multiplicates by 4 its size.

-rw-r--r-- 1 root wheel 32M Oct 3 23:01 bd_backup_20081003.gz
-rw-r--r-- 1 root wheel 33M Oct 4 23:01 bd_backup_20081004.gz
-rw-r--r-- 1 root wheel 60M Oct 6 23:02 bd_backup_20081006.gz
-rw-r--r-- 1 root wheel 58M Oct 7 23:02 bd_backup_20081007.gz
-rw-r--r-- 1 root wheel 84M Oct 8 23:03 bd_backup_20081008.gz
-rw-r--r-- 1 root wheel 82M Oct 9 23:03 bd_backup_20081009.gz
-rw-r--r-- 1 root wheel 84M Oct 10 23:03 bd_backup_20081010.gz
-rw-r--r-- 1 root wheel 85M Oct 11 23:02 bd_backup_20081011.gz
-rw-r--r-- 1 root wheel 87M Oct 13 23:03 bd_backup_20081013.gz
-rw-r--r-- 1 root wheel 133M Oct 14 23:04 bd_backup_20081014.gz
-rw-r--r-- 1 root wheel 162M Oct 15 23:05 bd_backup_20081015.gz
-rw-r--r-- 1 root wheel 188M Oct 16 23:05 bd_backup_20081016.gz
-rw-r--r-- 1 root wheel 247M Oct 17 23:09 bd_backup_20081017.gz
-rw-r--r-- 1 root wheel 532M Oct 18 23:18 bd_backup_20081018.gz
...
...
...
-rw-r--r-- 1 root wheel 1.2G Oct 26 23:22 bd_backup_20081026.gz

Another thing that I observed is that a snapshot of the /usr/local/pgsql/data/base directory in 13th of Octuber was:

3,7M ./1
3,6M ./10818
3,7M ./10819
0B ./16385/pgsql_tmp
2,1G ./16385
426M ./93742
2,5G

and Actually the I have the following information:

3.7M ./1
3.6M ./10818
3.7M ./10819
2.0K ./16385/pgsql_tmp
4.1G ./16385
2.0K ./93742/pgsql_tmp
420M ./93742
4.5G .

I think that it's imposible that in 15 days the database increase its size from 2.5G to 4.5G.

is it this behaviour normal?

What is hapenning?

Thanks for all and sorry for my English.

⋅ Categories: Drupal 6.x

Comments

vivek.puri’s picture

vivek.puri commented

It doesn't look normal. Check the size of log and cache tables. You can also exclude log, cache and session tables in your backup.

vp

alraben’s picture

alraben commented

you mean the following tables?

public | sessions | table |
public | cache | table |
public | accesslog | table |

How can I see the size of those tables?

Can I delete its content manually? is it important? are there any manner to make that through Drupal?

Thanks,
Álvaro

alraben’s picture

alraben commented

well I see the following command:

select pg_size_pretty(pg_total_relation_size('sessions'));

pg_size_pretty
----------------
180 MB
(1 row)

select pg_size_pretty(pg_total_relation_size('accesslog'));

pg_size_pretty
----------------
32KB
(1 row)

select pg_size_pretty(pg_total_relation_size('cache'));
pg_size_pretty
----------------
65 MB
(1 row)

select pg_size_pretty(pg_total_relation_size('watchdog'));
pg_size_pretty
----------------
68 MB
(1 row)

I see in the /usr/local/pgsql/data/base/ directory 2 files that its size is:

-rw------- 1 pgsql pgsql 716M Oct 27 21:02 16477.1
-rw------- 1 pgsql pgsql 380M Oct 27 21:02 16483.1

How can I identify the tables of this files?

What can I do?

thanks

vivek.puri’s picture

vivek.puri commented

The size of sessions looks bit large but these table still do not explain increase of nearly 2GB.

you can run this query:

select tablename, pg_size_pretty(pg_total_relation_size(tablename)) from pg_tables where schemaname = 'public';

If you are using schema other than public change the schemaname accordingly. this will give you table size of all your tables.

vp

alraben’s picture

alraben commented

The Size of all my tables are the following:

tablename | pg_size_pretty
-------------------------+----------------
variable | 1128 kB
actions | 32 kB
actions_aid | 8192 bytes
batch | 48 kB
cache | 65 MB
cache_form | 205 MB
cache_page | 24 kB
cache_menu | 3406 MB
files | 2192 kB
flood | 16 kB
history | 4064 kB
menu_router | 10152 kB
menu_links | 16 MB
sessions | 180 MB
system | 14 MB
url_alias | 1536 kB
filters | 56 kB
filter_formats | 48 kB
cache_filter | 57 MB
blocks | 144 kB
blocks_roles | 40 kB
boxes | 64 kB
cache_block | 24 kB
access | 16 kB
authmap | 16 kB
permission | 72 kB
role | 40 kB
users | 4048 kB
users_roles | 40 kB
node | 7096 kB
node_access | 24 kB
node_counter | 8192 bytes
node_revisions | 29 MB
node_type | 32 kB
comments | 40 kB
node_comment_statistics | 184 kB
term_data | 136 kB
term_hierarchy | 56 kB
term_node | 2000 kB
term_relation | 48 kB
term_synonym | 56 kB
vocabulary | 48 kB
vocabulary_node_types | 40 kB
menu_custom | 32 kB
watchdog | 68 MB
cache_update | 944 kB
book | 168 kB
languages | 48 kB
locales_source | 2152 kB
locales_target | 1008 kB
search_dataset | 7944 kB
search_index | 69 MB
search_total | 8728 kB
search_node_links | 184 kB
accesslog | 32 kB
trigger_assignments | 8192 bytes
upload | 1640 kB
eventos | 40 kB
medicamentos_eventos | 232 kB
medicamentos | 360 kB
alertas_farmacologicas | 112 kB
agenda_eventos | 112 kB
categorias | 40 kB

Do u see any thing incorrect?

Why dont I see a table with the sizes that I see when I make a ls -lh in the /usr/local/pgsql/data/base directory?

-rw------- 1 pgsql pgsql 717M Oct 28 08:31 16477.1
-rw------- 1 pgsql pgsql 380M Oct 28 08:31 16483.1

Thanks for all.

vivek.puri’s picture

vivek.puri commented

cache | 65 MB
cache_form | 205 MB
cache_page | 24 kB
cache_menu | 3406 MB

Drupal relies heavily on caching to improve performance. Your cache_menu table is almost 3.5GB which is huge because your content is hardly 8MB. You should take a look at this issue: #231587: cache_menu: data blobs identical and huge; . Your problem is related to that issue. The issue is fixed so you should also upgrade to latest drupal as soon as possible.

Why dont I see a table with the sizes that I see when I make a ls -lh in the /usr/local/pgsql/data/base directory?

Thats because postgresql doesn't work like mysql and you dont have same relation between file and table. That file 16477.1 could possibly be holding content from multiple tables.

vp

alraben’s picture

alraben commented

I see ... my cache_menu table is broken.

hmmm if I would not have possibility to upgrade my Drupal ... What could I do? Apply the patch that appear in that post?

isn't there another manner to clear the table?

Thanks.
Álvaro.

alraben’s picture

alraben commented

How can I desactivate the fact of iincreasing of the cache_menu table? not executing cron.php periodically?

Thanks,
Álvaro

alraben’s picture

alraben commented

Maybe ...

can I use the following?

http://drupal.org/node/321154

<?php
function cache_set($cid, $data, $table = 'cache', $expire = CACHE_PERMANENT, $headers = NULL) {
  // Added these 4 lines ...
  if ($table == 'cache_menu' && $cid == 'router:') {
    return;
  }
  // End of added lines
  $serialized = 0;
?>
vivek.puri’s picture

vivek.puri commented

If you cant update then best option is to install that patch. Just remember that patch will solve the future problem but not the exiting one. This means that patch wont clear your table. For clearing that table you can just delete rows from that cache_menu table.

vp

alraben’s picture

alraben commented

hi,

First of all thanks for ur help and patience and sorry for my english.

Well u meant that I must to apply the patch if I want to solve the problem in the future... But what patch? The patches that appear in the link u give me? (What of them? )Or the code that i found...

And finally to clear the cache_menu table... I could execute the following command:

Delete fom cache_menu no? If I execute this now, what're the consequencies for my site? To understand better the problem...can i put this sentence in a crontab to execute it periodically?

Thanks again for solve all my doubts

vivek.puri’s picture

vivek.puri commented

sorry for my english

your english is fine :)

But what patch?

the patch is in the link I mentioned at #231587: cache_menu: data blobs identical and huge; its in in comment number #26 .

yes command to remove tables from cache_menu is:

delete from cache_menu

You only have to run it once to clear old entries and you dont need it in cron. But run only after you have successfully applied the patch.

vp

alraben’s picture

alraben commented

Well ... I'm a little bit heavy, but I want to understand all all and all. Only write yes or not ok?

1) My problem begins for a bug in Drupal 6.1 (that I have installed). That bug makes that entries in the cache_menu table have a '0' in the expires column so cron jobs that are executed never free those entries and the table only crew crew and crew up ... no? That table is used by Drupal for improving the performance of the site nop? It permit Drupal to show pages faster than if dont use that method no?

2) Well ... with patch #26 I'll solve the problem (http://drupal.org/files/issues/2-level-menu-cache-231587-26.patch). I have Drupal 6.1, It's patch works for this version no? To apply a patch I see the following:

http://drupal.org/patch/apply

So I execute "patch -p0 < path/file.patch" because I think that this patch was made relative to the Drupal root directory

3) Well, When I execute the patch I can flush the cache_menu table, for example making that:

delete from cache_menu where expire='0' That's better than your solution no?

Well, what happen if I could apply the patch? The problem would be there but I could write a line in a crontab that make the "delete from cache_menu" ... to the postgresql database ...no?

Nothing more...

Thanks so much!!!!

vivek.puri’s picture

vivek.puri commented

1) Yes. But issue is more than just expiration related, it duplicates data.
2) Yes
3) No. Just use delete from cache_menu; this table will be rebuilt later so don't worry about deleting all rows in it.

To handle the expiration issue you should apply the patch in comment #2 here: #226728: Temporary cache table entries are not flushed.

Look at this way, there are two problems one is expiration of catch other ir more complex about data duplication which is solved by first patch.

vp

alraben’s picture

alraben commented

Ok...

2 problems - 2 patches to solve them:

1. duplicate data:

patch -p0 < 2-level-menu-cache-231587-26.patch

2. Cache expired 0

patch -p0 < cache_expired.patch

The last question :D ... If I execute the lines that I wrote, It's not necessary to execute delete from cache_menu no? Because with the second patch I've seen that the delete sentence is in a cron job system. Only I need to execute the delete from cache_menu manually, for example, if I didnt apply these patches no?, but I would be conscient that the problem wouldnt be solved.

Tomorrow, I'll apply the patches at work but if I wanted to execute the delete from cache_menu manually now to free disk space, There wasnt any problem no?

Thanks for all,
Álvaro.

vivek.puri’s picture

vivek.puri commented
if I wanted to execute the delete from cache_menu manually now to free disk space, There wasnt any problem no?

it will be fine.

vp