Horton

Thread

  1. Re: Naming-scheme for db-files

    Markus Wollny <markus.wollny@computec.de> — 2002-08-28T15:04:37Z

    Hi!

Yes, I run vacuum every night - and log-output indicates no errors at
all. Yet I am indeed quite puzzled about the size of this table. Is
there some way of finding out which column consumes so much space? 

select count(*) from ct_com_user return 95858 rows.
The CREATE-statement for this table is as follows:

CREATE TABLE ct_com_user (
  user_id numeric(10, 0), 
  login varchar(1000), 
  password varchar(1000), 
  status numeric(10, 0), 
  rights varchar(20) DEFAULT 'r', 
  firstname varchar(1000), 
  firstname_visible numeric(1, 0) DEFAULT 0, 
  lastname varchar(1000), 
  lastname_visible numeric(1, 0) DEFAULT 0, 
  clan varchar(1000), 
  clan_visible numeric(1, 0) DEFAULT 0, 
  street varchar(1000), 
  street_visible numeric(1, 0) DEFAULT 0, 
  zipcode varchar(1000), 
  zipcode_visible numeric(1, 0) DEFAULT 0, 
  city varchar(1000), 
  city_visible numeric(1, 0) DEFAULT 0, 
  country varchar(1000), 
  country_visible numeric(1, 0) DEFAULT 0, 
  phone1 varchar(1000), 
  phone1_visible numeric(1, 0) DEFAULT 0, 
  phone2 varchar(1000), 
  phone2_visible numeric(1, 0) DEFAULT 0, 
  mobile varchar(1000), 
  mobile_visible numeric(1, 0) DEFAULT 0, 
  fax varchar(1000), 
  fax_visible numeric(1, 0) DEFAULT 0, 
  email1 varchar(1000), 
  email1_visible numeric(1, 0) DEFAULT 0, 
  email2 varchar(1000), 
  email2_visible numeric(1, 0) DEFAULT 0, 
  icq varchar(1000), 
  icq_visible numeric(1, 0) DEFAULT 0, 
  homepage varchar(1000), 
  homepage_visible numeric(1, 0) DEFAULT 0, 
  description varchar(1000), 
  description_visible numeric(1, 0) DEFAULT 0, 
  hobbies varchar(1000), 
  hobbies_visible numeric(1, 0) DEFAULT 0, 
  signature1 varchar(4000), 
  signature2 varchar(4000), 
  signature3 varchar(4000), 
  forum_view varchar(20) DEFAULT 'flat', 
  temp_password varchar(100), 
  registered timestamptz, 
  last_login timestamptz, 
  created timestamptz DEFAULT ('now'::text)::timestamp(6) with time
zone, 
  lines numeric(3, 0) DEFAULT 400, 
  lines_forum numeric(3, 0) DEFAULT 20, 
  forum_lines numeric(3, 0) DEFAULT 20, 
  forum_smileys varchar(50) DEFAULT 'enhanced', 
  site_id numeric(10, 0) DEFAULT 0, 
  flag_id numeric(10, 0) DEFAULT 0, 
  forum_quoting varchar(50) DEFAULT 'enhanced', 
  forum_flatpostings numeric(3, 0) DEFAULT 10, 
  forum_images numeric(1, 0) DEFAULT 2, 
  user_image numeric(1, 0) DEFAULT 0, 
  user_image_visible numeric(1, 0) DEFAULT 0, 
  chat_password varchar(50), 
  chat_last_visit timestamptz, 
  authorpoints_visible numeric(1, 0) DEFAULT 0,
  emailindex txtidx,
  CONSTRAINT idx_pk_ct_com_user UNIQUE (user_id), 
  CONSTRAINT idx_u_ct_com_user_login UNIQUE (login)
) WITH OIDS;

As hardly anyone actually uses a signature, hobby or description, I
cannot imagine how this should amount to almost 2 GB of data (1862MB) -
this would mean roughly 20k of data per row average - and no way have we
got this amount of data - the absolute maximum data per row as defined
would be about 30k, I guess, but that would mean that nearly all of our
users would use nearly all available space and I know that this is quite
far from true. I suspect it may have something to do with emailindex of
the txtidx-type... As it's not absolutely necessary, I'll try and drop
this column and see what I get...

Regards,

	Markus

> -----Ursprüngliche Nachricht-----
> Von: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Gesendet: Mittwoch, 28. August 2002 16:30
> An: Markus Wollny
> Cc: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Naming-scheme for db-files
> 
> 
> On Wed, Aug 28, 2002 at 03:54:45PM +0200, Markus Wollny wrote:
> > Hi!
> > 
> > As I was just checking disk-usage of a database (PostgreSQL 
> 7.2.1), I
> > stumbled over some files named with a trailing .1 added to the usual
> > oid. Now if a table 'example' with oid 12345 exists, what 
> does the file
> > 12345.1 contain exactly? I didn't find anything about 
> .1-files in the
> > documentation...
> 
> Postgres splits files at 1GB. The .1 file would be the second 
> part of the
> file. When that also reaches 1GB, you'll get a .2 file.
> 
> You're doing VACUUM [FULL] regularly, right?
> 
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   
> http://svana.org/kleptog/
> > There are 10 kinds of people in the world, those that can do binary
> > arithmetic and those that can't.
>

  2. Qos how to improve performace for particular connections

    David Blood <david@matraex.com> — 2002-08-28T15:12:34Z

    Is there a way to give one connection/s priority over others.  Something
like "nice" .  That way less import queries could be made to wait for
the more important ones.



David Blood
Matraex, Inc

  3. Re: Naming-scheme for db-files

    Alvaro Herrera <alvherre@atentus.com> — 2002-08-28T16:08:25Z

    Markus Wollny dijo: 

> Yes, I run vacuum every night - and log-output indicates no errors at
> all. Yet I am indeed quite puzzled about the size of this table. Is
> there some way of finding out which column consumes so much space? 

[lots of NUMERIC in table description]

NUMERIC is not the best thing to use for the most of these cases.  Use
INTEGER where you want a number (user_id, etc), or better yet SERIAL
(see the docs for implications).

Where you want boolean values, use BOOL.  Where you want multiple
choice, use "char" (with the ""); that gives you a 1-char space.

This way the table will be probably not only way smaller, but faster as
well.

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"El que vive para el futuro es un iluso, y el que vive para el pasado,
un imbécil" (Luis Adler, "Los tripulantes de la noche")