Oct 232021
 

Messages such as:

Internal error: I was expecting a short description and found nothing for databases/postgresql

indicate a cache entry which predates a change to the cache format. I could clear out the entire cache, but I want to try a targeted approach. In issue 339, some detail is provided. The common thread seems to be a deleted port.

My theory: at one time these database entries contains proper data. A script which updated all ports from the repo failed to detect a deleted port and information already in the database was lost during the update.

There is a table for recording items to be cleared from cache:

freshports.devgit=# \d cache_clearing_ports
                                        Table "public.cache_clearing_ports"
   Column   |            Type             | Collation | Nullable |                     Default                      
------------+-----------------------------+-----------+----------+--------------------------------------------------
 id         | integer                     |           | not null | nextval('cache_clearing_ports_id_seq'::regclass)
 port_id    | integer                     |           | not null | 
 category   | text                        |           | not null | 
 port       | text                        |           | not null | 
 date_added | timestamp without time zone |           | not null | now()
Indexes:
    "cache_clearing_ports_pkey" PRIMARY KEY, btree (id)
    "cache_clearing_ports_port_id_idx" UNIQUE CONSTRAINT, btree (port_id)
Foreign-key constraints:
    "cache_clearing_ports_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE

This populates the table with all deleted ports:

freshports.devgit=# begin;
BEGIN
freshports.devgit=# insert into cache_clearing_ports (port_id, category, port)  
select id, (select name from categories where id = category_id), (select name from element where id = element_id) from ports where status = 'D';
INSERT 0 20715
freshports.devgit=# select count(*) from cache_clearing_ports;
 count 
-------
 20715
(1 row)

freshports.devgit=# commit;
COMMIT

This command notifies the application which is listening on the front end:

freshports.devgit=# NOTIFY port_updated;
NOTIFY

In the logs of that application, I see:

Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: Just woke up! *************
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: Got NOTIFY: pid='33939', channel='port_updated', payload=''
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: found key port_updated
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: invoking RemoveCacheEntry()
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: checking for cache entries to remove...
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: COUNT: 20715 entries to process
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: removing glob /var/db/freshports/cache/ports/databases/phpmyadmin-devel/*
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: removing glob /var/db/freshports/cache/categories/databases/*
Oct 23 12:16:33 dev-nginx01 fp-listen[69025]: DELETE FROM cache_clearing_ports WHERE id = 32289779

It eventually ends around:

Oct 23 12:31:48 dev-nginx01 fp-listen[69025]: DELETE FROM cache_clearing_ports WHERE id = 32310493
Oct 23 12:31:48 dev-nginx01 fp-listen[69025]: finished
Oct 23 12:31:48 dev-nginx01 fp-listen[69025]: invoking ClearMiscCaches()
Oct 23 12:31:48 dev-nginx01 fp-listen[69025]: invoked: ClearMiscCaches()
Oct 23 12:31:48 dev-nginx01 fp-listen[69025]: ClearMiscCaches() is clearing out entries in /var/db/freshports/cache/news/
Oct 23 12:31:48 dev-nginx01 fp-listen[69025]: ClearMiscCaches() is removing /var/db/freshports/cache/news/index-html-head-pagesize100-pagenum1-html
Oct 23 12:31:48 dev-nginx01 fp-listen[69025]: finished: ClearMiscCaches()

That is about 15 minutes to clear out 20715 entries, roughly 1400 a minute or about 22 per second.

It is also doing one transactions per cache-clearing.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive