Vantage 8.03 conversion to Epicor 9/9.05 performance issues (lar

Yahoo Archive
1
We had a similar issue and found that if you did the conversion on a
physical box that it cut the time down to a fraction of what it took on
a virtual box.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of bob.herold
Sent: Wednesday, August 25, 2010 11:04 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Vantage 8.03 conversion to Epicor 9/9.05 performance
issues (large database)





I am in the process of converting my SQL server based Vantage 8.03.409A
database to Epicor 9.04.506b3 and am experiencing performance problems.
The conversion is currently running 13 to 14 days. I am wondering if
anyone else has had a similar experience. I am running the conversion on
what I would call intermediate hardware. I don't want to make a new
server purchase prior to seeing what Epicor 9 or 9.05 looks like so I am
willing to have the conversion run a little longer than it should on new
server equipment, but 13 days seems unreasonable. I am hoping that my
current Progress settings can be tweaked to improve the conversion time.

Our production database is currently 108 GB. We went live on Vantage
8.03.403C in March 2008.

Here are the upgrade systems hardware setup and software settings:

The physical system is a Dell Precision T7500 running VMware ESX 4.1. It
is configured with dual 4 core Intel Xeon processors running at 2.53
GHz. The system has two 300 GB 7200 RPM SAS hard drives and 28 GB of
memory.

The virtual system which has Epicor 9 installed is configured as a
Windows 2008 Enterprise R2 system. I setup the system with 4 processors,
22 GB of memory and created C: and E: drives to split the database
files. The initial conversion is going to run against SQL Server 2005 32
bit. All available patches and hot fixes have been applied.

The Progress settings are DB buffers set at 100000, lock table entries
set at 409600 and spin locks at 80000. I've tried running the conversion
with the Progress database in Read Only mode but it actually seemed to
lengthen the conversion time and Epicor support advised against it.

There is one good aspect to the conversion, it finishes without any
errors and initial testing of Epicor 9 looks pretty clean.

Bob





[Non-text portions of this message have been removed]
2
I am in the process of converting my SQL server based Vantage 8.03.409A database to Epicor 9.04.506b3 and am experiencing performance problems. The conversion is currently running 13 to 14 days. I am wondering if anyone else has had a similar experience. I am running the conversion on what I would call intermediate hardware. I don't want to make a new server purchase prior to seeing what Epicor 9 or 9.05 looks like so I am willing to have the conversion run a little longer than it should on new server equipment, but 13 days seems unreasonable. I am hoping that my current Progress settings can be tweaked to improve the conversion time.

Our production database is currently 108 GB. We went live on Vantage 8.03.403C in March 2008.

Here are the upgrade systems hardware setup and software settings:

The physical system is a Dell Precision T7500 running VMware ESX 4.1. It is configured with dual 4 core Intel Xeon processors running at 2.53 GHz. The system has two 300 GB 7200 RPM SAS hard drives and 28 GB of memory.

The virtual system which has Epicor 9 installed is configured as a Windows 2008 Enterprise R2 system. I setup the system with 4 processors, 22 GB of memory and created C: and E: drives to split the database files. The initial conversion is going to run against SQL Server 2005 32 bit. All available patches and hot fixes have been applied.

The Progress settings are DB buffers set at 100000, lock table entries set at 409600 and spin locks at 80000. I've tried running the conversion with the Progress database in Read Only mode but it actually seemed to lengthen the conversion time and Epicor support advised against it.

There is one good aspect to the conversion, it finishes without any errors and initial testing of Epicor 9 looks pretty clean.

Bob
3
My first thought is do you really need to convert all that data? 108 GB isn't chump change. Why so large, lots of customers, parts, quotes, orders, etc? All that fat is going to follow you and slow down E9. Is leaving 8.03 up as a legacy system for reference and doing a fresh install of E9 an option (that's what we're doing but for a different reason)? Import what you need and leave the rest behind.

Ok so if you must convert the data, your disk I/O is absolutely killing you! Ideally on SQL server you want enough memory to load the entire DB into memory. With only 22 GB allocated, obviously that's not happening for you, so disk I/O will be extensive. While expensive, according to the Dell website, that model supports max memory of 192 GB. Feel like opening your wallet?

Tell us a bit more about your configuration please. Are 8.03 and 9.04 both virtual machines on the same physical machine or separate physical boxes? If 8.03 is a separate box, why are you virtualizing SQL server in the first place? Also, why only 4 cores to the virtual guest? I'm less familair with VMware than Hyper-V; does VMware limit you to 4 logical CPUs per guest like Hyper-V?

You only mentioned two hard drives, are these in a RAID array, or presented as separate logical drives? Did you pass the VM disks through to a mount point on the host, or are they Virtual Hard Disks sitting on an array/logical partition? Are the canonical C: and E: you mentioned separate VHD files on separate physical drives? Need some more detail please.

Load up PERFMON during a conversion and Look at the counters for:
Physical Disk - % Disk TIme
Avg. Disk Queue Length
Avg. Disk sec/Read
Avg. Disk sec/Write
Disk Reads/Sec
Disk Reads/Write.

Sql Server specific counters are:
SqlServer:Buffer Manager - Buffer Cache hit ratio
SqlServer:Buffer Manager - Page Life Expectancy
SqlServer:Memory Manager - Memory Grants Pending

You'll also probably want to look at some system counters:
System - Processor Queue Length
Paging File - % Usage
Memory - Available MBytes

If you can record several hours, a day, two days, or whatever of the conversion, then open the CSV log file in Excel and summarize the results for each counter using Average, Median, Min, Max and Std Deviation. Post them here or shoot me an email (jallmond@...). With only two drives, I would expect your disk queue length to be absolutely through the roof and to the moon!

If you follow the SQL Server Tuning Guide from Epicor, it's possible to turn off the Progress DB side for some added performance. In my experience, playing with the Progress numbers is a waste of time when using SQL server. Your mileage may vary of course.

SQL server optimization covers a lot of ground, but essentially comes down to disk I/O. Does your SQL server service account have permission to lock pages in memory? If not, ouch, that's hurting you too. Memory problems can lead to more (unnecessary) disk activity.

Is hyper-threading enabled? Epicor will tell you to turn it off (which in general is probably a good idea), but if your overall processor utilization is low, SQL server can benefit from the additional worker processes. However, I would expect during the conversion that disk processes are the name of the game. If your CPUs run "hot" then disable HT. With 8 (or 4 if you must stay virtual) cores at your disposal, you should break TempDB into separate files of equal size, one for each core. Equal is important here or no benefit. SQL server will fork a disk process for each TempDB file, so the more you can keep each core busy the better.

With only two hard drives, you're far from an optimal storage deployment for SQL server. Ideally use two drives in a RAID 1 array for the OS partition. You want two separate 4 drive RAID 10 arrays for your Mfgsys.MDF data file and .LDF log file. Do the same with TempDB data and logs. Even better if they can have their own array. Solid State Drives are your friend for the .MDF file, but use conventional rotating media for log files as they are written sequentially. RAID 10 requires 4 drives minimum, and additional spindles are the name of the game here. What about the system paging file, where does that live? Get it off the boot drive and onto an array. Preferably one with the data file over a log array, or at least the same controller as the data array. This is an optimal and expensive setup, but do your best to get as close as you can.

Further disk optimizations include using a 64 KB allocation size for SQL server paritions, with aligned disk partitions and an integer divisible disk partition offset to RAID stripe size ratio. With Server 2008 and up you're likely ok. The default offset is 1024 KB, which is compatible with most popular RAID controller stripes. Server 2003 partitions start after the hidden 63 sectors times 512 byte sectors yields 31.5 KB, which is not so much integer divisible.

I know I covered a lot, but hope that helps,
Jared

--- In vantage@yahoogroups.com, "bob.herold" <bherold@...> wrote:
>
> I am in the process of converting my SQL server based Vantage 8.03.409A database to Epicor 9.04.506b3 and am experiencing performance problems. The conversion is currently running 13 to 14 days. I am wondering if anyone else has had a similar experience. I am running the conversion on what I would call intermediate hardware. I don't want to make a new server purchase prior to seeing what Epicor 9 or 9.05 looks like so I am willing to have the conversion run a little longer than it should on new server equipment, but 13 days seems unreasonable. I am hoping that my current Progress settings can be tweaked to improve the conversion time.
>
> Our production database is currently 108 GB. We went live on Vantage 8.03.403C in March 2008.
>
> Here are the upgrade systems hardware setup and software settings:
>
> The physical system is a Dell Precision T7500 running VMware ESX 4.1. It is configured with dual 4 core Intel Xeon processors running at 2.53 GHz. The system has two 300 GB 7200 RPM SAS hard drives and 28 GB of memory.
>
> The virtual system which has Epicor 9 installed is configured as a Windows 2008 Enterprise R2 system. I setup the system with 4 processors, 22 GB of memory and created C: and E: drives to split the database files. The initial conversion is going to run against SQL Server 2005 32 bit. All available patches and hot fixes have been applied.
>
> The Progress settings are DB buffers set at 100000, lock table entries set at 409600 and spin locks at 80000. I've tried running the conversion with the Progress database in Read Only mode but it actually seemed to lengthen the conversion time and Epicor support advised against it.
>
> There is one good aspect to the conversion, it finishes without any errors and initial testing of Epicor 9 looks pretty clean.
>
> Bob
4
Here's a weekend .sql script we perform to:

Rebuild Part Full Text Catalog (deleted the others)
Rebuild Indexes
Shrink

The first time we ran it, it reduced the database size by 25%, suggesting fragmented indexes can take a lot of room. As always, back up the database. And you might try it on Test/Pilot first.

Also change the recovery model to "Simple" to reduce the log overhead. Change the Live database back to "Full" when done.

And we're thinking of converting 9.04 SQL to OpenEdge 9.05. Don't like the hardware specs for SQL 9.05.

USE Epicor904
EXEC sp_fulltext_table 'part', 'drop'
EXEC sp_fulltext_catalog 'part', 'drop'
EXEC sp_fulltext_catalog 'part', 'create', 'e:\fulltextcatalogs\part'
EXEC sp_fulltext_table 'part', 'create', 'part', 'idxFullText'
EXEC sp_fulltext_column 'part', 'PartDescription', 'add'
EXEC sp_fulltext_catalog 'part', 'start_full'

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
dbcc shrinkdatabase(Epicor904,10)
dbcc checkdb(Epicor904)


--- In vantage@yahoogroups.com, "bob.herold" <bherold@...> wrote:
>
> I am in the process of converting my SQL server based Vantage 8.03.409A database to Epicor 9.04.506b3 and am experiencing performance problems. The conversion is currently running 13 to 14 days. I am wondering if anyone else has had a similar experience. I am running the conversion on what I would call intermediate hardware. I don't want to make a new server purchase prior to seeing what Epicor 9 or 9.05 looks like so I am willing to have the conversion run a little longer than it should on new server equipment, but 13 days seems unreasonable. I am hoping that my current Progress settings can be tweaked to improve the conversion time.
>
> Our production database is currently 108 GB. We went live on Vantage 8.03.403C in March 2008.
>
> Here are the upgrade systems hardware setup and software settings:
>
> The physical system is a Dell Precision T7500 running VMware ESX 4.1. It is configured with dual 4 core Intel Xeon processors running at 2.53 GHz. The system has two 300 GB 7200 RPM SAS hard drives and 28 GB of memory.
>
> The virtual system which has Epicor 9 installed is configured as a Windows 2008 Enterprise R2 system. I setup the system with 4 processors, 22 GB of memory and created C: and E: drives to split the database files. The initial conversion is going to run against SQL Server 2005 32 bit. All available patches and hot fixes have been applied.
>
> The Progress settings are DB buffers set at 100000, lock table entries set at 409600 and spin locks at 80000. I've tried running the conversion with the Progress database in Read Only mode but it actually seemed to lengthen the conversion time and Epicor support advised against it.
>
> There is one good aspect to the conversion, it finishes without any errors and initial testing of Epicor 9 looks pretty clean.
>
> Bob
>