Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark
On Mon, Mar 24, 2014 at 4:49 AM, piotr maliński wrote: > I know it's bad. I'm trying to determine the cause of the difference, and > if it's a "feature" of that SSD or a bug of some sort. There was a very intensive discussion for a post labeled "UPDATE/INSERTing 1-2k rows slower than expected". You can read it at https://www.mail-archive.com/sqlite-users%40sqlite.org/msg58872.html . Also there were different tests I made during this discussion. As long as I remember the general observation was that it's hardware that usually says "ok, I did this guaranteed -to-be-on-disk operation you've asked for", but actually caching it somewhere inside. And probably multiply USB controllers from the bunch of manufacturers are to blame. SATA controller on motherboards are usually less diversified, so more likely to be more accurate. Also there's a setting in Windows for hard drives, "enable writing cache". If you find a similar setting in xubuntu, then probably enabling it would make your sata connection on par with your usb connection. But it's just a hypothesis, it's harder to make useful tests with sata connection due physical and interface limitations of the interface. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark
I know it's bad. I'm trying to determine the cause of the difference, and if it's a "feature" of that SSD or a bug of some sort. I'll check if the speed changes when I disable barrier on the mounted filesystem. If so then it's slow on write barriers. 2014-03-24 1:35 GMT+01:00 Simon Slavin : > > On 23 Mar 2014, at 11:19pm, piotr maliński wrote: > > > So maybe this particular SSD is slow with write barriers, while other > > doesn't have a problem with it (as they don't change performance between > > SATA and USB3). > > You have missed the point. When Florian write "Many USB SATA adapters do > not handle [write barriers] correctly" he is saying that your storage > medium will corrupt your data if power fails or other hardware problems > occur before the files are not closed correctly. Write barriers are > important. > > SQLite tries very hard to make sure your data is safe. Doing this > involves a number of 'write' commands which have to be done in a specific > order. Rotating disk drives are very slow to do this because they have to > wait for the disk to rotate to the right position each time. SSD storage > is faster because any sector can be written at any time, but they still > should be accepting each 'write' command, then doing it, then returning a > result of 'write successful'. In other words, they should not be able to > say "command executed" immediately. They should have a write barrier. > > If you find some storage system that is unexpectedly fast, it is probably > not doing this correctly. It is accepting the 'write' command, immediately > saying that the command was successful, but actually doing the 'write' > command in the background later. Most desktop computers are deliberately > set up to do this because it makes the computer run faster, and that's what > users want. And most storage systems that do this do not do the writes in > the order the commands were given. They blast all the writes to storage in > sector order, because this gives, overall, a faster result. > > And that's why hard disks sold to be used in servers apparently run more > slowly: they are set to do storage properly, because they are more likely > to be used to store valuable data rather than just write a document or > email which can be retyped if changes are lost. > > So yes, one drive system is running quickly. But it's doing it at the > cost of corrupting your data if you suffer a hardware problem. You have > lost the ACID property of your database. If you want to know that your > data is safe, you're probably going to want the system that runs slowly. > > On the other hand, that particular type of SSD may just have timings > slightly different from what the storage system expects. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark
On 23 Mar 2014, at 11:19pm, piotr maliński wrote: > So maybe this particular SSD is slow with write barriers, while other > doesn't have a problem with it (as they don't change performance between > SATA and USB3). You have missed the point. When Florian write "Many USB SATA adapters do not handle [write barriers] correctly" he is saying that your storage medium will corrupt your data if power fails or other hardware problems occur before the files are not closed correctly. Write barriers are important. SQLite tries very hard to make sure your data is safe. Doing this involves a number of 'write' commands which have to be done in a specific order. Rotating disk drives are very slow to do this because they have to wait for the disk to rotate to the right position each time. SSD storage is faster because any sector can be written at any time, but they still should be accepting each 'write' command, then doing it, then returning a result of 'write successful'. In other words, they should not be able to say "command executed" immediately. They should have a write barrier. If you find some storage system that is unexpectedly fast, it is probably not doing this correctly. It is accepting the 'write' command, immediately saying that the command was successful, but actually doing the 'write' command in the background later. Most desktop computers are deliberately set up to do this because it makes the computer run faster, and that's what users want. And most storage systems that do this do not do the writes in the order the commands were given. They blast all the writes to storage in sector order, because this gives, overall, a faster result. And that's why hard disks sold to be used in servers apparently run more slowly: they are set to do storage properly, because they are more likely to be used to store valuable data rather than just write a document or email which can be retyped if changes are lost. So yes, one drive system is running quickly. But it's doing it at the cost of corrupting your data if you suffer a hardware problem. You have lost the ACID property of your database. If you want to know that your data is safe, you're probably going to want the system that runs slowly. On the other hand, that particular type of SSD may just have timings slightly different from what the storage system expects. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark
So maybe this particular SSD is slow with write barriers, while other doesn't have a problem with it (as they don't change performance between SATA and USB3). 2014-03-23 22:18 GMT+01:00 Florian Weimer : > * piotr maliński: > > > The usb3 case has asmedia chip and phoronix sqlite insert benchmark runs > > around 6 times faster than when running via sata. Some benchmarks like > > untaring, some iozone benchmarks do not differ so I'm suspecting > controller > > or OS issue. Does sqlite insert uses some specific I/O operations? > > Yes, write barriers. Many USB SATA adapters do not handle them > properly, leading to significant speed-ups and increased risk of data > loss. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark
* piotr maliński: > The usb3 case has asmedia chip and phoronix sqlite insert benchmark runs > around 6 times faster than when running via sata. Some benchmarks like > untaring, some iozone benchmarks do not differ so I'm suspecting controller > or OS issue. Does sqlite insert uses some specific I/O operations? Yes, write barriers. Many USB SATA adapters do not handle them properly, leading to significant speed-ups and increased risk of data loss. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users