Обсуждение: Disappointing performance in db migrated from MS SQL Server
Hello all. I am in the midst of porting a large web application from a MS SQL Server backend to PostgreSQL. The migration work is basically complete, and we're at the testing and optimization phase of the project. The results so far have been disappointing, with Postgres performing queries in about the same time as SQL Server even though Postgres is running on a dedicated box with about 4 times the clock speed of the SQL Server box. For a chart of my results, please see http://leonout.com/pggraph.pdf for a graph of some test results. Here are the specs of the systems: SQL Server Dell PowerEdge 2400 Windows 2000 Advanced Server Dual Pentium III 667 2 GB Registered PC133 SDRAM MS SQL Server 2000 SP2 - shared database (although to be fair, this app is by far the heaviest) RAID 1 for system / RAID 5 for data (10k RPM Ultra160 SCSI drives) PostgreSQL Dell PowerEdge 2650 RedHat Enterprise Linux 3.1 Dual Xeon 3.06 GHz (Hyperthreading currently disabled) 4 GB DDR SDRAM PostgreSQL 7.4 - dedicated to this app, with no other apps running on system RAID 5 (15k RPM Ultra160 SCSI drives) The database is about 4.3 GB in size. My postgresql.conf is as follows: max_connections = 50 shared_buffers = 10000 # min 16, at least max_connections*2, 8KB each - default is 1000 sort_mem = 2000 # min 64, size in KB - default is 1024 (commented out) effective_cache_size = 250000 # typically 8KB each - default is 1000 (commented out) geqo = true lc_messages = 'en_US.UTF-8' # locale for system error message strings lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting I hope that someone can help with this. Thanks in advance for your help! Leon
On Thu, 12 Feb 2004, Leon Out wrote: > Hello all. I am in the midst of porting a large web application from a > MS SQL Server backend to PostgreSQL. The migration work is basically > complete, and we're at the testing and optimization phase of the > project. The results so far have been disappointing, with Postgres > performing queries in about the same time as SQL Server even though > Postgres is running on a dedicated box with about 4 times the clock > speed of the SQL Server box. For a chart of my results, please see > http://leonout.com/pggraph.pdf for a graph of some test results. A couple of things. One, CPU speed is about number 5 in the list of things that make a database fast. Drive subsystem (number of drivers, controller, RAID cache), memory speed, memory size, and proper database tuning are all significantly more important thatn the CPU speed. Our old server was a dual PIII-750 with 1.5 gig ram (PC133) and it ran about 85% as fast as our brand spanking new Dell 2650 dual 2800MHz box with 2 gig ram. They both had the same basic drive subsystem, by the way. Using a battery backed RAID controller (the lsi megaraid one, not the adaptect, as it's not very fast) made the biggest difference. With that thrown in we got about double the speed on the new box as the old one. Have you read the tuning docs on varlena? http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html It's a must read. > Here are the specs of the systems: > > SQL Server > Dell PowerEdge 2400 > Windows 2000 Advanced Server > Dual Pentium III 667 > 2 GB Registered PC133 SDRAM > MS SQL Server 2000 SP2 - shared database (although to be fair, this app > is by far the heaviest) > RAID 1 for system / RAID 5 for data (10k RPM Ultra160 SCSI drives) > > PostgreSQL > Dell PowerEdge 2650 > RedHat Enterprise Linux 3.1 > Dual Xeon 3.06 GHz (Hyperthreading currently disabled) > 4 GB DDR SDRAM > PostgreSQL 7.4 - dedicated to this app, with no other apps running on > system > RAID 5 (15k RPM Ultra160 SCSI drives) > > The database is about 4.3 GB in size. > > My postgresql.conf is as follows: > > max_connections = 50 > shared_buffers = 10000 # min 16, at least max_connections*2, > 8KB each - default is 1000 > sort_mem = 2000 # min 64, size in KB - default is 1024 > (commented out) > effective_cache_size = 250000 # typically 8KB each - default is 1000 > (commented out) I'm gonna guess that you could use a larger sort_mem (at least 8 meg, no more than 32 meg is usually a good range. With 4 gigs of ram, you can probably go to 64 or 128 meg if you only handle a hand full of clients at at time, but sort_mem is per sort, so be careful cranking it up too fast, as you'll throwh the server into a swap storm. I.e. setting sort_mem high is a foot gun. Your effective cache size should likely be at LEAST a setting that represents 2 gigs, maybe more. It's measured in blocks, so unless you've changed your block size from 8k, that would be: 250000 What are your query settings for random_page_cost, and cpu*cost settings? It's likely a good idea to drop your random page cost to close to 1, as with this much memory, most of your data will find itself in memory. 10000 is probably plenty for shared_buffers. You might try setting it higher to see if it helps, but I'm doubting it will. But more important, WHAT are you doing that's slow? Matching text, foreign keys, triggers, stored procedures? Use explain analyze on the the slow / mediocre queries and we can help a bit.
Leon Out wrote: > Hello all. I am in the midst of porting a large web application from a > MS SQL Server backend to PostgreSQL. The migration work is basically > complete, and we're at the testing and optimization phase of the > project. The results so far have been disappointing, with Postgres > performing queries in about the same time as SQL Server even though > Postgres is running on a dedicated box with about 4 times the clock > speed of the SQL Server box. For a chart of my results, please see > http://leonout.com/pggraph.pdf for a graph of some test results. My only guess is that the tests are I/O bound and therefore the faster CPU's aren't helping PostgreSQL. -- 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
It might be helpful to include a sample query that is running slower
than you expect, with the table structure, and the output of explain
{query}.
Gavin
Leon Out wrote:
> Hello all. I am in the midst of porting a large web application from a
> MS SQL Server backend to PostgreSQL. The migration work is basically
> complete, and we're at the testing and optimization phase of the
> project. The results so far have been disappointing, with Postgres
> performing queries in about the same time as SQL Server even though
> Postgres is running on a dedicated box with about 4 times the clock
> speed of the SQL Server box. For a chart of my results, please see
> http://leonout.com/pggraph.pdf for a graph of some test results.
>
> Here are the specs of the systems:
>
> SQL Server
> Dell PowerEdge 2400
> Windows 2000 Advanced Server
> Dual Pentium III 667
> 2 GB Registered PC133 SDRAM
> MS SQL Server 2000 SP2 - shared database (although to be fair, this
> app is by far the heaviest)
> RAID 1 for system / RAID 5 for data (10k RPM Ultra160 SCSI drives)
>
> PostgreSQL
> Dell PowerEdge 2650
> RedHat Enterprise Linux 3.1
> Dual Xeon 3.06 GHz (Hyperthreading currently disabled)
> 4 GB DDR SDRAM
> PostgreSQL 7.4 - dedicated to this app, with no other apps running on
> system
> RAID 5 (15k RPM Ultra160 SCSI drives)
>
> The database is about 4.3 GB in size.
>
> My postgresql.conf is as follows:
>
> max_connections = 50
> shared_buffers = 10000 # min 16, at least max_connections*2,
> 8KB each - default is 1000
> sort_mem = 2000 # min 64, size in KB - default is 1024
> (commented out)
> effective_cache_size = 250000 # typically 8KB each - default is 1000
> (commented out)
> geqo = true
>
> lc_messages = 'en_US.UTF-8' # locale for system error
> message strings
> lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
> lc_numeric = 'en_US.UTF-8' # locale for number formatting
> lc_time = 'en_US.UTF-8' # locale for time formatting
>
>
> I hope that someone can help with this. Thanks in advance for your help!
>
> Leon
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
Please run your largest (worst) queries using EXPLAIN ANALYZE and send in the results so we can see how the queries are being executed & optimized. On Feb 12, 2004, at 11:29 AM, Leon Out wrote: > Hello all. I am in the midst of porting a large web application from a > MS SQL Server backend to PostgreSQL. The migration work is basically > complete, and we're at the testing and optimization phase of the > project. The results so far have been disappointing, with Postgres > performing queries in about the same time as SQL Server even though > Postgres is running on a dedicated box with about 4 times the clock > speed of the SQL Server box. For a chart of my results, please see > http://leonout.com/pggraph.pdf for a graph of some test results. > -- PC Drew Manager, Dominet IBSN 1600 Broadway, Suite 400 Denver, CO 80202 Phone: 303-984-4727 x107 Cell: 720-841-4543 Fax: 303-984-4730 Email: drewpc@ibsncentral.com
Bruce, my bet is on the limited amount of shared memory. The setup as posted by Leon only shows 80 MB. On a 4 GB database, that's not all that much. Depending on what he's doing, this might be a bottleneck. I don't like the virtual memory strategy of Linux too much and would rather increase this to 1 - 2 GB for the Postgres DB - Specially since he's not running anything else on the machine and he has 4 GB to play with. On Thursday 12 February 2004 14:05, Bruce Momjian wrote: > Leon Out wrote: [snip] > > My only guess is that the tests are I/O bound and therefore the faster > CPU's aren't helping PostgreSQL.
Leon, > Hello all. I am in the midst of porting a large web application from a > MS SQL Server backend to PostgreSQL. The migration work is basically > complete, and we're at the testing and optimization phase of the > project. The results so far have been disappointing, with Postgres > performing queries in about the same time as SQL Server even though > Postgres is running on a dedicated box with about 4 times the clock > speed of the SQL Server box. For a chart of my results, please see > http://leonout.com/pggraph.pdf for a graph of some test results. Your settings look ok to start, but we'll probably want to tune them further. Can you post some details of the tests? Include: 1) the query 2) the EXPLAIN ANALYZE results of the query 3) Whether you ran the test as the only connection, or whether you tested multi-user load. The last is fairly important for a SQL Server vs. PostgreSQL test; SQL Server is basically a single-user-database, so like MySQL it appears very fast until you get a bunch o' users on it. Finally, for most queries the disk I/O and the RAM are more important than the CPU clock speed. From the looks of it, you upgraded the CPU + RAM, but did downgraded the disk array as far as database writes are concered; not a terrible effective way to gain performance on your hardware. -- -Josh Berkus Aglio Database Solutions San Francisco
On Thu, Feb 12, 2004 at 05:19:27PM -0500, Chris Ruprecht wrote: > what he's doing, this might be a bottleneck. I don't like the virtual memory > strategy of Linux too much and would rather increase this to 1 - 2 GB for the > Postgres DB - Specially since he's not running anything else on the machine > and he has 4 GB to play with. Have you ever had luck with 2G of shared memory? When I have tried that, the system is very fast initially, and gradually slows to molasses-like speed. My hypothesis is that the cache-lookup logic isn't that smart, and so is inefficient either when using the cache or when doing cache maintenance. A -- Andrew Sullivan
All, thanks for your suggestions. I've tweaked my configuration, and I think I've squeezed a little more performance out of the setup. I also tried running several tests simultaneously against postgres and SQL Server, and postgres did much better with the heavy load. My new settings are: max_connections = 50 shared_buffers = 120000 # min 16, at least max_connections*2, 8KB each - default is 1000 sort_mem = 8000 # min 64, size in KB - default is 1024 (commented out) effective_cache_size = 375000 # typically 8KB each - default is 1000 (commented out) random_page_cost = 1 # units are one sequential page fetch cost - default is 4 (commented out) geqo = true Josh, the disks in the new system should be substantially faster than the old. Both are Ultra160 SCSI RAID 5 arrays, but the new system has 15k RPM disks, as opposed to the 10k RPM disks in the old system. On Feb 12, 2004, at 3:26 PM, Josh Berkus wrote: > Leon, > >> Hello all. I am in the midst of porting a large web application from a >> MS SQL Server backend to PostgreSQL. The migration work is basically >> complete, and we're at the testing and optimization phase of the >> project. The results so far have been disappointing, with Postgres >> performing queries in about the same time as SQL Server even though >> Postgres is running on a dedicated box with about 4 times the clock >> speed of the SQL Server box. For a chart of my results, please see >> http://leonout.com/pggraph.pdf for a graph of some test results. > > Your settings look ok to start, but we'll probably want to tune them > further. > Can you post some details of the tests? Include: > > 1) the query > 2) the EXPLAIN ANALYZE results of the query > 3) Whether you ran the test as the only connection, or whether you > tested > multi-user load. > > The last is fairly important for a SQL Server vs. PostgreSQL test; SQL > Server > is basically a single-user-database, so like MySQL it appears very > fast until > you get a bunch o' users on it. > > Finally, for most queries the disk I/O and the RAM are more important > than the > CPU clock speed. From the looks of it, you upgraded the CPU + RAM, > but did > downgraded the disk array as far as database writes are concered; not a > terrible effective way to gain performance on your hardware. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco >
> Josh, the disks in the new system should be substantially faster than > the old. Both are Ultra160 SCSI RAID 5 arrays, but the new system has > 15k RPM disks, as opposed to the 10k RPM disks in the old system. Spindle speed does not correlate with 'throughput' in any easy way. What controllers are you using for these disks?
>>>>> "LO" == Leon Out <leon-lists@comvision.com> writes: LO> Josh, the disks in the new system should be substantially faster than LO> the old. Both are Ultra160 SCSI RAID 5 arrays, but the new system has LO> 15k RPM disks, as opposed to the 10k RPM disks in the old system. If you've got the time, try making your 5 disk array into a RAID10 plus one spare. I found that with that few disks, RAID10 was a better performer for an even mix of read/write to the DB. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
>>>>> "LO" == Leon Out <leon-lists@comvision.com> writes: LO> project. The results so far have been disappointing, with Postgres LO> performing queries in about the same time as SQL Server even though LO> Postgres is running on a dedicated box with about 4 times the clock LO> speed of the SQL Server box. For a chart of my results, please see LO> http://leonout.com/pggraph.pdf for a graph of some test results. Are you using transactions liberally? If you have large groups of inserts/updates, putting them inside transactions buys you a lot of improvement by batching the writes to the WAL. Also, increase your checkpoint_segments if you do a lot of writes. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On Fri, 13 Feb 2004 matt@ymogen.net wrote: > > Josh, the disks in the new system should be substantially faster than > > the old. Both are Ultra160 SCSI RAID 5 arrays, but the new system has > > 15k RPM disks, as opposed to the 10k RPM disks in the old system. > > Spindle speed does not correlate with 'throughput' in any easy way. What > controllers are you using for these disks? This is doubly so with a good RAID card with battery backed cache. I'd bet that 10k rpm drives on a cached array card will beat an otherwise equal setup with 15k rpm disks and no cache. I know that losing the cache slows my system down to a crawl (i.e. set it to write thru instead of write back.) comparitively speaking.