Hi folks,
I am working as an Oracle developer, I have faced a crazy issue while Oracle running over SSD.
I have two OCZ Vertex 3 MAX IOPS drives (firmware 2.15). It runs on an INTEL C600 SATA Chipset. Windows 2008 R2.
So the issue is following.
When I put an Oracle redo logs on first SSD and the database file on other SSD a serious degradation happens on the second SSD. I created a test table without any indexes primary keys, just for test purpose to simplify the situation. It has a blob field and varchar field. I`m inserting data at max speed with 1kb to blob field.
And here what is happens: Oracle begins to write to redo logs at speed 30-40mb/s. And while it's writing only to logs it works fine. Then Oracle stop write to redo logs and write to database file at speed 40mb. Then it begins to write to redo logs again. And after it, the mystery happens. It begins to write to datafile at speed of 2mb/sec ( ~200 iops) and that all. And from this point, it will write to this SSD drive at this speed. If I open the disk from file manager it will be slow and if I try to copy a file to it while oracle writing 2mb/s it will be verrry slow too. So it looks that SSD drives running at its limit but how that can happen? The SSD is more than half empty. I tried to switch redo logs and datafile drives and get the same result but with the other drive.
If I just copy a file to SSD it shows write speed ~600mb/s. There also two HDD drives in that system. So I tried to put redo logs on HDD and data files on SSD. And here what is happened. Write speed to redo logs was increased to 50-60mb/s(so the speed of inserts too. Why its slower on SSD??). But then again when it begins to write to SSD drive to datafile it will get same 2mb/s speed.
The next what I tried was to put redo logs and data files on one SSD drive. And here miracle happened. It was writing to redo logs first then to data files, but it was writing at 20mb/s sec speed to redo logs and to data files, and no such behavior with 2mb/s. So it works faster on one SSD than on two SSD. Why?
The next thing I tried was to put datafile on HDD and redo logs on SSD. And it worked fine too, the write speed to datafile was 30mb/s.
So I'm out of idea what is happening and how to fix it and what wrong with my SSD.
Thanks & Regards
Camillelola