Обсуждение: Handling large volumes of data

Поиск
Список
Период
Сортировка

Handling large volumes of data

От
Johann Spies
Дата:
I want to set up a system where logs of all kinds can be put in tables
so that queries and reports can be generated from there.  Our Firewall
logs alone generate about 600,000,000 lines per month and that will
increase as we get more bandwidth.

I am testing postgresql's ability to handle large datasets.  I have
loaded about 4,900,000,000 in one of two tables with 7200684 in the
second table in database 'firewall', built one index using one
date-field (which took a few days) and used that index to copy about
3,800,000,000 of those records from the first to a third table,
deleted those copied record from the first table and dropped the third
table.

This took about a week on a 2xCPU quadcore server with 8Gb RAM.  During and
after that exercise the server was lethargic and constantly ran at a
load average of at least 5.

So I decided maybe it is a good thing to run 'autovacuum analyse' to
clean up things.  After a few days the process is still running and
the load average still constantly about 5.

I then decided to just drop the tables.  I did that about 18 hours ago
and still there is no sign of Postgresql finishing that (pid 18614 below).

The machine is sluggish.  A 'ps fanx'  shows:

12501 ?        S      0:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c
config_file=/etc/postgresql/8.1/main/postgresql.conf
12504 ?        D      0:54  \_ postgres: writer process
12505 ?        S      1:42  \_ postgres: stats buffer process
12506 ?        S      1:03  |   \_ postgres: stats collector process
15918 ?        D    1214:28  \_ postgres: autovacuum process   firewall
18613 ?        D    130:08  \_ postgres: exilog exilog 146.232.128.197(53907) SELECT
18614 ?        S     12:42  \_ postgres: exilog exilog 146.232.128.197(53908) idle
31932 ?        D     16:11  \_ postgres: exilog exilog 146.232.128.106(36547) INSERT
10380 ?        S      0:00  \_ postgres: log firewall [local] DROP TABLE waiting
20753 ?        D      5:11  \_ postgres: exilog exilog 146.232.128.197(43581) INSERT
16370 ?        S      5:04  \_ postgres: exilog exilog 146.232.128.59(40620) idle
 3483 ?        S      0:50  \_ postgres: exilog exilog 146.232.128.49(33803) INSERT
 3484 ?        S      0:04  \_ postgres: exilog exilog 146.232.128.49(33804) idle
 3485 ?        S      0:02  \_ postgres: exilog exilog 146.232.128.49(33805) idle

dstat-output shows a lot of IO.  It is actually better now. Earlier today
it was constantly about 50M per line:

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq|_read _writ|_recv _send|__in_ _out_|_int_ _csw_
  4   1  87   9   0   0|  24M   15M|   0     0 |  96B   10k|1108  1902
  3   1  85  11   0   0|  38M   32M|7242B 8796B|   0     0 |1175  1501
  2   0  88  10   0   0|  27M   33M|9949B   10k|   0     0 | 737   714
  1   1  87  10   0   0|  24M   28M|2412B 2948B|   0     0 | 637   533
  2   0  87  11   0   0|  28M   38M|2507B 2306B|   0     0 | 789   765
  2   1  87   9   0   0|  40M   38M|2268B 2450B|   0     0 | 900   795
  2   1  85  12   0   0|  33M   25M|4753B 3376B|  36k    0 | 950  1217
  2   2  80  16   0   0|  24M   28M|2590B 2738B|   0     0 | 899  1487
  2   1  84  12   0   0|  32M   40M|2603B 3025B|   0     0 |1042  1377
  2   0  86  11   0   0|  28M   30M|8530B 9116B|   0     0 |1054  1302
  1   0  77  22   0   0|8412k   12M|  12k   12k|   0     0 | 854  1286

Apparently the best approach is not to have very large tables.  I am
thinking of making (as far as the firewall is concerned) a different
table for each day and then drop the older tables as necessary.

Any advice on how to best handle this kind of setup will be
appreciated.

Regards
Johann
--
Johann Spies          Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch

     "Be careful for nothing; but in every thing by prayer
      and supplication with thanksgiving let your requests
      be made known unto God. And the peace of God, which
      passeth all understanding, shall keep your hearts
      hearts and minds through Christ Jesus."
                           Philippians 4:6,7

Re: Handling large volumes of data

От
"Shoaib Mir"
Дата:


On Tue, Apr 8, 2008 at 7:42 PM, Johann Spies <jspies@sun.ac.za> wrote:
Apparently the best approach is not to have very large tables.  I am
thinking of making (as far as the firewall is concerned) a different
table for each day and then drop the older tables as necessary.

Any advice on how to best handle this kind of setup will be
appreciated.

 
Table paritioning is what you need --> http://www.postgresql.org/docs/current/static/ddl-partitioning.html and then I will also advise distribute your tables across different disks through tablespaces. Tweak the shared buffers and work_mem settings as well. 
 
 
--
Shoaib Mir
Fujitsu Australia Software Technology

Re: Handling large volumes of data

От
Tino Schwarze
Дата:
On Tue, Apr 08, 2008 at 11:42:34AM +0200, Johann Spies wrote:

> 12501 ?        S      0:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c
config_file=/etc/postgresql/8.1/main/postgresql.conf
> 12504 ?        D      0:54  \_ postgres: writer process
> 12505 ?        S      1:42  \_ postgres: stats buffer process
> 12506 ?        S      1:03  |   \_ postgres: stats collector process
> 15918 ?        D    1214:28  \_ postgres: autovacuum process   firewall
> 18613 ?        D    130:08  \_ postgres: exilog exilog 146.232.128.197(53907) SELECT
> 18614 ?        S     12:42  \_ postgres: exilog exilog 146.232.128.197(53908) idle
> 31932 ?        D     16:11  \_ postgres: exilog exilog 146.232.128.106(36547) INSERT
> 10380 ?        S      0:00  \_ postgres: log firewall [local] DROP TABLE waiting

The drop table will wait for the autovacuum to finish. You might want to
kill the autovacuum process (this doesn't do any harm, just aborts the
operation so the drop table may proceed).

Bye,

Tino.

--
„Es gibt keinen Weg zum Frieden. Der Frieden ist der Weg.” (Mahatma Gandhi)

www.craniosacralzentrum.de
www.forteego.de

Re: Handling large volumes of data

От
Michael Monnerie
Дата:
On Dienstag, 8. April 2008 Johann Spies wrote:
> This took about a week on a 2xCPU quadcore server with 8Gb RAM.

This is not the most interesting thing here. What disk I/O subsystem do
you use? At least a hardware RAID controller with RAID 0 or 10 should
be used, with 10krpm or 15krpm drives. SAS preferred, as on SATA the
only quick disks are Western Digital Raptor (which aren't too bad,
btw).

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4
Вложения

Re: Handling large volumes of data

От
Johann Spies
Дата:
On Tue, Apr 08, 2008 at 12:01:14PM +0200, Michael Monnerie wrote:
> On Dienstag, 8. April 2008 Johann Spies wrote:
> > This took about a week on a 2xCPU quadcore server with 8Gb RAM.
>
> This is not the most interesting thing here. What disk I/O subsystem do
> you use? At least a hardware RAID controller with RAID 0 or 10 should
> be used, with 10krpm or 15krpm drives. SAS preferred, as on SATA the
> only quick disks are Western Digital Raptor (which aren't too bad,
> btw).

I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950
server. I am using an XFS-filesystem.  I am not certain about the
speed of the hard disk, but we bought the fastest we could get.

Regards
Johann


--
Johann Spies          Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch

     "Be careful for nothing; but in every thing by prayer
      and supplication with thanksgiving let your requests
      be made known unto God. And the peace of God, which
      passeth all understanding, shall keep your hearts
      hearts and minds through Christ Jesus."
                           Philippians 4:6,7

Re: Handling large volumes of data

От
Tino Schwarze
Дата:
On Tue, Apr 08, 2008 at 12:13:36PM +0200, Johann Spies wrote:

> > > This took about a week on a 2xCPU quadcore server with 8Gb RAM.
> >
> > This is not the most interesting thing here. What disk I/O subsystem do
> > you use? At least a hardware RAID controller with RAID 0 or 10 should
> > be used, with 10krpm or 15krpm drives. SAS preferred, as on SATA the
> > only quick disks are Western Digital Raptor (which aren't too bad,
> > btw).
>
> I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950
> server.

I thought, the 2950 can only take 6 3.5" disks? Or are these 72GB 2.5"
ones? Then there are 15k SAS drives available.

> I am using an XFS-filesystem.  I am not certain about the
> speed of the hard disk, but we bought the fastest we could get.

Bye,

Tino.

--
„Es gibt keinen Weg zum Frieden. Der Frieden ist der Weg.” (Mahatma Gandhi)

www.craniosacralzentrum.de
www.forteego.de

Re: Handling large volumes of data

От
Johann Spies
Дата:
On Tue, Apr 08, 2008 at 11:55:00AM +0200, Tino Schwarze wrote:

> The drop table will wait for the autovacuum to finish. You might want to
> kill the autovacuum process (this doesn't do any harm, just aborts the
> operation so the drop table may proceed).

Thanks. That is good to know.

Regards
Johann

--
Johann Spies          Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch

     "Be careful for nothing; but in every thing by prayer
      and supplication with thanksgiving let your requests
      be made known unto God. And the peace of God, which
      passeth all understanding, shall keep your hearts
      hearts and minds through Christ Jesus."
                           Philippians 4:6,7

Re: Handling large volumes of data

От
"Tena Sakai"
Дата:

Hi,

> So I decided maybe it is a good thing to run 'autovacuum analyse' to
> clean up things.  After a few days the process is still running and
> the load average still constantly about 5.

> I then decided to just drop the tables.  I did that about 18 hours ago
> and still there is no sign of Postgresql finishing that (pid 18614 below).

A few months ago I had something kinda similar and asked advice on this
very list.  Someone told me to look at a view called pg_stat_activity.
Like this: select * from pg_stat_activity;

It gave me insightful tips.  One of the columns it reports is called
"waiting" and it shows which process has exclusive write on a table
and which process is being blocked from writing to the table.  (I may
not be saying things perfectly correctly, but you get the gist of it.)
You might want to take a look at this view (there is nothing complicated
about running it) and you might find out a thing or two that is insightful.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Johann Spies
Sent: Tue 4/8/2008 2:42 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Handling large volumes of data

I want to set up a system where logs of all kinds can be put in tables
so that queries and reports can be generated from there.  Our Firewall
logs alone generate about 600,000,000 lines per month and that will
increase as we get more bandwidth.

I am testing postgresql's ability to handle large datasets.  I have
loaded about 4,900,000,000 in one of two tables with 7200684 in the
second table in database 'firewall', built one index using one
date-field (which took a few days) and used that index to copy about
3,800,000,000 of those records from the first to a third table,
deleted those copied record from the first table and dropped the third
table.

This took about a week on a 2xCPU quadcore server with 8Gb RAM.  During and
after that exercise the server was lethargic and constantly ran at a
load average of at least 5.

So I decided maybe it is a good thing to run 'autovacuum analyse' to
clean up things.  After a few days the process is still running and
the load average still constantly about 5. 

I then decided to just drop the tables.  I did that about 18 hours ago
and still there is no sign of Postgresql finishing that (pid 18614 below).

The machine is sluggish.  A 'ps fanx'  shows:

12501 ?        S      0:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c config_file=/etc/postgresql/8.1/main/postgresql.conf
12504 ?        D      0:54  \_ postgres: writer process
12505 ?        S      1:42  \_ postgres: stats buffer process
12506 ?        S      1:03  |   \_ postgres: stats collector process
15918 ?        D    1214:28  \_ postgres: autovacuum process   firewall
18613 ?        D    130:08  \_ postgres: exilog exilog 146.232.128.197(53907) SELECT
18614 ?        S     12:42  \_ postgres: exilog exilog 146.232.128.197(53908) idle
31932 ?        D     16:11  \_ postgres: exilog exilog 146.232.128.106(36547) INSERT
10380 ?        S      0:00  \_ postgres: log firewall [local] DROP TABLE waiting
20753 ?        D      5:11  \_ postgres: exilog exilog 146.232.128.197(43581) INSERT
16370 ?        S      5:04  \_ postgres: exilog exilog 146.232.128.59(40620) idle
 3483 ?        S      0:50  \_ postgres: exilog exilog 146.232.128.49(33803) INSERT
 3484 ?        S      0:04  \_ postgres: exilog exilog 146.232.128.49(33804) idle
 3485 ?        S      0:02  \_ postgres: exilog exilog 146.232.128.49(33805) idle

dstat-output shows a lot of IO.  It is actually better now. Earlier today
it was constantly about 50M per line:

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq|_read _writ|_recv _send|__in_ _out_|_int_ _csw_
  4   1  87   9   0   0|  24M   15M|   0     0 |  96B   10k|1108  1902
  3   1  85  11   0   0|  38M   32M|7242B 8796B|   0     0 |1175  1501
  2   0  88  10   0   0|  27M   33M|9949B   10k|   0     0 | 737   714
  1   1  87  10   0   0|  24M   28M|2412B 2948B|   0     0 | 637   533
  2   0  87  11   0   0|  28M   38M|2507B 2306B|   0     0 | 789   765
  2   1  87   9   0   0|  40M   38M|2268B 2450B|   0     0 | 900   795
  2   1  85  12   0   0|  33M   25M|4753B 3376B|  36k    0 | 950  1217
  2   2  80  16   0   0|  24M   28M|2590B 2738B|   0     0 | 899  1487
  2   1  84  12   0   0|  32M   40M|2603B 3025B|   0     0 |1042  1377
  2   0  86  11   0   0|  28M   30M|8530B 9116B|   0     0 |1054  1302
  1   0  77  22   0   0|8412k   12M|  12k   12k|   0     0 | 854  1286

Apparently the best approach is not to have very large tables.  I am
thinking of making (as far as the firewall is concerned) a different
table for each day and then drop the older tables as necessary.

Any advice on how to best handle this kind of setup will be
appreciated.

Regards
Johann
--
Johann Spies          Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch

     "Be careful for nothing; but in every thing by prayer
      and supplication with thanksgiving let your requests
      be made known unto God. And the peace of God, which
      passeth all understanding, shall keep your hearts
      hearts and minds through Christ Jesus."
                           Philippians 4:6,7 

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Handling large volumes of data

От
Michael Monnerie
Дата:
On Dienstag, 8. April 2008 Johann Spies wrote:
> I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950
> server. I am using an XFS-filesystem.  I am not certain about the
> speed of the hard disk, but we bought the fastest we could get.

720G looks like SATA disks, probably with "only" 7.200rpm. The Western
Digital Raptor with 10.000rpm are about twice as fast on random access.
Also, RAID5/6 are very,very slow when it comes to small disk *writes*.
Which is exactly what a db makes. Retry with RAID-10, and you'll see
the difference.

We've had 6 WD Raptors in RAID-6 on an Areca RAID, and changed it to 8x
Raptor RAID-10, that improved things.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4
Вложения

Re: Handling large volumes of data

От
Andrew Sullivan
Дата:
On Tue, Apr 08, 2008 at 12:13:36PM +0200, Johann Spies wrote:
> I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950

Thow away your RAID 5.  It's a loser for this.  Raid 1+0 is what you need.

> server. I am using an XFS-filesystem.  I am not certain about the

On another note, I've had abysmal experiences with xfs on linux.  Like,
"Oops, everything's in lost+found now!" abysmal.

(Unfortunately, actually, in my experience Linux has only bad and worse
choices for database filesystems.  Maybe they'll get it right with ext4.)

A