Re: Benchmark Data requested

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Doing it at low scales is not attractive.

Commercial databases are publishing at scale factor of 1000(about 1TB) to 10000(10TB) with one in 30TB space. So ideally right now tuning should start at 1000 scale factor.

Unfortunately I have tried that before with PostgreSQL the few of the problems are as follows:

Single stream loader of PostgreSQL takes hours to load data. (Single stream load... wasting all the extra cores out there)

Multiple table loads ( 1 per table) spawned via script is bit better but hits wal problems.

To avoid wal problems, I had created tables and load statements within the same transaction, faster but cannot create index before load or it starts writing to wal... AND if indexes are created after load, it takes about a day or so to create all the indices required. (Its single threaded and creating multiple indexes/indices at the same time could result in running out of temporary "DISK" space since the tables are so big. Which means 1 thread at a time is the answer for creating tables that are really big. It is slow.

Boy, by this time most people doing TPC-H in high end give up on PostgreSQL.

I have not even started Partitioning of tables yet since with the current framework, you have to load the tables separately into each tables which means for the TPC-H data you need "extra-logic" to take that table data and split it into each partition child table. Not stuff that many people want to do by hand.

Then for the power run that is essentially running one query at a time should essentially be able to utilize the full system (specially multi-core systems), unfortunately PostgreSQL can use only one core. (Plus since this is read only and there is no separate disk reader all other processes are idle) and system is running at 1/Nth capacity (where N is the number of cores/threads)

(I am not sure here with Partitioned tables, do you get N processes running in the system when you scan the partitioned table?)

Even off-loading work like "fetching the data into bufferpool" into separate processes will go big time with this type of workloads.

I would be happy to help out if folks here want to do work related to it. Infact if you have time, I can request a project in one of the Sun Benchmarking center to see what we can learn with community members interested in understanding where PostgreSQL performs and fails.

Regards,
Jignesh

Greg Smith wrote:
On Mon, 4 Feb 2008, Simon Riggs wrote:

Would anybody like to repeat these tests with the latest production
versions of these databases (i.e. with PGSQL 8.3)

Do you have any suggestions on how people should run TPC-H? It looked like a bit of work to sort through how to even start this exercise.

--
* Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux