Re: Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)

On Wed, 2007-12-05 at 00:13 -0800, Robert Bernabe wrote:
> Hi All,
> I've been tasked to evaluate PG as a possible replacement of our
> MS SQL 2000 solution. Our solution is 100% stored procedure/function
> centric. It's a report generation system whose sole task is to produce
> text files filled with processed data that is post-processed by a
> secondary system. Basically options are selected via a web interface
> and all these parameters are passed unto the stored procedure and then
> the stored procedure would run and in the process call other stored
> procedures until eventually a single formatted text file is produced.
> I decided on Fedora Core 7 and the 8.3 Beta release of Enterprise
> DB PostgreSQL. I decided to port 1 stored procedure plus it's several
> support stored procedures into pl/pgsql from T-SQL and compare the
> performance by measuring how long each system takes to produce the
> text file. For this test, the output to the text file was discarded
> and the stored procedure/function would end once the final temporary
> table is filled with the information that is eventually dumped into
> the text file.
>
> Windows 2000 Professional + MSDE (/MS SQL) Box vs. FC7 +
> EnterpriseDB PG Box
>
> Note that both boxes have EXACTLY the same hardware (not VMWARE or
> anything)
> AMD X2 3800
> 2 G RAM DDR 400
> 80 G Barracuda Sata
>
> The data was copied to the Linux box and checked lightly for
> consistency versus the windows box (number of tables / columns and
> records) and they all match. After data transfer to the Linux Box, I
> ran REINDEX and ANALYZE.
>
> For the actual run the following tables were used and I'm displaying
> the results of analyze.
>
> INFO: analyzing "public.AreaDefn"
> INFO: "AreaDefn": scanned 15 of 15 pages, containing 2293 live rows
> and 0 dead rows; 2293 rows in sample, 2293 estimated total rows
> INFO: analyzing "public.AreaDefn2"
> INFO: "AreaDefn2": scanned 30 of 30 pages, containing 3439 live rows
> and 0 dead rows; 3000 rows in sample, 3439 estimated total rows
> INFO: analyzing "public.Areas"
> INFO: "Areas": scanned 2 of 2 pages, containing 164 live rows and 0
> dead rows; 164 rows in sample, 164 estimated total rows
> INFO: analyzing "public.Brands"
> INFO: "Brands": scanned 1 of 1 pages, containing 11 live rows and 0
> dead rows; 11 rows in sample, 11 estimated total rows
> INFO: analyzing "public.Categories"
> INFO: "Categories": scanned 1 of 1 pages, containing 26 live rows and
> 0 dead rows; 26 rows in sample, 26 estimated total rows
> INFO: analyzing "public.CategoryDefn"
> INFO: "CategoryDefn": scanned 1 of 1 pages, containing 133 live rows
> and 0 dead rows; 133 rows in sample, 133 estimated total rows
> INFO: analyzing "public.CategoryDefn2"
> INFO: "CategoryDefn2": scanned 2 of 2 pages, containing 211 live rows
> and 0 dead rows; 211 rows in sample, 211 estimated total rows
> INFO: analyzing "public.CategorySets"
> INFO: "CategorySets": scanned 1 of 1 pages, containing 3 live rows
> and 0 dead rows; 3 rows in sample, 3 estimated total rows
> INFO: analyzing "public.CATemplateGroup"
> INFO: analyzing "public.Channels"
> INFO: "Channels": scanned 1 of 1 pages, containing 7 live rows and 0
> dead rows; 7 rows in sample, 7 estimated total rows
> INFO: analyzing "public.ClientCodes"
> INFO: analyzing "public.Clients"
> INFO: "Clients": scanned 7 of 7 pages, containing 366 live rows and 0
> dead rows; 366 rows in sample, 366 estimated total rows
> INFO: analyzing "public.Customers"
> INFO: "Customers": scanned 2 of 2 pages, containing 129 live rows and
> 0 dead rows; 129 rows in sample, 129 estimated total rows
> NFO: analyzing "public.Databases"
> INFO: "Databases": scanned 1 of 1 pages, containing 1 live rows and 0
> dead rows; 1 rows in sample, 1 estimated total rows
> INFO: analyzing "public.DataSources"
> INFO: "DataSources": scanned 1 of 1 pages, containing 8 live rows and
> 0 dead rows; 8 rows in sample, 8 estimated total rows
> INFO: analyzing "public.DateToWeekConversion"
> INFO: "DateToWeekConversion": scanned 4 of 4 pages, containing 371
> live rows and 0 dead rows; 371 rows in sample, 371 estimated total
> rows
> INFO: analyzing "public.Divisions"
> INFO: "Divisions": scanned 1 of 1 pages, containing 1 live rows and 0
> dead rows; 1 rows in sample, 1 estimated total rows
> INFO: analyzing "public.MetricTable"
> INFO: "MetricTable": scanned 1 of 1 pages, containing 48 live rows
> and 0 dead rows; 48 rows in sample, 48 estimated total rows
> INFO: analyzing "public.Offtake"
> INFO: "Offtake": scanned 3000 of 13824 pages, containing 141000 live
> rows and 0 dead rows; 3000 rows in sample, 649728 estimated total rows
> INFO: analyzing "public.SKUs"
> INFO: "SKUs": scanned 3 of 3 pages, containing 73 live rows and 0
> dead rows; 73 rows in sample, 73 estimated total rows
> INFO: analyzing "public.SMSDefaults"
> INFO: "SMSDefaults": scanned 1 of 1 pages, containing 43 live rows
> and 0 dead rows; 43 rows in sample, 43 estimated total rows
> INFO: analyzing "public.StandardPeriods"
> INFO: "StandardPeriods": scanned 1 of 1 pages, containing 8 live rows
> and 0 dead rows; 8 rows in sample, 8 estimated total rows
> INFO: analyzing "public.StandardUnits"
> INFO: "StandardUnits": scanned 1 of 1 pages, containing 9 live rows
> and 0 dead rows; 9 rows in sample, 9 estimated total rows
> INFO: analyzing "public.SubDataSources"
> INFO: "SubDataSources": scanned 0 of 0 pages, containing 0 live rows
> and 0 dead rows; 0 rows in sample, 0 estimated total rows
> INFO: analyzing "public.VolumeUnitDefn"
> INFO: "VolumeUnitDefn": scanned 0 of 0 pages, containing 0 live rows
> and 0 dead rows; 0 rows in sample, 0 estimated total rows
> INFO: analyzing "public.VolumeUnits"
> INFO: "VolumeUnits": scanned 0 of 0 pages, containing 0 live rows and
> 0 dead rows; 0 rows in sample, 0 estimated total rows
>
> as you can see the biggest one only has 600k records.
>
> Here are the settings used for postgresql.conf ( will just list those
> that were modified)
> #---------------------------------------------------------------------------
> # RESOURCE USAGE (except WAL)
> #---------------------------------------------------------------------------
>
> # - Memory -
> #shared_buffers = 32MB
> shared_buffers = 128MB # min 128kB or max_connections*16kB
> # (change requires restart)
> #temp_buffers = 8MB # min 800kB
> temp_buffers = 32MB
> #max_prepared_transactions = 5 # can be 0 or more
> # (change requires restart)
> #max_prepared_transactions = 20
> # Note: increasing max_prepared_transactions costs ~600 bytes of
> shared memory
> # per transaction slot, plus lock space (see
> max_locks_per_transaction).
> #work_mem = 1MB # min 64kB
> work_mem = 2MB
> #maintenance_work_mem = 16MB # min 1MB
> maintenance_work_mem = 32MB
> #max_stack_depth = 2MB # min 100kB
>
>
> Everything else was kept as is (given by the installer).
>
> /etc/sysctl.conf settings below :
> kernel.shmmax = 1536000000
> kernel.msgmni = 1024
> fs.file-max = 8192
> kernel.sem = "250 32000 32 1024"
>
> The main stored function has approximately 1200 lines of pl/pgsql
> code. While it is running it calls 7 other support stored functions
> plus a small view.
>
> The test basically was run two ways :
>
> a) for the linux box, we used PG Admin III to run the core stored
> function and in the windows box we used query analyzer.
>
> b) Created a small vb program that just calls the stored function for
> both boxes.
>
> Since I'm a total newbie in PG, I was expecting dismal results in
> the initial run since our port of the code would not be optimized for
> PG and sure enough I got them.
>
> Windows 2k Pro + MSDE - 4 seconds
> FC7 + postgresql-8.3-beta3 - 77 seconds
>
> thinking that maybe the GUI of FC7 is hampering the load, I
> decided to run it again using runlevel 3. The result was approximately
> a 1-2 % gain on the FC7 but that is insignificant compared to the
> discrepancy I'm measuring so I decided to just use the w/GUI results.
> We noticed that the CPU for the linux box was tapped out (at least one
> of the cores) nearly 100% for the duration of the process. There was
> plenty of ram available and there was little to no disk access during
> the entire run.
>
> I decided to further normalize the test and make the OS constant.
>
> Windows 2k Pro + MSDE - 4 seconds
> Windows 2k Pro + postgresql-8.3-beta3 - 54 seconds
>
> Turns out that for our code, running PG in windows is faster
> significantly. This was a surprise coming from all the research on the
> internet but it does jive with the results of this guy :
>
> http://channel9.msdn.com/ShowPost.aspx?PostID=179064
>
> Note that this guy used MS SQL 2005. Which we have proved to be 2-3
> times slower than MS SQL 2000 and hence our research into other
> options. :)
>
> Anyways I'd like to break up this request/begging for help into two
> parts.
>
> 1) First would be settings of postgresql.conf. Did I do it correctly?
> The sample data is so small....I based my settings on the
> recommendations researched for data centers.
>
> 2) Code optimization which I plan to start in another email thread
> since the discussions there would be more detailed.
>
> Would it also make sense to optimize (as far as possible) everything
> (including the code) for windows first? The target here would be a
> linux OS but since the discrepancy is so big...the unified Windows OS
> might be a good place to start for now.
>
> Many Thanks in advance.
>
> Regards

Hi Robert,

Assuming that you've transferred across all relevant indices, the
biggest gotcha I've found from porting stored procedures is forgetting
to mark them STABLE or IMMUTABLE where relevant (see
http://www.postgresql.org/docs/8.3/static/sql-createfunction.html for
more details). Without these function markers, PostgreSQL assumes that
the functions are VOLATILE which severely restricts their ability to be
optimised by the planner.

BTW you mention both EnterpriseDB PostgreSQL 8.3 beta and just
PostgreSQL 8.3 beta in the text above. Both of these are different -
which one are you actually using?

Kind regards,

Mark.

--
ILande - Open Source Consultancy
http://www.ilande.co.uk