TPC-H Performance Study

The TPC-H benchmark is widely used in the database community as a yardstick to assess the performance of a systems against large scale decision support applications. The benchmark is designed and maintained by the Transaction Processing Counsel.

In October 2006 we ran this benchmark on our AMD 3800 PC with 2GB RAM and ample disk space. The systems considered were PostgreSQL 8.1, MySQL 5.0 and MonetDB/SQL. All systems were installed with their default settings, i.e. it mimics an out-of-the-box situation that end-users will experience. For PostgreSQL we called the statistics utility directly after loading. The queries were run one after the other in a single client session several times.

	Scale factor 0.01			Scale factor 1			Scale factor 2			Scale factor 5
	msec			seconds			seconds			seconds
	M5	PG	MySQL	M5	PG	MySQL	M5	PG	MySQL	M5	MySQL
Q1	28	726	198	2.8	73.7	17.6	13.1	145.7	37.9	110	101
Q2	9	32	95	0.6	4.6	230.7	1.4	9.8	465	3	1422
Q3	11	112	302	0.5	42.2	23.3	2.5	43	137	37	372
Q4	10	27	29	0.7	3.4	2.1	3.1	E	98.4	20	290
Q5	12	35	801	0.6	1.6	20.2	2.3	E	3195.1	15
Q6	4	78	72	0.4	6.9	5.9	0.7	E	17.7	15	52
Q7	13	310	56	1.4	13.9	6.0	5.3	32.5	1531.6	49	19878
Q8	9	75	19	0.7	9.9	1.4	5.6	38	552.2	45	2248
Q9	12	14	77	1.1	143.9	9.9	5.6	564.9	2380.1	118
Q10	12	28	288	1.4	1.5	13.3	5.3	E	142.7	32	1353
Q11	6.5	36	15	0.2	2.2	1.3	0.5	3	233.6	3	E
Q12	9	105	156	1.5	9.1	6.6	9	E	59.3	41	165
Q13	47	107		4.0	13.8		25.3	22.9		39
Q14	7	73	323	0.1	5.9	30.6	2	E	1098.2	26
Q15	6	96		0.3	6.2		2.3	E		7
Q16	9	57	81	0.7	15.6	8.4	2.6	17.8	19.2	24	56
Q17	4	16	11	0.7	421m	1.1	10.8		2.3	23	206
Q18	11	147		2.0	15		10.8	168.5		26	-
Q19	18	109	10	3.1	12.7	0.4	7.1	78	1.2	36	366
Q20	20	3770	12	0.8		0.6	2.4		311.8	44	799
Q21	39	261	314	4.3	49.4	5.5	19.5	455.6	2605.9	127	-
Q22	18	659	14	0.9		0.5	2.1		2	6	5
load	-	-	-	1m58	5m34	2m34	7m22	13m20	5m53	65m00	-

			MonetDB is somewhat slower				MonetDB is >10x faster

			Takes >1hr to run				E	Error, empty result

Although set out as a small-scale experiment to assess our SQL implementation functionality, we were pleasantly surprised by the performance and scalability. Some observations on the results shown:

Confirmation The results obtained align with our experience in more detailed sciences studies (see our science library).
Scalability MonetDB was designed from a main-memory perspective, but its performance shows it is capable to grow well beyond the main memory capacity. Scale-factor 2 and 5 makes the database size larger than the available main memory. All systems experience dramatic increase in IO behavior.
Tuning The performance figures of PostgreSQL and MySQL can be improved by throwing database expertise at the problem. Buffer spaces may be tuned, configuration files tweaked, additional indices may be introduced, etc.. However, such expertise does not belong to the average user. He will experience a fast system provided the database remains small and the queries are not too complex.
Bulk loading The loading time is largely determined by the amount of data read/written. Turning the transaction logger off improves the performance significantly. Furthermore, PostgreSQL and MonetDB ensure database consistency by checking all referential constraints as well. This is ignored by MySQL.