First of all, this is not an attempt to create a flamewar of any
kind. I have been a MySQL user for years, including owning a few
multi-gigabyte databases for my websites, and using it to host
algebra.com (about 12 GB database).

I have had my ups and downs with MySQL. The ups were ease of use and
decent performance for small databases such as algebra.com. The downs
were things like twenty hour REPAIR TABLE operations on a 35 GB
table, etc.

Right now I have a personal (one user) project to create a 5-10
Terabyte data warehouse. The largest table will consume the most space
and will take, perhaps, 200,000,000 rows.

I want to use it to obtain valuable business intelligence and to make
money.

I expect it to grow, never shrink, and to be accessed via batch
queries. I do not care for batch queries to be super fast, for example
an hour per query would be just fine.

However, while an hour is fine, two weeks per query is NOT fine.

I have a server with about 18 TB of storage and 48 GB of RAM, and 12
CPU cores.

My initial plan was to use MySQL, InnoDB, and deal with problems as
they arise. Perhaps, say, I would implement my own joining
procedures.

After reading some disparaging stuff about InnoDB performance on large
datasets, however, I am getting cold feet. I have a general feeling
that, perhaps, I will not be able to succeed with MySQL, or, perhaps,
with either MySQL and Postgres.

I do not know much about Postgres, but I am very eager to learn and
see if I can use it for my purposes more effectively than MySQL.

I cannot shell out $47,000 per CPU for Oracle for this project.

To be more specific, the batch queries that I would do, I hope,
would either use small JOINS of a small dataset to a large dataset, or
just SELECTS from one big table.

So... Can Postgres support a 5-10 TB database with the use pattern
stated above?

i

On 10-09-2011 08:30, Michael Vilain wrote:
nice typo


--
Luuk

Yeah, they do. I'm most of the way through repairing a 57G table which
takes about a day and a half.

Hmmn. This suggests that you might want to reread the manual.

A few moments spent looking at the MySQL manual reveals partitioned
tables and MERGE tables, both of which are reasonable ways to divide
one logical table into several physical ones. If your data just
grows, but the oldest stuff never changes, merged tables may be
appropriate, since you can make the older tables packed and read-only.
Either way, the individual tables can be much smaller and more
tractable.

If your data naturally falls into slices (by numeric value in 5.1,
number or string in 5.5), partitioned tables may work, particularly if
you can construct your queries to take advantage of partition pruning
in your queries.

R's,
John

On 2011-09-10, John Levine <johnl (AT) iecc (DOT) com> wrote:
Then you know!

At first, I really liked your idea of a MERGE. It does fit my dataset
very well. I had NO idea that it was possible. I thought that I can,
essentially, split my data by month.

Then I realized that I perform my updates for the more recent items by
means of INSERT... ON DUPLICATE KEY UPDATE, and it would not work with
MERGE tables.


``Because the MERGE engine cannot enforce uniqueness over the set of
underlying tables''

Maybe I can operate it by slicing data by, say, last digit of the
numeric ID. (I get the numeric ID of my items with the items, they
already have a natural unique key)

The more I think about the problem, the more I have a bad feeling
about it.

i

Ignoramus5263 <ignoramus5263 (AT) NOSPAM (DOT) 5263.invalid> wrote:
If nobody here gives you a concrete answer, I suggest asking on the
pgsql-performance mailing list; it's the sort of question they're used
to answering.

http://archives.postgresql.org/pgsql-performance/

-M-

On 2011-09-11, Matthew Woodcraft <mattheww (AT) chiark (DOT) greenend.org.uk> wrote:
Thanks. I sbuscribed and sent the same question, slightly reworded, to that list.

Ignoramus13208 wrote:
so do it a different way.

Ignoramus13208 <ignoramus13208 (AT) NOSPAM (DOT) 13208.invalid> wrote:
I doubt that. MERGE is the much older and quite crappy sister of
partitioned tables. And since you claimed partitions would not fit,
MERGE won't too.

Also the MERGE engine is notoriously unstable. I expect to see
support for MERGE tables being dropped soon.


XL

On 2011-09-11, Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:
OK, thanks for letting me know... I will not even consider...

i

That should be no problem.
We run a 250m rows beast here unpartitioned.
Updated in real time.

BTW select count(*) needs 5 minutes under full load and 2 minutes on a smaller but less busy box.

regards,

Toni

MySQL Innobase 5.5 and 5.1