openerp-india team mailing list archive

["Guewen Baconnier @ Camptocamp" <1274093@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

Hi,

I was trying to figure out why a database was so weighty on the disk: 14GB
Do the test yourself:

postgres=# SELECT pg_size_pretty(pg_database_size('openerp_foobar'));
-[ RECORD 1 ]--+------
pg_size_pretty | 14 GB

This query gives the larger tables (on disk) of the database:
  openerp_foobar=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 4;
-[ RECORD 1 ]---------------------------------------------
relation | pg_toast.pg_toast_46606318
size     | 10120 MB
-[ RECORD 2 ]---------------------------------------------
relation | public.account_move_line
size     | 224 MB
-[ RECORD 3 ]---------------------------------------------
relation | public.stock_move
size     | 167 MB
-[ RECORD 4 ]---------------------------------------------
relation | public.sale_order_line
size     | 133 MB

Note: pg_toast.pg_toast_46606318 holds large objects of res_partner

It appeared that the res_partner table was mostly (and by far) responsible for that:
openerp_foobar=# SELECT pg_size_pretty(pg_total_relation_size('res_partner'));
-[ RECORD 1 ]--+------
pg_size_pretty | 10 GB

Although it only contains 300.616 records.

The reason: for each new partner created, OpenERP will create an image
stored on the new record. 3 times (with different sizes).

The images are always the same (there is  a few different default
images, one for the companies, one for the contacts, maybe others for
users and employees), except when we have modified them (never happen).

openerp_foobar=# select count(*) from res_partner group by image;
-[ RECORD 1 ]-
count | 185428
-[ RECORD 2 ]-
count | 38
-[ RECORD 3 ]-
count | 36
-[ RECORD 4 ]-
count | 1
-[ RECORD 5 ]-
count | 1
-[ RECORD 6 ]-
count | 115112

It is very surprising to see these default images copied again and again.
A better solution would be to only store the default images in one place and fallback on them when the image fields are empty.

This is really a problem, limiting the scalability of the system and is
painful for the backups.

** Affects: openobject-addons
     Importance: Undecided
     Assignee: OpenERP Publisher's Warranty Team (openerp-opw)
         Status: New


** Tags: maintenance

-- 
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Addons.
https://bugs.launchpad.net/bugs/1274093

Title:
  [7.0] Images on res.partner taking a lot of space uselessly

Status in OpenERP Addons (modules):
  New

Bug description:
  Hi,

  I was trying to figure out why a database was so weighty on the disk: 14GB
  Do the test yourself:

  postgres=# SELECT pg_size_pretty(pg_database_size('openerp_foobar'));
  -[ RECORD 1 ]--+------
  pg_size_pretty | 14 GB

  This query gives the larger tables (on disk) of the database:
    openerp_foobar=# SELECT nspname || '.' || relname AS "relation",
      pg_size_pretty(pg_relation_size(C.oid)) AS "size"
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY pg_relation_size(C.oid) DESC
    LIMIT 4;
  -[ RECORD 1 ]---------------------------------------------
  relation | pg_toast.pg_toast_46606318
  size     | 10120 MB
  -[ RECORD 2 ]---------------------------------------------
  relation | public.account_move_line
  size     | 224 MB
  -[ RECORD 3 ]---------------------------------------------
  relation | public.stock_move
  size     | 167 MB
  -[ RECORD 4 ]---------------------------------------------
  relation | public.sale_order_line
  size     | 133 MB

  Note: pg_toast.pg_toast_46606318 holds large objects of res_partner

  It appeared that the res_partner table was mostly (and by far) responsible for that:
  openerp_foobar=# SELECT pg_size_pretty(pg_total_relation_size('res_partner'));
  -[ RECORD 1 ]--+------
  pg_size_pretty | 10 GB

  Although it only contains 300.616 records.

  The reason: for each new partner created, OpenERP will create an image
  stored on the new record. 3 times (with different sizes).

  The images are always the same (there is  a few different default
  images, one for the companies, one for the contacts, maybe others for
  users and employees), except when we have modified them (never
  happen).

  openerp_foobar=# select count(*) from res_partner group by image;
  -[ RECORD 1 ]-
  count | 185428
  -[ RECORD 2 ]-
  count | 38
  -[ RECORD 3 ]-
  count | 36
  -[ RECORD 4 ]-
  count | 1
  -[ RECORD 5 ]-
  count | 1
  -[ RECORD 6 ]-
  count | 115112

  It is very surprising to see these default images copied again and again.
  A better solution would be to only store the default images in one place and fallback on them when the image fields are empty.

  This is really a problem, limiting the scalability of the system and
  is painful for the backups.

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-addons/+bug/1274093/+subscriptions