Streaming large data into postgres [WORM like applications]

Inserting 50,000 rows a second is, uh... difficult to do, no matter
what database you're using. You'll probably have to spool the inserts
and insert them as fast as you can, and just hope you don't fall too
far behind.

But I'm suspecting that you aren't going to be doing much, if any,
referential integrity checking, at least beyond basic type checking.
You probably aren't going to care about multiple inserts affecting
each other, or worry about corruption if a given insert fails... in
fact, you probably aren't even going to need transactions at all,
other than as a way to insert faster. Is SQL the right tool for you?

On May 11, 2007, at 1:43 PM, Dhaval Shah wrote:

I do care about the following:

1. Basic type checking
2. Knowing failed inserts.
3. Non-corruption
4. Macro transactions. That is a minimal read consistency.

The following is not necessary

1. Referential integrity

In this particular scenario,

1. There is a sustained load and peak loads. As long as we can handle
peak loads, the sustained loads can be half of the quoted figure.
2. The row size has limited columns. That is, it is spans at most a
dozen or so columns and most integer or varchar.

It is more data i/o heavy rather than cpu heavy.

Regards
Dhaval

On 5/11/07, Ben <bench@silentmedia.com> wrote:

Inserting 50,000 rows a second is, uh... difficult to do, no matter
what database you're using. You'll probably have to spool the inserts
and insert them as fast as you can, and just hope you don't fall too
far behind.

But I'm suspecting that you aren't going to be doing much, if any,
referential integrity checking, at least beyond basic type checking.
You probably aren't going to care about multiple inserts affecting
each other, or worry about corruption if a given insert fails... in
fact, you probably aren't even going to need transactions at all,
other than as a way to insert faster. Is SQL the right tool for you?

On May 11, 2007, at 1:43 PM, Dhaval Shah wrote:

Here is the straight dope, one of internal teams at my customer site
is looking into MySql and replacing its storage engine so that they
can store large amount of streamed data. The key here is that the data
they are getting is several thousands of rows in an extremely short
duration. They say that only MySql provides them the ability to
replace the storage engine, which granted is easier.

If I go with the statement that postgres can basically do what they
intend to do for handling large datasets, I need to prepare my talking
points.

The requirements are as follows:

1. Large amount of streamed rows. In the order of @50-100k rows per
second. I was thinking that the rows can be stored into a file and the
file then copied into a temp table using copy and then appending those
rows to the master table. And then dropping and recreating the index
very lazily [during the first query hit or something like that]

The table size can grow extremely large. Of course, if it can be
partitioned, either by range or list.

2. Most of the streamed rows are very similar. Think syslog rows,
where for most cases only the timestamp changes. Of course, if the
data can be compressed, it will result in improved savings in terms of
disk size.

The key issue here is that the ultimate data usage is Write Once Read
Many, and in that sense I am looking for a very optimal solution for
bulk writes and maintaining indexes during bulk writes.

So with some intelligent design, it is possible to use postgres. Any
help in preparing my talking points is appreciated.

Regards
Dhaval

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Dhaval Shah

One approach would be to spool all the data to a flat file and then
pull them into the database as you are able to. This would give you
extremely high peak capability.

On May 11, 2007, at 10:35 PM, Dhaval Shah wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/11/07 21:35, Dhaval Shah wrote:

I do care about the following:

1. Basic type checking
2. Knowing failed inserts.
3. Non-corruption
4. Macro transactions. That is a minimal read consistency.

The following is not necessary

1. Referential integrity

In this particular scenario,

1. There is a sustained load and peak loads. As long as we can handle
peak loads, the sustained loads can be half of the quoted figure.
2. The row size has limited columns. That is, it is spans at most a
dozen or so columns and most integer or varchar.

It is more data i/o heavy rather than cpu heavy.

Have you tested PG (and MySQL, for that matter) to determine what
kind of load they can handle on existing h/w?

Back to the original post: 100K inserts/second is 360 *million*
inserts per hour. That's a *lot*. Even if the steady-state is 50K
inserts/sec that's 180M inserts/hr. If each record is 120 bytes,
that's 43 gigabytes per hour. Which is 12MB/second. No problem
from a h/w standpoint.

However, it will fill a 300GB HDD in 7 hours.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGRZdhS9HxQb37XmcRAimAAJ9oi5AG0EcyATxeGDrlA1qdqU7krwCfc0k+
J7zMkiJiVKxS+DWM6I6Oujw=
=D04k
-----END PGP SIGNATURE-----

At 04:43 AM 5/12/2007, Dhaval Shah wrote:

1. Large amount of streamed rows. In the order of @50-100k rows per
second. I was thinking that the rows can be stored into a file and the
file then copied into a temp table using copy and then appending those
rows to the master table. And then dropping and recreating the index
very lazily [during the first query hit or something like that]

Is it one process inserting or can it be many processes?

Is it just a short (relatively) high burst or is that rate sustained
for a long time? If it's sustained I don't see the point of doing so
many copies.

How many bytes per row? If the rate is sustained and the rows are big
then you are going to need LOTs of disks (e.g. a large RAID10).

When do you need to do the reads, and how up to date do they need to be?

Regards,
Link.

Consolidating my responses in one email.

1. The total data that is expected is some 1 - 1.5 Tb a day. 75% of
the data comes in a period of 10 hours. Rest 25% comes in the 14
hours. Of course there are ways to smooth the load patterns, however
the current scenario is as explained.

2 I do expect that the customer rolls in something like a NAS/SAN with
Tb of disk space. The idea is to retain the data for a duration and
offload it to tape.

That leads to the question, can the data be compressed? Since the data
is very similar, any compression would result in some 6x-10x
compression. Is there a way to identify which partitions are in which
data files and compress them until they are actually read?

Regards
Dhaval

On 5/12/07, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:

At 04:43 AM 5/12/2007, Dhaval Shah wrote:

1. Large amount of streamed rows. In the order of @50-100k rows per
second. I was thinking that the rows can be stored into a file and the
file then copied into a temp table using copy and then appending those
rows to the master table. And then dropping and recreating the index
very lazily [during the first query hit or something like that]

Is it one process inserting or can it be many processes?

Is it just a short (relatively) high burst or is that rate sustained
for a long time? If it's sustained I don't see the point of doing so
many copies.

How many bytes per row? If the rate is sustained and the rows are big
then you are going to need LOTs of disks (e.g. a large RAID10).

When do you need to do the reads, and how up to date do they need to be?

Regards,
Link.

--
Dhaval Shah

At 8:49p on 12 May 2007, Dhaval Shah wrote:

That leads to the question, can the data be compressed? Since the data
is very similar, any compression would result in some 6x-10x
compression. Is there a way to identify which partitions are in which
data files and compress them until they are actually read?

There was a very interesting article in ;login: magazine in April of
this year discussing how they dealt with an exorbitant amount of largely
similar data. The article claimed that through aggregation and gzip
compression, they were able to reduce what they needed to store by
roughly 350x, or about .7 bytes per 'event'. The article is

The Secret Lives of Computers Exposed: Flight Data Recorder for Windows
by Chad Verbowski

You might try to get your mitts on that article for some ideas. I'm not
sure you could apply any of their ideas directly to the Postgres backend
data files, but perhaps somewhere in your pipeline.

Kevin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/12/07 19:49, Dhaval Shah wrote:

Consolidating my responses in one email.

1. The total data that is expected is some 1 - 1.5 Tb a day. 75% of
the data comes in a period of 10 hours. Rest 25% comes in the 14
hours. Of course there are ways to smooth the load patterns, however
the current scenario is as explained.

2 I do expect that the customer rolls in something like a NAS/SAN with
Tb of disk space. The idea is to retain the data for a duration and
offload it to tape.

45TB per month???? Wow.

The archival process *must* be considered when designing the system.

PostgreSQL's ability to use tablespaces and partitioned tables will
make that much easier. Otherwise, you'd have to be deleting from
one "side" of the table while inserting into the other "side".

Partitioning will also let you divide the table into multiple
"active" segments, so that multiple inserters can run simultaneously
without stepping on each other while spreading the load across
multiple controllers and RAID-sets.

If it's a SAN/NAS that is organized into RAID-5 groups, make *sure*
that it has *lots* of batter-backed write-back cache.

Regarding compression: if the columns are integers or short VARCHAR
fields, I do not see how compression can help you, unless you use
block-layer compression.

Which Linux doesn't do.

Does FreeBSD have block-level compression?

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGR8mtS9HxQb37XmcRAjXQAJ9TN2FqU1Wo4PZmS6MAhxaJgCm6/wCfXIl8
wZOYG7vWxwODNaRwDGSJxYQ=
=Qh2r
-----END PGP SIGNATURE-----

Dhaval Shah wrote:

2. Most of the streamed rows are very similar. Think syslog rows,
where for most cases only the timestamp changes. Of course, if the
data can be compressed, it will result in improved savings in terms of
disk size.

If it really is usually just the timestamp that changes, one way to
"compress" such data might be to split your logical row into two
tables. First table has all the original columns but the timestanp,
plus an ID. Second table has the timestamp and a foreign key into
the first table. Depending on how wide your original row is, and how
often it's only the timestamp that changes, this could result in
decent "compression".

Of course, now you need referential integrity.

- John D. Burger
MITRE

John D. Burger wrote:

Dhaval Shah wrote:

2. Most of the streamed rows are very similar. Think syslog rows,
where for most cases only the timestamp changes. Of course, if the
data can be compressed, it will result in improved savings in terms of
disk size.

If it really is usually just the timestamp that changes, one way to
"compress" such data might be to split your logical row into two
tables. First table has all the original columns but the timestanp,
plus an ID. Second table has the timestamp and a foreign key into the
first table. Depending on how wide your original row is, and how often
it's only the timestamp that changes, this could result in decent
"compression".

Of course, now you need referential integrity.

I thought of something similar. Maybe you could put those timestamps in
an array column; saves you a referential integrity check that you don't
seem to need very much.

OTOH, _if_ your log messages(?) look very similar each time, you may be
able to turn the problem around; you store unique log messages, with the
timestamps that they occured on. That way you rarely need to store more
than a timestamp. It'll add time to look up the matching log message
(there'll be quite a few less of them though). I'm quite confident
you'll save time inserting records this way, although that's hand waving
at this point.

You may be able to parameterize some log messages and store the
parameters with the timestamps. Takes a bit more processing though
(regular expression matches maybe?), and you'll want to now all
different log message permutations beforehand.

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //