PostgreSQL Advocacy - MySQL million tables

Author

MySQL million tables

Christopher Kings-Lynne

2006-03-09, 3:24 am

I see in this post on planetmysql.org that this guy got MySQL to die
after creating 250k tables. Anyone want to see how far PostgreSQL can
go? :)

http://bobfield.blogspot.com/2006/0...ion-tables.html

Also, can we beat his estimate of 27 hours for creation?

Chris

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jonah H. Harris

2006-03-09, 3:24 am

On 3/9/06, Christopher Kings-Lynne < chriskl@familyhealth
.com.au> wrote:
>
> Also, can we beat his estimate of 27 hours for creation?

I just tried it on my a Thinkpad:(Pentium M @ 1.73GHz) running SuSE 10 and
an untuned PostgreSQL 8.1.3 using a shell script and psql. I was able to
create 274,000 tables in exactly 798 seconds.

And it's still chugging away :)

Chris

>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Stefan 'Kaishakunin' Schumacher

2006-03-09, 3:24 am

Also sprach Christopher Kings-Lynne (chriskl@familyhealt
h.com.au)
> I see in this post on planetmysql.org that this guy got MySQL to die
> after creating 250k tables. Anyone want to see how far PostgreSQL can
> go? :)
>
> http://bobfield.blogspot.com/2006/0...ion-tables.html
>
> Also, can we beat his estimate of 27 hours for creation?

for i in `seq 1 1000000`;
do
echo "create table test$i (ts timestamp);"|psql tabletorture;
done

This is a Pentium 3 with 256MB Ram, I'll see how far it gets and how
long it takes.

Now I got ~2600 tables in 4 minutes, so it might take a day to get the
million. However, the MySQL-guy has no statements about his
test-machine, so it's not comparable.

--
PGP FPR: CF74 D5F2 4871 3E5C FFFE 0130 11F4 C41E B3FB AE33
--
Im Klang der Gion-Shoja-Glocken tönt die Vergänglichkeit aller Dinge,
die Farbe der Sala-Blüte offenbart, daß die Erfolgreichen fallen müssen.
Die Stolzen währen nicht ewig, sie vergehen wie der Traum einer Frühlingsnacht.
Die Mächtigen fallen zuletzt, sie sind wie Staub vor dem Winde. Heike Monogatari

Christopher Kings-Lynne

2006-03-09, 3:24 am

Another mysql blogger has chimed in now:

http://arjen-lentz.livejournal.com/66547.html

He did it with MyISAM tables though.

Chris

Jonah H. Harris wrote:
> On 3/9/06, *Christopher Kings-Lynne* < chriskl@familyhealth
.com.au
> < mailto:chriskl@famil
yhealth.com.au>> wrote:
>
> Also, can we beat his estimate of 27 hours for creation?
>
>
> I just tried it on my a Thinkpad:(Pentium M @ 1.73GHz) running SuSE 10
> and an untuned PostgreSQL 8.1.3 using a shell script and psql. I was
> able to create 274,000 tables in exactly 798 seconds.
>
> And it's still chugging away :)
>
>
> Chris
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
>
> --
> Jonah H. Harris, Database Internals Architect
> EnterpriseDB Corporation
> 732.331.1324

---------------------------(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

Jonah H. Harris

2006-03-09, 3:24 am

On 3/9/06, Stefan 'Kaishakunin' Schumacher <stefan@net-tex.de> wrote:
>
> Also sprach Christopher Kings-Lynne (chriskl@familyhealt
h.com.au)
>
> for i in `seq 1 1000000`;
> do
> echo "create table test$i (ts timestamp);"|psql tabletorture;
> done

My results were based on 500 CREATE TABLE's per transaction, so my results
aren't comparable either.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Jean-Paul Argudo

2006-03-09, 3:24 am

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

Hi all,

I dont think this kind of test could have any meaning, given that table
creation time doesnt import in any way.

.... but I was curious, so I made the test :)

We would need his complete machine specs to be sure we can beat him.
[color=darkred]
> for i in `seq 1 1000000`;
> do
> echo "create table test$i (ts timestamp);"|psql tabletorture;
> done

Just tried that script (still running) on a Dell PowerEdge 2800 (not my
prefered, but only one I could use for that test...)

2 physical CPUS in ht: Intel(R) Xeon(TM) CPU 3.00GHz with 2m cache
2Gb ram

Builtin Perc4, with Raid 5 and 5+1 10Krpm disks

Linux ***** 2.4.27-2-686-smp #1 SMP Wed Aug 17 10:05:21 UTC 2005 i686
GNU/Linux

I can send more details if you want (postgresql.conf..)

> Now I got ~2600 tables in 4 minutes, so it might take a day to get the
> million. However, the MySQL-guy has no statements about his
> test-machine, so it's not comparable.

I got a rate of 12 tables created per second (fluctuating from 10 to 13
so thats an approximation I do, Ill get final results on the end).

So at the moment I'll have 1M table in about 23 hours on that server.

I'll send complete results when it will be finished.

- --
Jean-Paul Argudo
www.PostgreSQLFr.org
www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFED+9anDfYiZOk
HKQRAgkSAJ0SUM5GfouY
bushribtHPzGtfWBPQCf
fWNc
4vwrAr9z8p+8AAqL2ndF
lP4=
=bX5g
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Greg Sabino Mullane

2006-03-09, 7:26 am

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

I kicked this off last night before bed. It ran much quicker than
I thought, due to that 27 hour estimate.

Total time: 23 minutes 29 seconds :)

I committed every 2000 table commits. It could probably be made
to go slightly faster, as this was an out-of-the-box Postgres database,
with no postgresql.conf tweaking. I simply piped a text file into
psql for the testing, from the outout of a quick perl script:

my $max = 1_000_000;
my $check = 2_000;
print "BEGIN;\n";
for my $num (1..$max) {
print "CREATE TABLE foo$num (a smallint);\n";
$num%$check or print "COMMIT;\nBEGIN;\n";
}
print "COMMIT;\n";

And the proof:

greg=# select count(*) from pg_class where relkind='r' and relname ~ 'foo'
count
- ---------
1000000

Maybe I'll see just how far PG *can* go next. Time to make a PlanetPG post,
at any rate.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200603090720
http://biglumber.com/x/web? pk=2529...14964
AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFEEB2yvJuQZxSW
SsgRAvUbAKCzO80prZ4D
X4l3iT0Dh+Re5M4TpACf
W95z
y4cdkQjw2ubAP4btMwSw
5iw=
=Ginx
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Christopher Browne

2006-03-09, 9:24 am

A long time ago, in a galaxy far, far away, greg@turnstep.com ("Greg Sabino Mullane") wrote:
> I kicked this off last night before bed. It ran much quicker than
> I thought, due to that 27 hour estimate.
>
> Total time: 23 minutes 29 seconds :)

I'm jealous. I've got the very same thing running on some Supposedly
Pretty Fast Hardware, and it's cruising towards 31 minutes plus a few
seconds.

While it's running, the time estimate is...

select (now() - '2006-03-09 13:47:49') * 1000000 / (select count(*)
from pg_class where relkind='r' and relname ~ 'foo');

That pretty quickly converged to 31:0?...

> Maybe I'll see just how far PG *can* go next. Time to make a
> PlanetPG post, at any rate.

Another interesting approach to it would be to break this into several
streams.

There ought to be some parallelism to be gained, on systems with
multiple disks and CPUs, by having 1..100000 go in parallel to 100001
to 200000, and so forth, for (oh, say) 10 streams. Perhaps it's
irrelevant parallelism; knowing that it helps/hurts would be nice...
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://linuxfinances.info/info/rdbms.html
Where do you want to Tell Microsoft To Go Today?

Christopher Kings-Lynne

2006-03-09, 8:25 pm

> greg=# select count(*) from pg_class where relkind='r' and relname ~ 'foo'

> count
> - ---------
> 1000000
>
> Maybe I'll see just how far PG *can* go next. Time to make a PlanetPG post,
> at any rate.

Try \dt :D

Chris

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

Greg Sabino Mullane

2006-03-10, 9:25 am

Richard Huxton

2006-03-10, 1:24 pm

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
>
> Sure. Took 42 seconds, but it showed up just fine. :)

The real test is to put a few rows into each, join the lot and see how
long it takes geqo to plan it.

Actually, if that works the real test is to find a use for this :-)

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jim Nasby

2006-03-10, 8:24 pm

I can't believe y'all are burning cycles on this. :P

On Mar 9, 2006, at 8:04 AM, Christopher Browne wrote:

> A long time ago, in a galaxy far, far away, greg@turnstep.com
> ("Greg Sabino Mullane") wrote:
>
> I'm jealous. I've got the very same thing running on some Supposedly
> Pretty Fast Hardware, and it's cruising towards 31 minutes plus a few
> seconds.
>
> While it's running, the time estimate is...
>
> select (now() - '2006-03-09 13:47:49') * 1000000 / (select count(*)
> from pg_class where relkind='r' and relname ~ 'foo');
>
> That pretty quickly converged to 31:0?...
>
>
> Another interesting approach to it would be to break this into several
> streams.
>
> There ought to be some parallelism to be gained, on systems with
> multiple disks and CPUs, by having 1..100000 go in parallel to 100001
> to 200000, and so forth, for (oh, say) 10 streams. Perhaps it's
> irrelevant parallelism; knowing that it helps/hurts would be nice...
> --
> (format nil "~S@~S" "cbbrowne" "cbbrowne.com")
> http://linuxfinances.info/info/rdbms.html
> Where do you want to Tell Microsoft To Go Today?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Guido Barosio

2006-03-10, 8:24 pm

Well,

This is a WTF case, but year ago, a request arrived from the
Develociraptors to the DBA team.

Their need was a 2 terabyte db [with a particular need, continue]. They
did benchmark on both mysql and postgresql, and believe me, it was funny,
cause the DBA team refuse to support the idea, and left the funny und wild
developmentiraptors on their own.

The result? A script creating more o less 40.000 (oh yeah, like the foo$i
one) tables on a mysql db and making it almost unable to be browsed, but
live, currently in a beta stage, but freezed as the lack of support.
(without DBA's support, again)

Lovely! But you never know with these things, you neeever know.

note: I've created 250k tables in 63 minutes using the perl script from a
previous post, on my own workstation. (RH3, short on ram, average CPU,
ebay.com used and shipped from the north to the south crappy drive)

g.-

On 3/11/06, Jim Nasby <jim@nasby.net> wrote:
>
> I can't believe y'all are burning cycles on this. :P
>
> On Mar 9, 2006, at 8:04 AM, Christopher Browne wrote:
>
>
> --
> Jim C. Nasby, Database Architect decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
/"\ ASCII Ribbon Campaign .
\ / - NO HTML/RTF in e-mail .
X - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------

Joshua D. Drake

2006-03-10, 8:24 pm

Jim Nasby wrote:
> I can't believe y'all are burning cycles on this. :P
Your kidding right? Have you seen the discussions that happen this list? ;)

Joshua D. Drake

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Robert Treat

2006-03-10, 8:24 pm

I can't believe the mysql guys found this to be non-trivial.

http://bitbybit.dk/carsten/blog/?p=83
http://www.flamingspork.com/blog/20...million-tables/

On Friday 10 March 2006 19:17, Jim Nasby wrote:
> I can't believe y'all are burning cycles on this. :P
>
> On Mar 9, 2006, at 8:04 AM, Christopher Browne wrote:
>
> --
> Jim C. Nasby, Database Architect decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Robert Treat
Build A Brighter Lamp :: Linux apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Rick Ellis

2006-03-11, 8:24 pm

In article <200603102034.45797.xzilla@users.sourceforge.net>,
Robert Treat <xzilla@users.sourceforge.net> wrote:

>I can't believe the mysql guys found this to be non-trivial.

I can ;)

--
http://yosemitenews.info/

Jim C. Nasby

2006-03-15, 8:24 pm

On Fri, Mar 10, 2006 at 08:34:45PM -0500, Robert Treat wrote:
> I can't believe the mysql guys found this to be non-trivial.
>
> http://bitbybit.dk/carsten/blog/?p=83
> http://www.flamingspork.com/blog/20...million-tables/

So did anyone complete the million table test? We have anything to post?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

http://archives.postgresql.org

Chris

2006-03-15, 8:24 pm

Jim C. Nasby wrote:
> On Fri, Mar 10, 2006 at 08:34:45PM -0500, Robert Treat wrote:
>
>
>
> So did anyone complete the million table test? We have anything to post?

Already done:

http://people.planetpostgresql.org/...-challenge.html

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Sponsored Links