|
|
|
|
|
|
| Author |
Message |
Joshua D. Drake
*nix forums Guru
Joined: 09 Mar 2005
Posts: 341
|
 Posted: Tue Mar 15, 2005 9:13 pm Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
| Quote: | Also, a 32-bit machine can only hold so much RAM. If I'm correct, there
are ways to address more memory than that on a 32 bit machine, but I
wonder at what cost? In other words, is it a good idea to address more
than 4GB on a 32 bit machine? If not, is it a reasonable choice to
invest in 64 bit if you want >4GB of RAM? Or are you better off just
spending the money on RAID and staying at 4GB?
|
It entirely depends on the database but not that the 32bit limit of 4GB
is per CPU. So if you have 4 CPUs you can have 16GB of ram.
However, you should be running Opterons anyway.
J
| Quote: |
Regards,
Jeff Davis
PS: A minor typo in "Disk and WAL" -> "checkpoint_segments":
s/Depening/Depending/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
-- |
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org |
|
| Back to top |
|
 |
Mark Rae
*nix forums addict
Joined: 14 Mar 2005
Posts: 50
|
| Posted: Tue Mar 15, 2005 10:31 pm Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote:
| Quote: | Be careful assuming that. DB benchmarks are hard to do in a general
sense. His results probably indicate a general trend, but you should
test your application yourself to get a real result. His pattern of SQL
queries might be very different from yours.
|
Very true.
You may have noticed that I had a very low query rate of 5.8 queries
per second, because some of the queries have 12 tables to join and
take about 20s to run. This tends to work in postgres' favour.
If you have lots have simple queries, it will be better for mysql
and the break even point will be higher.
Also, while on the subject of scaling. I had the opportunity
to try postgres on a 16CPU Altix and couldn't get it to scale
more than about 4x, whereas Oracle got up to about 12x faster
I assume this is because of the NUMA architecture. I was also
told that Oracle had made no special optimizations to accomodate it.
My guess is that because postgres allocates all its shared
buffers as a contiguous chunk, it puts all the load on one
memory bank.
Oracle on the other hand, seems to use lots of smaller regions
which would probably be spread throughout the physical memory.
Perhaps one of the developers could comment on how difficult
it would be to change the shared buffer handling to use multiple
segments. As I'd definitely be willing to give it a go.
-Mark
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq |
|
| Back to top |
|
|
Mark Rae
*nix forums addict
Joined: 14 Mar 2005
Posts: 50
|
| Posted: Tue Mar 15, 2005 10:56 pm Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
On Tue, Mar 15, 2005 at 06:46:50PM -0500, Bruce Momjian wrote:
| Quote: | Mark Rae wrote:
Also, while on the subject of scaling. I had the opportunity
to try postgres on a 16CPU Altix and couldn't get it to scale
more than about 4x, whereas Oracle got up to about 12x faster
We have had some major SMP improvements in current CVS. Were you
testing that or 8.0.X?
|
It tried it with 8.0.0rc3, and had previously tried a 7.4 version
-Mark
---------------------------(end of broadcast)---------------------------
TIP 3: 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 |
|
| Back to top |
|
|
J. Greenlees
*nix forums beginner
Joined: 04 Mar 2005
Posts: 4
|
| Posted: Tue Mar 15, 2005 11:14 pm Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
Mark Rae wrote:
| Quote: | On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote:
Be careful assuming that. DB benchmarks are hard to do in a general
sense. His results probably indicate a general trend, but you should
test your application yourself to get a real result. His pattern of SQL
queries might be very different from yours.
Very true.
You may have noticed that I had a very low query rate of 5.8 queries
per second, because some of the queries have 12 tables to join and
take about 20s to run. This tends to work in postgres' favour.
If you have lots have simple queries, it will be better for mysql
and the break even point will be higher.
Also, while on the subject of scaling. I had the opportunity
to try postgres on a 16CPU Altix and couldn't get it to scale
more than about 4x, whereas Oracle got up to about 12x faster
I assume this is because of the NUMA architecture. I was also
told that Oracle had made no special optimizations to accomodate it.
My guess is that because postgres allocates all its shared
buffers as a contiguous chunk, it puts all the load on one
memory bank.
Oracle on the other hand, seems to use lots of smaller regions
which would probably be spread throughout the physical memory.
Perhaps one of the developers could comment on how difficult
it would be to change the shared buffer handling to use multiple
segments. As I'd definitely be willing to give it a go.
-Mark
|
a bit of info re mysql and speed with concurrent transactions.
a community site I was working to get running a bit better was using
phpnuke and mysql. ( not my site, was a contract )
with 56,000 members the site was bogged down almost to the point of
timing out, this was with only 100 or so users online.
another community site, with custom script using mysql backend, sperad
over several servers rather than one machine, and 250,000 members. ( 4
terabytes data transfer a month minimum. )
it's often slow responding, but doesn't get close to a timeout.
while these are subjective observations, they show that tuning, and
structure of application will have a significant affect, more than would
generally be assumed.
mysql is a good application, for lower traffic applications [ local
intranet with <100 users ], but I would never actually recommend using
mysql on a large database.[ large query useage would be horrendously slow ]
Jaqui |
|
| Back to top |
|
|
Mark Rae
*nix forums addict
Joined: 14 Mar 2005
Posts: 50
|
| Posted: Tue Mar 15, 2005 11:17 pm Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
On Tue, Mar 15, 2005 at 07:00:25PM -0500, Bruce Momjian wrote:
| Quote: | Oh, you have to try CVS HEAD or a nightly snapshot. Tom made a major
change that allows scaling in SMP environments.
|
Ok, I'll give it a try in the next couple of days when there is
some free time available on the machine.
-Mark
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org |
|
| Back to top |
|
|
Bruce Momjian
*nix forums Guru Wannabe
Joined: 02 Mar 2005
Posts: 287
|
| Posted: Tue Mar 15, 2005 11:46 pm Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
Mark Rae wrote:
| Quote: | On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote:
Be careful assuming that. DB benchmarks are hard to do in a general
sense. His results probably indicate a general trend, but you should
test your application yourself to get a real result. His pattern of SQL
queries might be very different from yours.
Very true.
You may have noticed that I had a very low query rate of 5.8 queries
per second, because some of the queries have 12 tables to join and
take about 20s to run. This tends to work in postgres' favour.
If you have lots have simple queries, it will be better for mysql
and the break even point will be higher.
Also, while on the subject of scaling. I had the opportunity
to try postgres on a 16CPU Altix and couldn't get it to scale
more than about 4x, whereas Oracle got up to about 12x faster
I assume this is because of the NUMA architecture. I was also
told that Oracle had made no special optimizations to accomodate it.
My guess is that because postgres allocates all its shared
buffers as a contiguous chunk, it puts all the load on one
memory bank.
Oracle on the other hand, seems to use lots of smaller regions
which would probably be spread throughout the physical memory.
Perhaps one of the developers could comment on how difficult
it would be to change the shared buffer handling to use multiple
segments. As I'd definitely be willing to give it a go.
|
We have had some major SMP improvements in current CVS. Were you
testing that or 8.0.X?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
|
Bruce Momjian
*nix forums Guru Wannabe
Joined: 02 Mar 2005
Posts: 287
|
| Posted: Wed Mar 16, 2005 12:00 am Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
Mark Rae wrote:
| Quote: | On Tue, Mar 15, 2005 at 06:46:50PM -0500, Bruce Momjian wrote:
Mark Rae wrote:
Also, while on the subject of scaling. I had the opportunity
to try postgres on a 16CPU Altix and couldn't get it to scale
more than about 4x, whereas Oracle got up to about 12x faster
We have had some major SMP improvements in current CVS. Were you
testing that or 8.0.X?
It tried it with 8.0.0rc3, and had previously tried a 7.4 version
|
Oh, you have to try CVS HEAD or a nightly snapshot. Tom made a major
change that allows scaling in SMP environments.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match |
|
| Back to top |
|
|
Doug McNaught
*nix forums Guru Wannabe
Joined: 16 Mar 2005
Posts: 215
|
| Posted: Wed Mar 16, 2005 1:30 pm Post subject:
Re: prelimiary performance comparison pgsql vs mysql
|
|
|
"Joshua D. Drake" <jd@commandprompt.com> writes:
| Quote: | Also, a 32-bit machine can only hold so much RAM. If I'm correct, there
are ways to address more memory than that on a 32 bit machine, but I
wonder at what cost? In other words, is it a good idea to address more
than 4GB on a 32 bit machine? If not, is it a reasonable choice to
invest in 64 bit if you want >4GB of RAM? Or are you better off just
spending the money on RAID and staying at 4GB?
It entirely depends on the database but not that the 32bit limit of 4GB
is per CPU. So if you have 4 CPUs you can have 16GB of ram.
|
It's actually per-process, not per-CPU. The x86 ISA only has 32-bit
address registers, so a process can only "see" 4GB max. The PAE
extensions that came in with the PPro allow for more address bits in
the page tables, so each process sees a different subset of a larger
pool of physical RAM.
The implication of this for PostgreSQL on x86 is that each backend has
a maximum of 4GB (actually, usually more like 3 to allow for kernel
address space) that must include shared buffers, server code and data,
and memory used for sorting etc.
On 64-bit platforms, the 4GB address space limitation disappears, and
a single backend could use 20GB for a sort, if the memory was
available and the administrator allowed it.
| Quote: | However, you should be running Opterons anyway.
|
Yup. :)
-Doug
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 8:20 am | All times are GMT
|
|
Loans | Homeowner Loans | Soul Calibur III Mp3 | Credit Cards | Loans
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
PostgreSQL
