[GeoNetwork-users] Geonetwork database increase suddenly

Nicolas_De_Ville_De · December 1, 2021, 8:39am

Dear,

During the weekend, my geonetwork database (h2) decided on its own to grow from ~200Mb to 1.2Gb. This is seriously slowing down the access to the main page!

I did not add any new material/metadata/images or whatever. I have no clue on what is stored in the database that can take 1Gb of space!

Is there a way to 'clean' the database? Maybe there are stored queries with results or other working information that should be deleted?! I tried to open the H2 database with DBeaver but there are some indexing/PK errors./
/

//

/Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement:
ALTER TABLE PUBLIC.CATEGORIESDES ADD CONSTRAINT PUBLIC.FK_69P88C8991LETUOQ16JDDI307 FOREIGN KEY(IDDES) REFERENCES PUBLIC.CATEGORIES(ID) NOCHECK [23505-197]/

//

Many thanks for your help,

--
Nicolas de Ville
Belgian Marine Data Centre (BMDC)
OD-Nature, Royal Belgian Institute of Natural Sciences
02/627.42.79
www.bmdc.be
https://metadata.naturalsciences.be/

Jose_Garcia1 · December 1, 2021, 1:24pm

Hi Nicolas

For production I would recommend not using H2, but other database systems
like Postgres.

I'm not sure how could happen this size increase, but check if maybe it is
enabled the setting to store the user searches, in GeoNetwork 3 versions
this is stored in the database and if there are many requests, can cause
this kind of problem.

Regards,
Jose García

On Wed, Dec 1, 2021 at 9:41 AM Nicolas De Ville de Goyet via
GeoNetwork-users <geonetwork-users@lists.sourceforge.net> wrote:

Dear,

During the weekend, my geonetwork database (h2) decided on its own to
grow from ~200Mb to 1.2Gb. This is seriously slowing down the access to
the main page!

I did not add any new material/metadata/images or whatever. I have no
clue on what is stored in the database that can take 1Gb of space!

Is there a way to 'clean' the database? Maybe there are stored queries
with results or other working information that should be deleted?! I
tried to open the H2 database with DBeaver but there are some
indexing/PK errors./
/

//

/Unique index or primary key violation: "PRIMARY KEY ON
"""".PAGE_INDEX"; SQL statement:
ALTER TABLE PUBLIC.CATEGORIESDES ADD CONSTRAINT
PUBLIC.FK_69P88C8991LETUOQ16JDDI307 FOREIGN KEY(IDDES) REFERENCES
PUBLIC.CATEGORIES(ID) NOCHECK [23505-197]/

//

Many thanks for your help,

--
Nicolas de Ville
Belgian Marine Data Centre (BMDC)
OD-Nature, Royal Belgian Institute of Natural Sciences
02/627.42.79
www.bmdc.be
https://metadata.naturalsciences.be/

_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at
http://sourceforge.net/projects/geonetwork

--

*Vriendelijke groeten / Kind regards,Jose García
<http://www.geocat.net/>Veenderweg 136721 WD BennekomThe NetherlandsT: +31
(0)318 416664 <+31318416664>Please consider the environment before printing
this email.*

Nicolas_De_Ville_De · December 2, 2021, 8:12am

Helo Jose,

Thank you for your answer.

Indeed I'll switch to Postgres because H2 is really unstable; Maybe it would be useful to add a comment about that in the documentation.

The only settings I found is about search statistics which wasn't enable. I didn't found the user search parameter (if it is different). Do you know if it is possible to delete the stored searches without accessing directly the H2 database?

Best regards,

Nicolas

On 01/12/2021 14:24, Jose Garcia wrote:

Hi Nicolas

For production I would recommend not using H2, but other database systems like Postgres.

I'm not sure how could happen this size increase, but check if maybe it is enabled the setting to store the user searches, in GeoNetwork 3 versions this is stored in the database and if there are many requests, can cause this kind of problem.

Regards,
Jose García

On Wed, Dec 1, 2021 at 9:41 AM Nicolas De Ville de Goyet via GeoNetwork-users <geonetwork-users@lists.sourceforge.net <mailto:geonetwork-users@lists.sourceforge.net>> wrote:

    Dear,

    During the weekend, my geonetwork database (h2) decided on its own to
    grow from ~200Mb to 1.2Gb. This is seriously slowing down the
    access to
    the main page!

    I did not add any new material/metadata/images or whatever. I have no
    clue on what is stored in the database that can take 1Gb of space!

    Is there a way to 'clean' the database? Maybe there are stored
    queries
    with results or other working information that should be deleted?! I
    tried to open the H2 database with DBeaver but there are some
    indexing/PK errors./
    /

    //

    /Unique index or primary key violation: "PRIMARY KEY ON
    """".PAGE_INDEX"; SQL statement:
    ALTER TABLE PUBLIC.CATEGORIESDES ADD CONSTRAINT
    PUBLIC.FK_69P88C8991LETUOQ16JDDI307 FOREIGN KEY(IDDES) REFERENCES
    PUBLIC.CATEGORIES(ID) NOCHECK [23505-197]/

    //

    Many thanks for your help,

    -- Nicolas de Ville
    Belgian Marine Data Centre (BMDC)
    OD-Nature, Royal Belgian Institute of Natural Sciences
    02/627.42.79
    www.bmdc.be
    <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.bmdc.be%2F&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459323269|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=G1V9%2FgqHgkwzYT6YoshaF60yUd2Dr%2FfNytQ6ojN0%2Fzg%3D&reserved=0>
    https://metadata.naturalsciences.be/
    <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmetadata.naturalsciences.be%2F&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459333223|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=qv0BtFB9n2mnWQaWofApM%2BARzyrXLVh5UtygBikcWig%3D&reserved=0>

    _______________________________________________
    GeoNetwork-users mailing list
    GeoNetwork-users@lists.sourceforge.net
    <mailto:GeoNetwork-users@lists.sourceforge.net>
    https://lists.sourceforge.net/lists/listinfo/geonetwork-users
    <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.sourceforge.net%2Flists%2Flistinfo%2Fgeonetwork-users&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459333223|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=q59OPl7FreUtAPNtbN386qFUWXFucoaHRxvm4luYaZk%3D&reserved=0>
    GeoNetwork OpenSource is maintained at
    http://sourceforge.net/projects/geonetwork
    <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fgeonetwork&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459343188|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=JFtpPuK9JRm1dAHjghzd%2Bz%2FFVZKjZHkhg9%2BadF3OXSc%3D&reserved=0>

--
***
Vriendelijke groeten / Kind regards,

Jose García

<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.geocat.net%2F&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459353140|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=tAlTvc%2FR4CpfOI5mr%2FJCvDdKbwihWPY9tRB%2FizfaqVg%3D&reserved=0>
Veenderweg 13
6721 WD Bennekom
The Netherlands
T: +31 (0)318 416664 <tel:+31318416664>

Please consider the environment before printing this email.
***

--
Nicolas de Ville
Belgian Marine Data Centre (BMDC)
OD-Nature, Royal Belgian Institute of Natural Sciences
02/627.42.79
www.bmdc.be
https://metadata.naturalsciences.be/

Jose_Garcia1 · December 2, 2021, 1:13pm

Hi Nicolas

Afaik, there are no API or UI services to clean that information.

Regards,
Jose García

On Thu, Dec 2, 2021 at 9:12 AM Nicolas De Ville de Goyet <
ndeville@anonymised.com> wrote:

Helo Jose,

Thank you for your answer.

Indeed I'll switch to Postgres because H2 is really unstable; Maybe it
would be useful to add a comment about that in the documentation.

The only settings I found is about search statistics which wasn't enable.
I didn't found the user search parameter (if it is different). Do you know
if it is possible to delete the stored searches without accessing directly
the H2 database?

Best regards,

Nicolas
On 01/12/2021 14:24, Jose Garcia wrote:

Hi Nicolas

For production I would recommend not using H2, but other database systems
like Postgres.

I'm not sure how could happen this size increase, but check if maybe it is
enabled the setting to store the user searches, in GeoNetwork 3 versions
this is stored in the database and if there are many requests, can cause
this kind of problem.

Regards,
Jose García

On Wed, Dec 1, 2021 at 9:41 AM Nicolas De Ville de Goyet via
GeoNetwork-users <geonetwork-users@lists.sourceforge.net> wrote:

Dear,

During the weekend, my geonetwork database (h2) decided on its own to
grow from ~200Mb to 1.2Gb. This is seriously slowing down the access to
the main page!

I did not add any new material/metadata/images or whatever. I have no
clue on what is stored in the database that can take 1Gb of space!

Is there a way to 'clean' the database? Maybe there are stored queries
with results or other working information that should be deleted?! I
tried to open the H2 database with DBeaver but there are some
indexing/PK errors./
/

//

/Unique index or primary key violation: "PRIMARY KEY ON
"""".PAGE_INDEX"; SQL statement:
ALTER TABLE PUBLIC.CATEGORIESDES ADD CONSTRAINT
PUBLIC.FK_69P88C8991LETUOQ16JDDI307 FOREIGN KEY(IDDES) REFERENCES
PUBLIC.CATEGORIES(ID) NOCHECK [23505-197]/

//

Many thanks for your help,

--
Nicolas de Ville
Belgian Marine Data Centre (BMDC)
OD-Nature, Royal Belgian Institute of Natural Sciences
02/627.42.79
www.bmdc.be
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.bmdc.be%2F&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459323269|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=G1V9%2FgqHgkwzYT6YoshaF60yUd2Dr%2FfNytQ6ojN0%2Fzg%3D&reserved=0>
https://metadata.naturalsciences.be/
<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmetadata.naturalsciences.be%2F&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459333223|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=qv0BtFB9n2mnWQaWofApM%2BARzyrXLVh5UtygBikcWig%3D&reserved=0>

_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.sourceforge.net%2Flists%2Flistinfo%2Fgeonetwork-users&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459333223|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=q59OPl7FreUtAPNtbN386qFUWXFucoaHRxvm4luYaZk%3D&reserved=0>
GeoNetwork OpenSource is maintained at
http://sourceforge.net/projects/geonetwork
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fgeonetwork&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459343188|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=JFtpPuK9JRm1dAHjghzd%2Bz%2FFVZKjZHkhg9%2BadF3OXSc%3D&reserved=0>

--

* Vriendelijke groeten / Kind regards, Jose García
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.geocat.net%2F&data=04|01|ndeville%40naturalsciences.be|26d8fc5149ce4ab8672d08d9b4cdf51e|4da8d35db472419c9e15665786aadbfb|0|0|637739620459353140|Unknown|TWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D|3000&sdata=tAlTvc%2FR4CpfOI5mr%2FJCvDdKbwihWPY9tRB%2FizfaqVg%3D&reserved=0>
Veenderweg 13 6721 WD Bennekom The Netherlands T: +31 (0)318 416664
<+31318416664> Please consider the environment before printing this email. *

--
Nicolas de Ville
Belgian Marine Data Centre (BMDC)
OD-Nature, Royal Belgian Institute of Natural Sciences
02/627.42.79www.bmdc.behttps://metadata.naturalsciences.be/

--

*Vriendelijke groeten / Kind regards,Jose García
<http://www.geocat.net/>Veenderweg 136721 WD BennekomThe NetherlandsT: +31
(0)318 416664 <+31318416664>Please consider the environment before printing
this email.*