Progress 9.1D on Solild State Disks

[drewser001]

Hi all,

I have been working very hard in an attempt to migrate a production Progress 9.1D database with 600+ users from Ultra320 SCSI disks to newer solid state SAS drives, but have been running into performance problems.

The test server has 12GB DDR2 ram, Two dual core (or single core w/Hyper threading)Xeon cpus

The DB & BI are running on the same RAID 1 pair of spindles (Ultra320 SCSI, 15K drives)

The DB parameters are:
db buffer blocks = 190000
num lock table entries 707778
Spin Lock Retries = 50000
maximum beffers = 25
Servers = 207
message buffer size = 4096

BI parameters are:
buffers = 25
bi threshold = 3
delay writes = 3
cluster age = 60
BI block size = 256K
BI size = 25GB

The windows partition is set with the default 4K allocation unit, and the RAID 1 set has a 64K stripe size.

So far, the SCSI disks are outperforming the SSDs, but not by much. I expect better performance from these drives than what I am getting. The SSDs are Enterprise class drives and have been benchmark tested on the server. Benchmarks for the SSDs blow the SCSI disks out of the water, but running the app/db on them tells a different story.

What can I do to this setup to increase performance (other than separate the DB and BI onto different RAID sets, which has been tried, to some degree) ??

With a 256K block size for the BI file, would it be better to initialize a separate RAID 1 with a larger stripe size and setup the windows partition with a larger allocation unit size as well? Does this help performance at all?

OR, would I be better off with a smaller BI block size, since the SSDs are designed for smaller random read/write sizes (4k and 8k), where I don't suffer from Spindle/Seek latency?

 

[RealHeavyDude]

Don't have any experience with SSDs in a Progress environment, but - 9.1D is a NOGO.

You can't expect more than 10 years old software to work with the latest and greatest hardware technology without a fuzz. Furthermore 9.1D is not supported anymore for some years, limited in many aspects and last, but not least, buggy.

You don't mention the block size of your database. For performance reasons it should be equal or bigger than the file system block size. For stability reasons, as of 9.1D, it should be equal.

256K can't be the block size of the BI. IIRC 9.1D is limited to 8K - therefore I think you mean the cluster size of the BI, which is important when it comes to check pointing rather than shear writing of file system blocks. The BI block size should not be anything less than the maximum.

Separating the BI from the database is always a good thing. In an ideal world it would have its own RAID set.

You don't mention after image - you have it enabled, do you? If not than maybe there is nothing of any value stored in your database. If you already have it enabled then you need to put it on their own disks ...

Regarding SSD performance: AFAIK they are superior on read performance but not on write performance. Depending on the transaction load of your system you might not gain what you expect compared to conventional disk systems.

Heavy Regards, RealHeavyDude.

 

[MaximMonin]

I had experience with SSD as disk for temporary files directory on linux system.
There were better performance (I used "wait input/output" parameter for checking real load of the system). But we had a problem with free space on this disk so changed it is sas hard disk.

Anyway place bi/db and temporary files (-T) on different physical drives.

 

[TomBascom]

600 users and 9.1D? Are you crazy?

I really, really hope you have after-imaging enabled.

25GB of bi file? Why? What are you doing wrong that requires this much bi file? This sounds like a very serious coding error. How do you reconcile such a large bi file with a bi threshold of "3" (which seems rather unlikely).

Only 1 pair of disks? And 600 users? Do these users do any work?

Is this a commercial application or is it something homegrown?

I suspect that your parameter settings and configuration are not what you think they are. Trying to read the properties file and or pull the settings out of Exploder usually results in wrong answers. To see what the database really used for startup parameters examine the 50 or so lines in the .lg file immediately after the most recent db startup.

SSD disks should beat the pants off spinning rust. If it isn't then it might be that your controller is simply swamped.

You haven't mentioned the OS. That might be a factor as well. For SSDs you want TRIM support (Since this is some form of Windows I think that means you need at least Windows Server 2008).

You haven't described the tests that you are running to compare performance. It is also quite possible that the problem is that your tests don't test what you think that they test.

 

[drewser001]

I am going to answer your questions in order, so here goes:

RealHeavyDude:
Not all 600 users hit the DB at the same time, actual "concurrent" use is probably closer to 100-150 users. We are stuck with the DB because the application requires it (and no, it isn't home grown). We are planning a 9.1d -> 9.1e upgrade in the near future, however. I can't go into the details as to why we are stuck on this version, but we are (would need a separate thread to discuss all that...)

I stand corrected, the "cluster" size is 256K. With that said, should the RAID stripe size remain at 64K and the OS partition block size remain at 4K for a separate raid/partition for the BI file, or would that specific RAID set benefit from a 128K stripe and a larger partition block size?

The DB Block size is 4K.

As far as After-Image, the application vendor has never recommended it be turned on, and they were/are who has helped us maintain this DB for 7 years. I've read a few threads now in regards to the AI file and get the impression that the DB absolutely *MUST* have it, but we have never suffered any type of unrecoverable crash the entire time the DB has been running. At this point, I doubt we would start using AI, either. Also, the server, the DB and the disk configuration were all established by the Application vendor and Progress, not us. Aside from that, we make nightly DB backups and dump that off to tape.

The SSDs being used are designed for Enterprise use, with *Heavy* read/write transactions in mind. I have been working with the SSD vendor to validate the drives are capable of performing at or near spec in the hardware setup we currently have. And yes, the transaction load that we are comparing is the MRP process itself, which appears to be the heaviest transactional load the server endures. The benchmark tests that I performed were according to the SSD drive vendor, they even provided the config file for the benchmarking application.

 

[drewser001]

MaximMonon:

So, a different set of disks for (-T) temp files as well? How often are they written/read to? As I understand it from the Admin guide, temp files are used when queries (4GL/SQL) have a sort order requirement, so the system writes the data out to the temp file before it applies the sort. Other than that, I have no idea when the server uses temp files.

So far though, it does appear that "read" performance is better, large queries that hammer the disks do run faster on the SSDs. It is just the MRP load that confuses me. The disks should be able to handle more load, and according to the benchmark tests, are capable of handling more load. The problem is, I don't completely understand what all is going on during the MRP run. I do know this much, it is performing ALOT of record updates, nearly 3:1 versus Creates and Writes (nearly 13 million updates versus ~5 million Writes and ~4 million creates in a 27 hour period, and ~3 million BI writes). Record Reads are @ 460 million for the same time frame. Keep in mind, these values are for MRP running on a test server, there are no users in the DB, this is just MRP transaction counts only!

Oh, and MRP executes on a single CPU/core.

 

[cj_brandt]

1. Enable AI. I managed a 9.1D06 Progress db with over 1,000 concurrent users and we didn't see any delay in regular processing when AI was enabled. At night during our batch processing we saw a 5% slowdown or so.
2. Use the -t parameter to make the temp files visible in the -T location. Tom B. has an article about slow performance caused by temp IO - is your temp IO also going to the SSD drives ?
3. You could download the ATM benchmark test and run some tests on the old SCSI drives and compare that to the SSD drives. At least that would be a test involving Progress to see what the difference in performance could be with a large number of writes, not just a hardware vendor's test.
4. What does windows perfmon - or whatever monitoring tool you have - show for the disk queue while you are getting the poor performance from the SSD drives ?

 

[drewser001]

Tom,

I gave the reasons for 9.1d in my reply to RealHeavyDude. Nothing I can do about it. Same goes for using AI... Which makes me want to ask, what performance issues will I encounter if it was enabled? And yes, while I am aware of newer hardware/software, the server/app are what they are and we are looking at every possible angle of increasing performance on the existing platform to bridge the gap until we are able to upgrade to something newer and better...

Perhaps I misquoted the BI settings... I will include a snippet from the lg file for your review at the end of this post.

As for the BI file's size, there has always been confusion regarding what it does, the settings to use, etc. This due to confusing/conflicting statements from Progress and the App vendor. Here is some background... Due to a corrupt record from years ago, we were unable to perform a dump/load until recently (needed serious Progress help, which was unavailable in years prior, as we were told there was no way to deal with the issue). Long story short, the BI file was 75GB before we recently & successfully performed a Dump/Load/Truncate. We grew it back to a fraction of it's last size (the DB is 50GB) in order to avoid slowness durning the time the DB engine grows the files "on-the-fly". Neither Progress nor the App vendor could tell us how long this "slowness" period would be, and our prior experiences it lasted for days, which would have been unacceptible for us, anyway, moving on...

Yes, the users do work on the system intermittently, not constantly. And yes, this is a commercial application, not home grown.

You may be correct in your thinking in regards to the controller. It is older hardware and the controller is designed for spindle disks, not SSDs. As for TRIM, that is not an issue for these SSDs, as they are designed for high read/write transactions in small block sizes (4K/8K), that is what they are designed for. But yes, the OS is Windows Enterprise Server 2003, which again was not designed with SSDs in mind. TRIM has more to do with when windows deletes a file and the SSD is not aware the space needs to be erased. Since we are talking Database, TRIM never truly comes into play, (except for Temp files, perhaps). Current SSD offerings use overprovisioned space to overcome TRIM issues where possible. If you are skeptical about SSDs and enterprise databases, take a look at Fusion-IO (unfortunately, they are NOT the SSD vendor, their drives only work in 64-bit servers and OS'es).

As for the initial tests the SSDs were put through, those were done as per the SSD vendors specification. I have been in close contact with their support group while working with these drives and have complete faith in the tests they have specified. In fact, the recommended testing tool (IOmeter) was originally developed by Intel.



As stated, here is the lg file snippet:
<lg file snippet>
11:42:58 BROKER 0: Database Type (-dt): PROGRESS. (4236)
11:42:58 BROKER 0: Force Access (-F): Not Enabled. (4237)
11:42:58 BROKER 0: Direct I/O (-directio): Not Enabled. (4238)
11:42:58 BROKER 0: Number of Database Buffers (-B): 190000. (4239)
11:42:58 BROKER 0: Maximum private buffers per user (-Bpmax): 64. (9422)
11:42:58 BROKER 0: Excess Shared Memory Size (-Mxs): 16631. (4240)
11:42:58 BROKER 0: The shared memory segment is not locked in memory. (10014)
11:42:58 BROKER 0: Current Size of Lock Table (-L): 707808. (4241)
11:42:58 BROKER 0: Hash Table Entries (-hash): 50207. (4242)
11:42:58 BROKER 0: Current Spin Lock Tries (-spin): 0. (4243)
11:42:58 BROKER 0: Number of Semaphore Sets (-semsets): 1. (6526)
11:42:58 BROKER 0: Crash Recovery (-i): Enabled. (4244)
11:42:58 BROKER 0: Database Blocksize (-blocksize): 4096. (6573)
11:42:58 BROKER 0: Delay of Before-Image Flush (-Mf): 3. (4245)
11:42:58 BROKER 0: Before-Image File I/O (-r -R): Reliable. (4247)
11:42:58 BROKER 0: Before-Image Truncate Interval (-G): 60. (4249)
11:42:58 BROKER 0: Before-Image Cluster Size: 268419072. (4250)
11:42:58 BROKER 0: Before-Image Block Size: 8192. (4251)
11:42:58 BROKER 0: Number of Before-Image Buffers (-bibufs): 5. (4252)
11:42:58 BROKER 0: BI File Threshold size (-bithold): 0.0 Bytes. (9238)
11:42:58 BROKER 0: BI File Threshold Stall (-bistall): Disabled. (6552)
11:42:58 BROKER 0: After-Image Stall (-aistall): Not Enabled. (4254)
11:42:58 BROKER 0: Number of After-Image Buffers (-aibufs): 5. (4256)
11:42:58 BROKER 0: Storage object cache size (-omsize): 1024 (8527)
11:42:58 BROKER 0: Maximum Number of Clients Per Server (-Ma): 1. (4257)
11:42:58 BROKER 0: Maximum Number of Servers (-Mn): 229. (4258)
11:42:58 BROKER 0: Minimum Clients Per Server (-Mi): 1. (4259)
11:42:58 BROKER 0: Maximum Number of Users (-n): 846. (4260)
11:42:58 BROKER 0: Host Name (-H): <removed>. (4261)
11:42:58 BROKER 0: Service Name (-S): <removed>. (4262)
11:42:58 BROKER 0: Network Type (-N): <removed>. (4263)
11:42:58 BROKER 0: Character Set (-cpinternal): ISO8859-1. (4264)
11:42:58 BROKER 0: Parameter File: Not Enabled. (4282)
11:42:58 BROKER 0: Maximum Servers Per Broker (-Mpb): 4. (5647)
11:42:58 BROKER 0: Minimum Port for Auto Servers (-minport): 3000. (5648)
11:42:58 BROKER 0: Maximum Port for Auto Servers (-maxport): 5000. (5649)
11:42:58 BROKER 0: This broker supports SQL server groups only. (8864)
11:42:58 BROKER 0: Large database file access has been enabled. (9426)

 

[drewser001]

CJ,

I have been logging disk Q and read/write activity in Perfmon for the last 48 hours (yes, our MRP takes that LONG, even on SCSI disks!). I would assume that everything is going to the SSDs right now, this is how it was with the SCSI disks as well. Dumb question, where do I find the -T setting?

At this very moment on SSDs, Avg Queue length is 0.033.

Question:
the "Employ Direct I/O" checkbox is unchecked... The helpfile states:

"Select the Employ Direct I/O check box to enable the Direct I/O property, which opens all files in unbuffered mode. Direct I/O enables the database to use an I/O technique that bypasses the operating system buffer pool and transfers data directly from a buffer to disk.
This technique has several advantages over buffered reads and writes, such as avoiding the overhead of maintaining the operating system buffer pool and eliminating competition for operating system buffers between the database and other programs. The operating system buffer-pool algorithms are designed for efficient sequential file access; the Progress buffer-pool algorithms are more efficient for access to a Progress database.
(The Employ Direct I/O check box is cleared by default.)"

Since I am using SSDs, would this benefit me at all to enable this? Is the DB engine tripping over the OS?

 

[cj_brandt]

I don't think directio helps when you have 9.1d and windows - but you could download the atm benchmark tool from PSDN and verify.
You don't have -spin set - fix that now. 11:42:58 BROKER 0: Current Spin Lock Tries (-spin): 0. (4243)

"Nothing I can do about it. Same goes for using AI... " Not true. You can enable AI for any vendor's database. I'm not walking in your shoes, but I'd SERIOUSLY question any vendor that doesn't help their clients get AI enabled. There are lots of Progress scripts you can find which handle the switching of AI files. If needed hire some help to get AI setup. If you watch these progress discussions long, you will find several people writing in with a need to recover their data and they didn't have AI enabled.

Does perfmon show increased throughput with the SSD drives ?

The -T parameter is a client parameter which states where the temp files will go. If it isn't defined the temp files go to %WRKDIR%. Look in %DLC%/installd.ini for %WRKDIR% location.

 

[drewser001]

Spin Lock Retries are set to 50,000 on the production server, apparently they are set to zero on the test server.

ok, where would I set the -T parameter, and how would it look if I set it to, say, a "T:\temp" folder?

 

[TomBascom]

Application partners are renowned for their ability to understand an industry and write an application that meets that industries needs. Which is probably why management bought this application. (Knowing which one it is might also be helpful... we might know something about tuning it.)

They are not well known for their ability to configure, manage or tune databases. You have implied that Progress has also had some involvement of some sort with your configuration. It does not sound (or look) like they provided anyone who was particularly qualified to work on database issues.

Take their advice on such matters at your peril.

You can run for 20 years without after-imaging. It only takes a fraction of a second to make that look like the worst non-decision you ever didn't make.

A BI file that is 25GB large is wrong. Something is not right with that picture.

I'm not at all skeptical about SSDs. I like them. They work very well. Although I doubt that it is the core issue here I think that TRIM support may be more useful to you than you think.

I am, however, definitely skeptical of any and all vendor claims.

I'm also familiar with and a fan of IOMeter. Perhaps you could describe your tests in a little more detail?

You could try -directio but don't expect much. It doesn't usually have any noticeable impact. If you get lucky please let us know. It would be a rare enough event to call for a special celebration.

Anyhow... it does not sound like you have an IO problem. Which would explain why SSDs don't make much difference.

But if you have been logging disk activity how much is there? How many IO ops/sec are you doing? Your disk queues are quite modest.

You should increase the number of -bibufs from 5 (which is miniscule) to something useful like 100 or 200.

-B is close to the best you'll get -- with v9 on Windows you cannot get more than 2GB and likely much less. But try pushing it up to 250,000 or even 500,000 if you can get it that high (you'll need to do some trial and error).

You should change your bi block size to 16 k.

Do you have a BIW running?

Are there any APWs running?

-spin 0? You have an Enterprise license and multiple CPUs. -spin is as close to a "silver bullet" as you will ever find in that situation. Set -spin 10000. Do NOT listen to any hogwash about setting it to X * NumCPUs.

You have SQL connections. Have you ever run UPDATE STATISTICS?

Are your users connecting using -H & -S? Is your MRP run running on the server or on a remote client? If it is on the server (it should be for best pperformance) then it ought not to be using -S.

Are there app servers used by this application or is it old style "fat client/server"?

Client connection parameters would also be interesting.

How is your database setup in terms of storage areas? (IOW what does the .st file look like?) My guess is that everything is in the schema area.

 

[drewser001]

Tom,

thankyou for your valuable input.

>>A BI file that is 25GB large is wrong. Something is not right with that picture.
What is a good BI size? How often should I truncate the BI and re-grow it? Keep in mind, our system turns to a slug when it grows a new BI file (they are 512MB each)

>>I'm also familiar with and a fan of IOMeter. Perhaps you could describe your tests in a little more detail?

>>But if you have been logging disk activity how much is there? How many IO ops/sec are you doing? Your disk queues are quite modest.
How would I capture IOPs via PerfMon?

>>You should increase the number of -bibufs from 5 (which is miniscule) to something useful like 100 or 200.
The production DB has BIbufs set to 25, will try 200 on the test server...

>>-B is close to the best you'll get -- with v9 on Windows you cannot get more than 2GB and likely much less.
>>But try pushing it up to 250,000 or even 500,000 if you can get it that high (you'll need to do some trial and error).
trying 250,000 now... (assuming this is the 190,000 setting currrently)

>>You should change your bi block size to 16 k.
FYI, the 8K setting is on the test db/server.
The production setting is 256K!
And this is one of my core questions... If the BI block size is 256K, and the cluster size is 25GB, what would be an optimum RAID/partition setup be? 256K BI block size seems to have helped the Server/DB performance (or was it the dump/load only, hard to say).

>>Do you have a BIW running?
Not sure where to find this at...

>>Are there any APWs running?
#APWs to start = 1
(Production server has #APWs to start = 8)

>>-spin 0? You have an Enterprise license and multiple CPUs.
>>-spin is as close to a "silver bullet" as you will ever find in that situation.
>>Set -spin 10000. Do NOT listen to any hogwash about setting it to X * NumCPUs.
So, the current production setting of 50000 is too high?

>>You have SQL connections. Have you ever run UPDATE STATISTICS?
Yes, and Yes. We reindexed and updated statistics when we performed the dump/load nearly two months ago.

>>Are your users connecting using -H & -S
Honestly, I don't know. Where would I find this?
>>Is your MRP run running on the server or on a remote client?
Server
>>If it is on the server (it should be for best pperformance) then it ought not to be using -S.
How would I verify this?

>>Are there app servers used by this application or is it old style "fat client/server"?
No app servers. Client/Server, with several web apps running SQL ODBC read only connections. Light duty queries mostly.

>>Client connection parameters would also be interesting.

>>How is your database setup in terms of storage areas?
>>(IOW what does the .st file look like?)
>>My guess is that everything is in the schema area.
That is affirmative...

The ST file simply shows the BI file info (50 lines), and a single entry for the DB. All entries point to the same folder.

 

[drewser001]

Tom,

Here is a snippet from the client .INI file for connection options... Is this what you are talking about?

ConnectOptions=-N TCP -H <servername removed> -S 6100 -ld <DBName removed>

I am assuming the -S is the DB connection port.

 

[TomBascom]

Perfmon can do all sorts of fun things. I don't have a proper version of Windows handy but it shouldn't take too much clicking around to find disk metrics. As I recall you click on the "+" to add a metric and then hunt for "physical disk" and select "disk reads/sec" and "disk writes/sec". (You should care about number of operations, not number of bytes.) I think there is summary data as well as something specific for each disk. It should be right near where you found the queueing info.

The bi block size is not the same as bi cluster size. You currently have a bi block size of 8. Your options are powers of 2 up to 16. 16 is almost always better than 8.

The bi cluster size is a different thing -- this controls how often a checkpoint must happen. You want your checkpoint frequency to be measured in minutes during peak loads (like an MRP run). Not seconds. This gives the BIW and APWs enough time to smooth out the IO in the background without forcing end users to freeze while flushing buffers. (You should look in PROMON to see if you are flushing buffers...) Or download ProTop. Currently it looks like you have the bi cluster set to 256MB ("Before-Image Cluster Size: 268419072") which ought to be sufficient for most purposes. If anything that might be a bit on the high side (as hard as it may be to believe that I would say such a thing...)

The bi file is a collection of clusters. (To make things even more confusing the "bi file" can actually be made up of multiple "extents" which are also sometimes called files...)

In one sense, the optimum size of the bi file is 4 clusters. (That is the minimum number.) In another sense it is "whatever your application needs". Unfortunately the second sense makes it all too easy to excuse very bad behaviors on the application's part. 25GB would be very bad behavior. But you say that you pre-grew it to that size (presumably via "proutil bigrow") so there may not be an actual need -- your previous 75GB bi file may have just been a wild aberration.

In an "ideal" world the bi file would never be truncated -- there is no recurring need to do so. In the real world it gets automatically truncated when certain maintenance tasks are performed. You would voluntarily truncate it only after some sort of very unusual event -- like a crazy out of control transaction causing all sorts of unusual growth. Or to reign in a mistaken pre-sizing. I usually then initialize it with 8 clusters.

Your test server isn't configured like production -- differences keep popping up. That's going to muddy the water. Try to get them into alignment except where we are suggesting differences.

You should see messages about the BIW and APWs starting just after the section on startup parameters. You can also see how many are running on the PROMON summary screen.

-spin 50,000 is probably high. But it also probably doesn't really impact much.

If PROMON says that all connections are remote that would be a pretty good clue that the MRP run is too You could dig into "user control" to see as well. Or you could look at the .lg file and see what happens when the session that kicks it off connects. The .lg file entries will tell you if it is local or remote.

As for client parameters. I'd start by looking at the shortcut or, if it's a scheduled task, that definition. There may be explicit parameters on the command line. Or there might be a .pf file. Or there might be a script being called. It may take several layers of indirection to get to the actual settings.

Putting everything in the schema area is really not a performance oriented approach. In fact if you were trying to make it slow that would be a good way to start.

 

[drewser001]

Here are the log entries from the production server as of March 6th:

14:39:43 BROKER 0: Multi-user session begin. (333)
14:39:43 BROKER 0: Begin Physical Redo Phase at 0 . (5326)
14:39:43 BROKER 0: Physical Redo Phase Completed at blk 0 off 155 upd 0. (7161)
14:39:45 BROKER 0: PROGRESS Version 9.1D on WINNT. (4234)
14:39:45 BROKER 0: Started using pid: 5384. (6574)
14:39:45 BROKER 0: Physical Database Name (-db): G:\<path removed>. (4235)
14:39:45 BROKER 0: Database Type (-dt): PROGRESS. (4236)
14:39:45 BROKER 0: Force Access (-F): Not Enabled. (4237)
14:39:45 BROKER 0: Direct I/O (-directio): Not Enabled. (4238)
14:39:45 BROKER 0: Number of Database Buffers (-B): 190000. (4239)
14:39:45 BROKER 0: Maximum private buffers per user (-Bpmax): 64. (9422)
14:39:45 BROKER 0: Excess Shared Memory Size (-Mxs): 16631. (4240)
14:39:45 BROKER 0: The shared memory segment is not locked in memory. (10014)
14:39:45 BROKER 0: Current Size of Lock Table (-L): 707808. (4241)
14:39:45 BROKER 0: Hash Table Entries (-hash): 50207. (4242)
14:39:45 BROKER 0: Current Spin Lock Tries (-spin): 50000. (4243)
14:39:45 BROKER 0: Number of Semaphore Sets (-semsets): 1. (6526)
14:39:45 BROKER 0: Crash Recovery (-i): Enabled. (4244)
14:39:45 BROKER 0: Database Blocksize (-blocksize): 4096. (6573)
14:39:45 BROKER 0: Delay of Before-Image Flush (-Mf): 3. (4245)
14:39:45 BROKER 0: Before-Image File I/O (-r -R): Reliable. (4247)
14:39:45 BROKER 0: Before-Image Truncate Interval (-G): 60. (4249)
14:39:45 BROKER 0: Before-Image Cluster Size: 268419072. (4250)
14:39:45 BROKER 0: Before-Image Block Size: 8192. (4251)
14:39:45 BROKER 0: Number of Before-Image Buffers (-bibufs): 25. (4252)
14:39:45 BROKER 0: BI File Threshold size (-bithold): 0.0 Bytes. (9238)
14:39:45 BROKER 0: BI File Threshold Stall (-bistall): Disabled. (6552)
14:39:45 BROKER 0: After-Image Stall (-aistall): Not Enabled. (4254)
14:39:45 BROKER 0: Number of After-Image Buffers (-aibufs): 5. (4256)
14:39:45 BROKER 0: Storage object cache size (-omsize): 1024 (8527)
14:39:45 BROKER 0: Maximum Number of Clients Per Server (-Ma): 1. (4257)
14:39:45 BROKER 0: Maximum Number of Servers (-Mn): 229. (4258)
14:39:45 BROKER 0: Minimum Clients Per Server (-Mi): 1. (4259)
14:39:45 BROKER 0: Maximum Number of Users (-n): 846. (4260)
14:39:45 BROKER 0: Host Name (-H): <removed>. (4261)
14:39:45 BROKER 0: Service Name (-S): <removed>. (4262)
14:39:45 BROKER 0: Network Type (-N): TCP. (4263)
14:39:45 BROKER 0: Character Set (-cpinternal): ISO8859-1. (4264)
14:39:45 BROKER 0: Parameter File: Not Enabled. (4282)
14:39:45 BROKER 0: Maximum Servers Per Broker (-Mpb): 20. (5647)
14:39:45 BROKER 0: Minimum Port for Auto Servers (-minport): 3000. (5648)
14:39:45 BROKER 0: Maximum Port for Auto Servers (-maxport): 5000. (5649)
14:39:45 BROKER 0: This broker supports SQL server groups only. (8864)
14:39:45 BROKER 0: Large database file access has been enabled. (9426)
14:43:03 SQLSRV2 3: SQL Server 9.1D.09 started, configuration: "<removed>.defaultconfiguration"
14:43:03 SQLSRV2 3: DLC from ENVIRONMENT VARIABLE is: D:\<path removed>
14:43:03 SQLSRV2 3: WRKDIR from REGISTRY is: D:\<path removed>
14:43:03 SQLSRV2 3: JDKHOME from REGISTRY is: D:\<path removed>
14:43:03 SQLSRV2 3: JREHOME from REGISTRY is: D:\<path removed>
14:43:03 SQLSRV2 3: CLASSPATH from DEFAULT is:
14:43:03 SQLSRV2 3: PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds
14:51:18 SQLSRV2 4: SQL Server 9.1D.09 started, configuration: "<removed>.defaultconfiguration"
14:51:18 SQLSRV2 4: Thread stack size: 1024000 (bytes).
14:51:18 SQLSRV2 4: DLC from ENVIRONMENT VARIABLE is: D:\<path removed>
14:51:18 SQLSRV2 4: WRKDIR from REGISTRY is: D:\<path removed>
14:51:18 SQLSRV2 4: JDKHOME from REGISTRY is: D:\<path removed>
14:51:18 SQLSRV2 4: JREHOME from REGISTRY is: D:\<path removed>
14:51:18 SQLSRV2 4: CLASSPATH from DEFAULT is:
14:51:18 SQLSRV2 4: PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds

 

[drewser001]

Starting from the top, these are the entries I want to focus on:

Number of Database Buffers (-B): 190000
Current Spin Lock Tries (-spin): 50000

Before-Image Cluster Size: 268419072. (4250)
Before-Image Block Size: 8192
Number of Before-Image Buffers (-bibufs): 25
BI File Threshold size (-bithold): 0.0 Bytes

After-Image Stall (-aistall): Not Enabled
Number of After-Image Buffers (-aibufs): 5

WRKDIR from REGISTRY is: D:\<path removed>

So, here is what I am planning on doing, and will report back next week when I have test results:

Drive Setup
----------------------------------------
- Split the 4-drive RAID 10 SSD set into two RAID 1 sets. One set for DB and the other for BI.
- 64K Stripe and 4K partition unit size for the DB RAID set
- 64K Stripe and 8K partition unit size for the BI RAID set (or should this stay at 4K?)
- Setup a final RAID 1 set for the Temp files on a pair of 15K 146GB SAS drives (since TRIM is obviously an issue here, and I dont have any more SSD drives to test with)
- I may move this setup to a different controller, as there are now two controllers in the server.
(One hosts the embedded array, the other hosts the newer external array.)


DB Setup
--------------------------------------------
- Reconfigure the settings/registry for the -t & -T parameters to point the server temp file creation to the SAS RAID set
- Increase bibufs to 100
- Increase -B to 250,000
- Increase BI Block size to 16K
- Set -spin to 10,000
- Verify and adjust for the -S setting for the Server-side execution of MRP


Will address the AI setup later on (and perhaps another thread if needed)

Thanks everyone for you input so far. If I missed anything at this point, please let me know!

 

[TomBascom]

That all sounds good but you're mostly focusing on fixing an IO problem that you have not actually shown that you have.

True, 999 out of 100 performance problems are IO problems... but it would be good to quantify it before you get too carried away.

Quite aside from messing with disk drives and startup parameters you can also get an awful lot of bang for your buck by doing a proper dump & load into a reasonable set of storage areas.

 

[drewser001]

That is just it, we did the full dump/load/truncate 2 months ago and we gained nearly a 90% performance increase from the system.

MRP is the big hog. It used to take 72 hours to run, and on the test server (and after simply truncating the BI file), we saw a drop of 20 hours from the MRP run time.

But now, when I migrate the DB & BI to SSDs, I get a performance DECREASE. One test I performed had DB & BI on separate pairs of SSDs, and I still saw a longer MRP run time (nearly 6 hours longer)!

While you guys have pointed out that I need to make sure ALL of the Db settings are identical between the test server and the production server, it seems to me that if a data bottleneck did not exist, then the simple introduction of SSDs into the mix (which eliminates latency due to head-seek times) theoretically should decrease the runtime to some degree (even if it were 10-15%). But instead, I don't see any benefit from the eliminated latency.

I attached the IOMeter test results. This was a basic test, with a 64 unit Queue Depth. Each test lasted for 10 minutes and was run in RAW mode (no partition).

 

[drewser001]

Tom,

That is the ultimate question, is this DB configuration (HW/SW) already at it's highest performance level already? Am I seeing a performance *hit* because the application & DB are designed with latentcy riddled spindle disks in mind, and the lack of latency in SSDs is a problem for the APP/DB?

All of the IOMeter tests show that the SSDs are capable of out-performing the spindle disks. But simple functions like "restoring the DB" once it is copied over take the same time or longer than the production system. So, where is the bottleneck? Or, are these tests not valid? It is difficult to say, because copying the DB onto the SSDs is faster than copying them to HDDs.