SSD performance issue

[bjanker77]

Hi.

Our biggest customer installed a new server over the weekend, and is experiencing a great drop in performance compared to the old server.

The server is a HP ProLiant BL460c Gen8. 64 GB RAM. 2 x 200GB SSD drives in RAID 1 setup. Win2012.

The database is about 22 GB in size, and was rebuilt using unload/reload, pagesize 8K.

The IT-manager has been monitoring the IO to the disks on the server, and for regular usage it's averaging around 500 MB/s. But when monitoring the db and log files, the IO is only between 100 KB/s and 1 MB/s. Which is way lower than expected.

Any first thoughts on why this server is not operating as expected when it comes to performance? Perhaps there is some switches we can add to the startup? Or something else we can check up on?

Regards,

Bjarne Anker Maritech Systems AS Norway

[Breck_Carter]

What version of SQL Anywhere are you using?

What is the IT manager using to monitor disk I/O? the Resource Manager?

Has the database data been entirely loaded into RAM?

SELECT PROPERTY ( 'CurrentCacheSize' ) / 1024.0 / 1024.0 AS CurrentCacheSizeG,
       PROPERTY ( 'MaxCacheSize' ) / 1024.0 / 1024.0 AS MaxCacheSizeG;

CurrentCacheSizeG,MaxCacheSizeG
0.059666,14.353611

[bjanker77]

The server is 12.0.1, build 4142. I'm not sure which software is used to monitor disk I/O at this point.

The database is not loaded into RAM, no.

This is the result of the query you provided:

CurrentCacheSizeG,MaxCacheSizeG 31.250000,31.250000

br,

Bjarne

[Breck_Carter]

Well, the current cache size is 31.25G, and your database is 22G, so perhaps the database data is in fact all loaded into RAM, so I'm not sure what disk I/O is actually required except log writes and the occasional checkpoint.

[Breck_Carter]

In your comment on a different question, you said "Every query is using the double amount of time"... so apparently there are symptoms OTHER than slow disk transfer rates.

What are the differences between old and new servers?

[bjanker77]

Hi again.

Yes, "everything" takes double the amount of time. It's almost measurable, which is concerning. The main difference between the servers, besides CPU-power, mainbord and 4-5 years of usage, is that the new server has over double the amount of RAM (64GB vs 24GB) and the new server has 2 x 200 GB SSD drives in RAID 1, while the old one had 8 x 72 GB HD drives in RAID 1+0. I suspect the RAID setup to have something to do with this. In all other areas the new server is much, much more powerful than the old one.

Regards,

Bjarne

[MCMartin]

For new CPUs compared to old ones, you might be easily mislead. The overall performance might be great in benchmarks, but keep also an eye on the single core performance. Today server CPUs tend to have a lot of cores, which each alone are not so powerful anymore as in former times. And even databases are still using single cores for a lot of tasks. So have a look at https://www.cpubenchmark.net/

[VolkerBarth]

Are there differences in the SQL Anywhere version (edition, build number, licensed processors) between both machines? Is virtualization used?

[Former Member]

Old Server was Raid 1+0 New Server is Raid 1, but yet new is SSD. Maybe test by adding another single drive, move database to that drive temporarily and see if performance improves. Note there is a big difference between Raid 1 and Raid 10, they are opposite ends of the performance spectrum.

[Vlad]

Hm, Internet says that there is no performance difference between RAID 10 and 01: http://www.thegeekstuff.com/2011/10/raid10-vs-raid01/ Do you have another evidence of your statement?

[VolkerBarth]

Note, according to Bjarne, the new server is using RAID 1, not 01 (aka 0+1).

[Breck_Carter]

The consensus in this article seems to be Raid 10 gives (much) better performance than Raid 1... but the comparisons all involve disk drives not SSDs.

FWIW this Google search turns up many interesting conversations...

why is ssd slow

[bjanker77]

The differences between the servers is amount of memory (64GB vs 24 GB), Windows 2012 R2 vs. 2008 and RAID 1 vs. RAID 1+0. Otherwise it's the same license, the same version of SQL Anywhere and the same db-file. We adjusted Power Options from "Balanced" to "High Performance" and got quite the boost. But still it's slower than the 4 year old server the moved from.

[Breck_Carter]

Presumably the server has at least one rotating disk drive big enough to hold one copy of the database. Try copying the *.db file over there and running a test. If it runs quickly then the SSDs are likely the culprit... which apparently is quite often the case.