DB on SSD

What I would do first is migrate to a Type II Storage Area, where data from the same object would be stored together.

You would be much better off using Type II storage areas.  But you can't get there until you upgrade from 9.x.  

Your understanding is not entirely correct.  Yes, buffers in the database buffer pool are the size of database blocks.  So if your DB block size is 8 KB then the buffer pool consists of -B number of 8 KB buffers.  But you don't want to tune your DB block size downward based on record size.  You should never use 1 KB or 2 KB blocks.  This is because data is fetched from disk in units of file system clusters and they are typically 4 KB or 8 KB.  So you don't want to do a 4 KB physical I/O for every 1 KB or 2 KB logical I/O.  That would hurt performance a lot.  It would also mean that block metadata (headers, row directories, etc.) make up a larger percentage of your buffer memory.

In Type I areas, a given record block may contain records from any table that is assigned to that area.  So if data in a large table is heavily scattered across the blocks in the area, you may have to do a lot more physical I/O to read all the blocks that contain those records than you would in the ideal case.  You can minimize this effect by putting a large table into its own storage area (and create another storage area for the indexes of that table).  That way when you query, say, your transaction history table and read an 8 KB block, it contains nothing but transaction history records, not customer or order-line records.  So putting that table in its own area can improve your caching efficiency somewhat.

If the statement that your indexes are "poorly configured" means that they don't match your query predicates and allow you to bracket efficiently, that will also make your ratio of logical to physical I/O higher than it should be, reduce your caching efficiency, and reduce overall application performance.  Adding indexes isn't free, in the sense that additional indexes result in additional work to be done for creates and deletes, and possibly updates.  But if adding a particular index will allow queries on that table to bracket efficiently where they currently do not, then it is well worth doing so.

Moving storage to SSD may provide a nice one-time bump in performance, temporarily masking these structure and schema inefficiencies or making their resolution less of a business priority.  But that doesn't mean these things aren't problems to be addressed.

Just to add to what Rob said.. in some cases it might make more sense to move the smaller (lookup style) tables into their own areas (or common areas for a set of tables).

Make sure your tablerangesize and indexrangesize startup parameters are set correctly and find your active tables/indexes. You probably wont be able to cache your larger tables, but smaller very active tables should be easy enough to cache once they aren't mixed in with all of the other tables.

Even on version 9 there are probably some low hanging fruit you could take care of to give a decent boost to performance.

What is the actual Progress version ?  9.1?  The last letter makes a difference.   9.1A,  9.1E  If you have 9.1E there are options available that aren't in 9.1A, B, C, D

Run a table analysis or database analysis to get an idea of the data layout and look at the scatter factor column for the large tables, don't bother for the smaller tables.

Look into enabling tablerangesize and indexrangesize so you can get an idea of what tables are being read.  You might already have done this since you were mentioning index reads before.

In version 9 there are benefits to moving tables that have high updates to their own storage areas as well as tables with high reads.  

Might want to post your database startup parameters - items like -B, spin, apw, biw, biclustersize and what OS are you running on.

Progress Version 9.1E04 (32 bit) on AIX 5.3

Much of the optimization of databases is based on traditional hard disks. It is faster to read 64kb of sequential data from a hard drive than it is to read 8 x 8kb or 64 x 1kb. This goes away with SSD and fiber data channels.

We have re-written most of our application but still have about 300 V9  .r files (without source) that need to be rewritten before we can upgrade the database, that won't be completed before we move to the new server. We can't upgrade AIX because Progress doesn't support V9 on AIX 6. This puts limits on the amount of shared memory, and the size of -B.

Trust me. We would love to run v11 and are working hard to re-write the remaining .r programs.

With a 64bit  RDBMS and SSD storage I'm not even sure there is a run-time advantage to Type II storage. It will be nice to rebuild the indexes on a table in minutes rather than the hours(/days) it now takes although the online utilities make periodic maintenance like this less crucial.

I taught system administration for Progress back in the V7 days and we are doing what we can to optimize performance in our current environment. I haven't found much on optimising performance when the database is on SSD. My boss says "it is going to Make Performance Great Again!". I am trying to approach this realistically and I think treating SSD like a fast HDD is naive.

We have 3 webspeed programmers, a ChUI programmer who took a couple of classes back in the V6/V7 days, me, and my boss who doubles as the AIX admin. I would love to run benchmarks but really don't have the time and I'm not sure it would have any value to others given our non-standard environment.

Eric

> treating SSD like a fast HDD is naive.

SSD almost does not have a seek time. The queries that retrieve a lot of data from disk using the indexes with bad logical scatter factor will run much faster on SSD. Idxfix/option 2 (scan indexes) running on HDD with/without record validation will rawly show how bad is a logical scatter factor for a specified index (provided a table is large enough compared to a system cache).

We are currently running quite a few databases on SSD.. I can assure you it makes a difference. SSD is much much faster at random reads/writes than a HDD could ever be. For the most part OE just does random reads and writes.. even if the data is contiguous it is up to the OS/Hardware to notice that and batch the operations.

The other part in the equation you seem to be forgetting is that block storage on disk directly equates to how data is buffered and written in OE.

So if you have a block with 1 record you want and 100 that you don't because of data being intermingled... well you just wasted memory in your buffer cache. Caching things you don't care about/need isn't great, especially important if you are limited to a few GB.

Same thing goes for writes.. database blocks are written/flushed not rows. Having data properly grouped in blocks allows for fewer physical writes to disk for heavily updated tables.

It does matter quite a bit. Every IO you get rid of just makes those SSD disks that much faster.

If it matters enough based on your current performance needs is a different matter entirely.

> With a 64bit  RDBMS and SSD storage I'm not even sure there is a run-time advantage to Type II storage

Yes there is.

SSD is indeed wonderfully faster than rotating rust.  *IF* you are IO bound then replacing a hard drive with SSD will seem miraculous.

Unless, of course, the storage is in a SAN.

SAN storage is at the far end of a cable.  That cable is much slower and has far more latency than the disks do.  Making the disks go faster won't help much.  The cable is still there making sure everything goes slow.  Plus you are sharing the device with a bunch of other applications that (incorrectly) view their needs as being equally important to your needs.  Getting the disks out of the SAN and internal to the server that needs the data will help a lot.

So if you have a SAN don't expect a miracle to occur just because you gave the vendor a boatload of money to put SSD in the SAN.  It might be a little bit better.  But it probably won't be a miracle.

For best performance you want the "path length" between the CPU using the data and the data itself to be as short as possible.  That means that you want the "working set" of active data to be in memory on the server, as compact and efficient as it can be and in the address space of the executable working with it.  IOW -- you need it to be in the -B buffer cache and you need it to be efficiently organized -- and type 2 storage areas are the best way to accomplish efficient organization.  I have done some testing and data in -B is *75x* faster to access than data in the OS filesystem cache.  Then you have to drill through the disk adapter and down to the device itself -- adding more layers of indirection and latency (ultimately actual IO ops were at least 1,500 times slower than -B).  Until data starts teleporting itself to CPUs this is going to be the case.  So sure, if you have a choice between SSD and rotating rust, SSD is going to be a whole lot faster way out at the far end of things when an IO occurs.  But avoiding that IO, and all of the junk that it takes to get an IO operation done, is still vitally important and type 2 areas are still a critical part of accomplishing excellent performance.

(If you are stuck on v9...  you can simulate many of the advantages of type 2 areas by creating storage areas for a single table and *only* storing that single table in that area (create a discrete area for the indexes too).  You probably only want to do this for very large and very active tables but it will get you many of the advantages of type 2 storage.)