Re: HDD vs SSD without explanation
2018-01-14 19:09 GMT-08:00 Justin Pryzby :
> On Sun, Jan 14, 2018 at 06:25:40PM -0800, Neto pr wrote:
>> > The query plan is all garbled by mail , could you resend? Or post a link
>> > from
>> > https://explain.depesz.com/
>
> On Sun, Jan 14, 2018 at 06:36:02PM -0800, Neto pr wrote:
>> I was not able to upload to the site, because I'm saving the execution
>> plan in the database, and when I retrieve it, it loses the line breaks,
>
> That's why it's an issue for me, too..
>
>> > What OS/kernel are you using? LVM? filesystem? I/O scheduler?
>> > partitions?
>>
>> See below the Disk FileSystem
>> root@hp2ml110deb:/# fdisk -l
>> Disk /dev/sda: 931.5 GiB, 1000204886016 bytes, 1953525168 sectors
>>
>> Disk /dev/sdb: 465.8 GiB, 500107862016 bytes, 976773168 sectors
>> Units: sectors of 1 * 512 = 512 bytes
>> Sector size (logical/physical): 512 bytes / 512 bytes
>> I/O size (minimum/optimal): 512 bytes / 512 bytes
>>
> What about sdb partitions/FS?
I used EXT4 filesystem in Debian SO.
>
> On Sun, Jan 14, 2018 at 06:25:40PM -0800, Neto pr wrote:
>> The DBMS and tablespace of users is installed in /dev/sdb SSD.
>
> Is that also a temp_tablespace ? Or are your hashes spilling to HDD instead ?
>
How can I find out where my temp_tablesapce is?
With the command \db+ (see below) does not show the location. But the
DBMS I asked to install inside the SSD, but how can I find out the
exact location of the temp_tablespace ?
tpch40gnorssd=# \db+
List of tablespaces
Name| Owner |Location| Access
privileges | Options | Size | Description
+--++---+-++-
pg_default | postgres ||
| | 21 MB |
pg_global | postgres ||
| | 573 kB |
tblpgssd | postgres | /media/ssd500gb/dados/pg101ssd |
| | 206 GB |
(3 rows)
--
> Group Key: nation.n_name, (date_part(_year_::text,
> (orders.o_orderdate)::timestamp without time zone))
> Buffers: shared hit=3773802 read=7120852, temp read=3550293 written=3541542
>
> Are your SSD being used for anything else ?
>
> What about these?
>
>> > readahead? blockdev --getra
>
About knowing if the SSD is being used by another process, I will
still execute the command and send the result.
But I can say that the SSD is only used by the DBMS.
Explaining better, My server has an HDD and an SSD. The Debian OS is
installed on the HDD and I installed the DBMS inside the SSD and the
data tablespace also inside the SSD .
The server is dedicated to the DBMS and when I execute the queries,
nothing else is executed. I still can not understand how an HDD is
faster than an SSD.
I ran queries again on the SSD and the results were not good see:
execution 1- 00:16:13
execution 2- 00:25:30
execution 3- 00:28:09
execution 4- 00:24:33
execution 5- 00:24:38
Regards
Neto
>> > If you're running under linux, maybe you can just send the output of:
>> > for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done
>> > or: tail
>> > /sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size}
>
>> > Can you reproduce the speed difference using dd ?
>> > time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K
>> > skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
>> >
>> > Or: bonnie++ -f -n0
>
> Justin
Re: HDD vs SSD without explanation
2018-01-15 3:04 GMT-08:00 Neto pr :
> 2018-01-14 19:09 GMT-08:00 Justin Pryzby :
>> On Sun, Jan 14, 2018 at 06:25:40PM -0800, Neto pr wrote:
>>> > The query plan is all garbled by mail , could you resend? Or post a
link from
>>> > https://explain.depesz.com/
>>
>> On Sun, Jan 14, 2018 at 06:36:02PM -0800, Neto pr wrote:
>>> I was not able to upload to the site, because I'm saving the execution
>>> plan in the database, and when I retrieve it, it loses the line breaks,
>>
>> That's why it's an issue for me, too..
>>
>>> > What OS/kernel are you using? LVM? filesystem? I/O scheduler?
partitions?
>>>
>>> See below the Disk FileSystem
>>> root@hp2ml110deb:/# fdisk -l
>>> Disk /dev/sda: 931.5 GiB, 1000204886016 bytes, 1953525168 sectors
>>>
>>> Disk /dev/sdb: 465.8 GiB, 500107862016 bytes, 976773168 sectors
>>> Units: sectors of 1 * 512 = 512 bytes
>>> Sector size (logical/physical): 512 bytes / 512 bytes
>>> I/O size (minimum/optimal): 512 bytes / 512 bytes
>>>
>> What about sdb partitions/FS?
>
> I used EXT4 filesystem in Debian SO.
>
>>
>> On Sun, Jan 14, 2018 at 06:25:40PM -0800, Neto pr wrote:
>>> The DBMS and tablespace of users is installed in /dev/sdb SSD.
>>
>> Is that also a temp_tablespace ? Or are your hashes spilling to HDD
instead ?
>>
>
> How can I find out where my temp_tablesapce is?
> With the command \db+ (see below) does not show the location. But the
> DBMS I asked to install inside the SSD, but how can I find out the
> exact location of the temp_tablespace ?
>
>
> tpch40gnorssd=# \db+
> List of tablespaces
> Name| Owner |Location| Access
> privileges | Options | Size | Description
>
+--++---+-++-
> pg_default | postgres ||
> | | 21 MB |
> pg_global | postgres ||
> | | 573 kB |
> tblpgssd | postgres | /media/ssd500gb/dados/pg101ssd |
> | | 206 GB |
> (3 rows)
>
--
>
I checked that the temporary tablespace pg_default is on the SSD, because
when running show temp_tablespaces in psql returns empty, and by the
documentation,
https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-TEMP-TABLESPACES
will be in the default directory, where I installed the DBMS in:
/media/ssd500gb/opt/pgv101norssd/data.
The servers where I executed the query with HDD SAS is not the same one
where I executed the query with SSD, but they are identical Server (HP
Proliant ML110), it has the same model and configuration, only the disks
that are not the same, see:
Server 1
- HDD SAS 15 Krpm - 320 GB (Location where O.S. Debian and Postgresql are
installed)
Server 2
- Samsung Evo SSD 500 GB (Location where Postgresql is Installed)
- HDD Sata 7500 Krpm - 1TB (Location where O.S Debian is installed)
>> Group Key: nation.n_name, (date_part(_year_::text,
(orders.o_orderdate)::timestamp without time zone))
>> Buffers: shared hit=3773802 read=7120852, temp read=3550293
written=3541542
>>
>> Are your SSD being used for anything else ?
>>
>> What about these?
>>
>>> > readahead? blockdev --getra
>>
>
> About knowing if the SSD is being used by another process, I will
> still execute the command and send the result.
>
> But I can say that the SSD is only used by the DBMS.
> Explaining better, My server has an HDD and an SSD. The Debian OS is
> installed on the HDD and I installed the DBMS inside the SSD and the
> data tablespace also inside the SSD .
> The server is dedicated to the DBMS and when I execute the queries,
> nothing else is executed. I still can not understand how an HDD is
> faster than an SSD.
> I ran queries again on the SSD and the results were not good see:
>
> execution 1- 00:16:13
> execution 2- 00:25:30
> execution 3- 00:28:09
> execution 4- 00:24:33
> execution 5- 00:24:38
>
> Regards
> Neto
>
>
>
>
>>> > If you're running under linux, maybe you can just send the output of:
>>> > for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done
>>> > or: tail
/sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size}
>>
>>> > Can you reproduce the speed difference using dd ?
>>> > time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K
skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
>>> >
>>> > Or: bonnie++ -f -n0
>>
>> Justin
Re: HDD vs SSD without explanation
Hello Neto Am 14.01.2018 um 21:44 schrieb Neto pr: Dear all Someone help me analyze the two execution plans below (Explain ANALYZE used), is the query 9 of TPC-H benchmark [1]. I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB 15 Krpm AND SSD Sansung EVO 500GB. My DBMS parameters presents in postgresql.conf is default, but in SSD I have changed random_page_cost = 1.0. you are comparing a SAS Drive against a SATA SSD. Their interfaces serve a completely different bandwidth. While a SAS-3 device does 12 Gbit/s SATA-3 device is only able to transfer 6 Gbit/s (a current SAS-4 reaches 22.5 Gbit/s) Do a short research on SAS vs SATA and then use a SAS SSD for comparison :) regards Georg
Re: HDD vs SSD without explanation
we've had the same experience here - with older SATA 2 (3Gbps) - in spite of SSD having no spin latency, the bus speed itself was half of the SAS-2 (6Gbps) we were using at the time which negated SSD perf in this area. HDD was about the same perf as SSD for us. Biran On Mon, Jan 15, 2018 at 1:32 PM, Georg H. wrote: > > Hello Neto > > Am 14.01.2018 um 21:44 schrieb Neto pr: > >> Dear all >> >> Someone help me analyze the two execution plans below (Explain ANALYZE >> used), is the query 9 of TPC-H benchmark [1]. >> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB >> 15 Krpm AND SSD Sansung EVO 500GB. >> My DBMS parameters presents in postgresql.conf is default, but in SSD I >> have changed random_page_cost = 1.0. >> >> you are comparing a SAS Drive against a SATA SSD. Their interfaces serve > a completely different bandwidth. > While a SAS-3 device does 12 Gbit/s SATA-3 device is only able to > transfer 6 Gbit/s (a current SAS-4 reaches 22.5 Gbit/s) > Do a short research on SAS vs SATA and then use a SAS SSD for comparison :) > > regards > Georg > >
Re: HDD vs SSD without explanation
2018-01-15 15:32 GMT-03:00 Georg H. : > > Hello Neto > > Am 14.01.2018 um 21:44 schrieb Neto pr: > >> Dear all >> >> Someone help me analyze the two execution plans below (Explain ANALYZE >> used), is the query 9 of TPC-H benchmark [1]. >> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB >> 15 Krpm AND SSD Sansung EVO 500GB. >> My DBMS parameters presents in postgresql.conf is default, but in SSD I >> have changed random_page_cost = 1.0. >> >> you are comparing a SAS Drive against a SATA SSD. Their interfaces serve > a completely different bandwidth. > While a SAS-3 device does 12 Gbit/s SATA-3 device is only able to > transfer 6 Gbit/s (a current SAS-4 reaches 22.5 Gbit/s) > Do a short research on SAS vs SATA and then use a SAS SSD for comparison :) > The query being all read operations both drives should perform somewhat similarly. Therefore, either the SAS drive has some special sauce to it (a.k.a very fast built-in cache) or there is something else going on these systems. Otherwise he shouldn't be stressing the 6 Gbit/s interface limit with a single drive, be that the SATA or the SAS drive. Neto, you have been suggested to provide a number of command outputs to know more about your system. Testing the raw read throughput of both your drives should be first on your list. Cheers.
Re: HDD vs SSD without explanation
Hi Georg, Your answer I believe has revealed the real problem. I looked at the specification of my SATA SSD, and from my SAS HDD, I saw that the SAS has 12 Gb/s versus 6 Gb/s from the SSD SSD: Samsung 500 GB SATA III 6Gb/s - Model: 850 Evo http://www.samsung.com/semiconductor/minisite/ssd/product/consumer/850evo/ HDD: HPE 300GB 12G SAS Part-Number: 737261-B21 https://h20195.www2.hpe.com/v2/GetPDF.aspx%2Fc04111744.pdf I saw that the SAS band is double, and because of that reason the difference in performance occurred. Another question, if I compare the disk below HDD SAS that has a transfer rate of 6Gb/s equal to the SSD SATA 6Gb/s, do you think the SSD would be more agile in this case? HDD: HP 450GB 6G SAS 15K rpm LFF (3.5-inch) Part-Number: 652615-B21 best Regards Neto 2018-01-15 16:32 GMT-02:00 Georg H. : > > Hello Neto > > Am 14.01.2018 um 21:44 schrieb Neto pr: >> >> Dear all >> >> Someone help me analyze the two execution plans below (Explain ANALYZE >> used), is the query 9 of TPC-H benchmark [1]. >> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB >> 15 Krpm AND SSD Sansung EVO 500GB. >> My DBMS parameters presents in postgresql.conf is default, but in SSD I >> have changed random_page_cost = 1.0. >> > you are comparing a SAS Drive against a SATA SSD. Their interfaces serve a > completely different bandwidth. > While a SAS-3 device does 12 Gbit/s SATA-3 device is only able to transfer > 6 Gbit/s (a current SAS-4 reaches 22.5 Gbit/s) > Do a short research on SAS vs SATA and then use a SAS SSD for comparison :) > > regards > Georg >
Re: HDD vs SSD without explanation
2018-01-15 17:55 GMT-02:00 Fernando Hevia : > > > 2018-01-15 15:32 GMT-03:00 Georg H. : >> >> >> Hello Neto >> >> Am 14.01.2018 um 21:44 schrieb Neto pr: >>> >>> Dear all >>> >>> Someone help me analyze the two execution plans below (Explain ANALYZE >>> used), is the query 9 of TPC-H benchmark [1]. >>> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB >>> 15 Krpm AND SSD Sansung EVO 500GB. >>> My DBMS parameters presents in postgresql.conf is default, but in SSD I >>> have changed random_page_cost = 1.0. >>> >> you are comparing a SAS Drive against a SATA SSD. Their interfaces serve a >> completely different bandwidth. >> While a SAS-3 device does 12 Gbit/s SATA-3 device is only able to >> transfer 6 Gbit/s (a current SAS-4 reaches 22.5 Gbit/s) >> Do a short research on SAS vs SATA and then use a SAS SSD for comparison >> :) > > > The query being all read operations both drives should perform somewhat > similarly. Therefore, either the SAS drive has some special sauce to it > (a.k.a very fast built-in cache) or there is something else going on these > systems. Otherwise he shouldn't be stressing the 6 Gbit/s interface limit > with a single drive, be that the SATA or the SAS drive. > > Neto, you have been suggested to provide a number of command outputs to know > more about your system. Testing the raw read throughput of both your drives > should be first on your list. > Guys, sorry for the Top Post, I forgot Fernando, I think the difference of 6 Gb/s to 12 Gb/s from SAS is what caused the difference in query execution time. Because looking at the execution plans and the cost estimate, I did not see many differences, in methods of access among other things. Regarding the query, none of them use indexes, since I did a first test without indexes. Do you think that if I compare the disk below HDD SAS that has a transfer rate of 6Gb/s equal to the SSD SATA 6Gb/s, do you think the SSD would be more agile in this case? HDD: HP 450GB 6G SAS 15K rpm LFF (3.5-inch) Part-Number: 652615-B21 Neto > Cheers. > > > >
Re: HDD vs SSD without explanation
2018-01-15 20:25 GMT-03:00 Neto pr : > 2018-01-15 17:55 GMT-02:00 Fernando Hevia : > > > > > > 2018-01-15 15:32 GMT-03:00 Georg H. : > >> > >> > >> Hello Neto > >> > >> Am 14.01.2018 um 21:44 schrieb Neto pr: > >>> > >>> Dear all > >>> > >>> Someone help me analyze the two execution plans below (Explain ANALYZE > >>> used), is the query 9 of TPC-H benchmark [1]. > >>> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS > 320GB > >>> 15 Krpm AND SSD Sansung EVO 500GB. > >>> My DBMS parameters presents in postgresql.conf is default, but in SSD I > >>> have changed random_page_cost = 1.0. > >>> > >> you are comparing a SAS Drive against a SATA SSD. Their interfaces > serve a > >> completely different bandwidth. > >> While a SAS-3 device does 12 Gbit/s SATA-3 device is only able to > >> transfer 6 Gbit/s (a current SAS-4 reaches 22.5 Gbit/s) > >> Do a short research on SAS vs SATA and then use a SAS SSD for comparison > >> :) > > > > > > The query being all read operations both drives should perform somewhat > > similarly. Therefore, either the SAS drive has some special sauce to it > > (a.k.a very fast built-in cache) or there is something else going on > these > > systems. Otherwise he shouldn't be stressing the 6 Gbit/s interface limit > > with a single drive, be that the SATA or the SAS drive. > > > > Neto, you have been suggested to provide a number of command outputs to > know > > more about your system. Testing the raw read throughput of both your > drives > > should be first on your list. > > > > > Guys, sorry for the Top Post, I forgot > > Fernando, I think the difference of 6 Gb/s to 12 Gb/s from SAS is what > caused the difference in query execution time. > Because looking at the execution plans and the cost estimate, I did > not see many differences, in methods of access among other things. > Regarding the query, none of them use indexes, since I did a first > test without indexes. > Do you think that if I compare the disk below HDD SAS that has a > transfer rate of 6Gb/s equal to the SSD SATA 6Gb/s, do you think the > SSD would be more agile in this case? > > HDD: HP 450GB 6G SAS 15K rpm LFF (3.5-inch) Part-Number: 652615-B21 > > Neto > The 6 Gb/s interface is capable of a maximum throughput of around 600 Mb/s. None of your drives can achieve that so I don't think you are limited to the interface speed. The 12 Gb/s interface speed advantage kicks in when there are several drives installed and it won't make a diference in a single drive or even a two drive system. But don't take my word for it. Test your drives throughput with the command Justin suggested so you know exactly what each drive is capable of: Can you reproduce the speed difference using dd ? > time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K > skip=$((128*$RANDOM/32)) # set bs to optimal_io_size While common sense says SSD drive should outperform the mechanical one, your test scenario (large volume sequential reads) evens out the field a lot. Still I would have expected somewhat similar results in the outcome, so yes, it is weird that the SAS drive doubles the SSD performance. That is why I think there must be something else going on during your tests on the SSD server. It can also be that the SSD isn't working properly or you are running an suboptimal OS+server+controller configuration for the drive.
Re: HDD vs SSD without explanation
2018-01-15 16:18 GMT-08:00 Fernando Hevia : > > > 2018-01-15 20:25 GMT-03:00 Neto pr : >> >> 2018-01-15 17:55 GMT-02:00 Fernando Hevia : >> > >> > >> > 2018-01-15 15:32 GMT-03:00 Georg H. : >> >> >> >> >> >> Hello Neto >> >> >> >> Am 14.01.2018 um 21:44 schrieb Neto pr: >> >>> >> >>> Dear all >> >>> >> >>> Someone help me analyze the two execution plans below (Explain ANALYZE >> >>> used), is the query 9 of TPC-H benchmark [1]. >> >>> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS >> >>> 320GB >> >>> 15 Krpm AND SSD Sansung EVO 500GB. >> >>> My DBMS parameters presents in postgresql.conf is default, but in SSD >> >>> I >> >>> have changed random_page_cost = 1.0. >> >>> >> >> you are comparing a SAS Drive against a SATA SSD. Their interfaces >> >> serve a >> >> completely different bandwidth. >> >> While a SAS-3 device does 12 Gbit/s SATA-3 device is only able to >> >> transfer 6 Gbit/s (a current SAS-4 reaches 22.5 Gbit/s) >> >> Do a short research on SAS vs SATA and then use a SAS SSD for >> >> comparison >> >> :) >> > >> > >> > The query being all read operations both drives should perform somewhat >> > similarly. Therefore, either the SAS drive has some special sauce to it >> > (a.k.a very fast built-in cache) or there is something else going on >> > these >> > systems. Otherwise he shouldn't be stressing the 6 Gbit/s interface >> > limit >> > with a single drive, be that the SATA or the SAS drive. >> > >> > Neto, you have been suggested to provide a number of command outputs to >> > know >> > more about your system. Testing the raw read throughput of both your >> > drives >> > should be first on your list. >> > >> >> >> Guys, sorry for the Top Post, I forgot >> >> Fernando, I think the difference of 6 Gb/s to 12 Gb/s from SAS is what >> caused the difference in query execution time. >> Because looking at the execution plans and the cost estimate, I did >> not see many differences, in methods of access among other things. >> Regarding the query, none of them use indexes, since I did a first >> test without indexes. >> Do you think that if I compare the disk below HDD SAS that has a >> transfer rate of 6Gb/s equal to the SSD SATA 6Gb/s, do you think the >> SSD would be more agile in this case? >> >> HDD: HP 450GB 6G SAS 15K rpm LFF (3.5-inch) Part-Number: 652615-B21 >> >> Neto > > > The 6 Gb/s interface is capable of a maximum throughput of around 600 Mb/s. > None of your drives can achieve that so I don't think you are limited to the > interface speed. The 12 Gb/s interface speed advantage kicks in when there > are several drives installed and it won't make a diference in a single drive > or even a two drive system. > > But don't take my word for it. Test your drives throughput with the command > Justin suggested so you know exactly what each drive is capable of: > >> Can you reproduce the speed difference using dd ? >> time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K >> skip=$((128*$RANDOM/32)) # set bs to optimal_io_size > > > While common sense says SSD drive should outperform the mechanical one, your > test scenario (large volume sequential reads) evens out the field a lot. > Still I would have expected somewhat similar results in the outcome, so yes, > it is weird that the SAS drive doubles the SSD performance. That is why I > think there must be something else going on during your tests on the SSD > server. It can also be that the SSD isn't working properly or you are > running an suboptimal OS+server+controller configuration for the drive. Ok. Can you help me to analyze the output of the command: dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size I put a heavy query running in the DBMS and ran the time sudo command ... three times for each environment (SAS HDD and SATA SSD), see below that the SSD had 412,325 and 120 MB/s The HDD SAS had 183,176 and 183 MB/s ... strange that in the end the SAS HDD can execute the query faster ... does it have something else to analyze in the output below? --- SAS HDD 320 Gb 12 Gb/s ==-- root@deb:/etc# time sudo dd if=/dev/sda2 of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size 32768+0 records in 32768+0 records out 34359738368 bytes (34 GB) copied, 188.01 s, 183 MB/s real3m8.473s user0m0.076s sys 0m23.628s root@deb:/etc# time sudo dd if=/dev/sda2 of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size 32768+0 records in 32768+0 records out 34359738368 bytes (34 GB) copied, 195.582 s, 176 MB/s real3m16.304s user0m0.056s sys 0m19.632s root@deb:/etc# time sudo dd if=/dev/sda2 of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size 32768+0 records in 32768+0 records out 34359738368 bytes (34 GB) copied, 187.822 s, 183 MB/s real3m8.457s user0m0.032s sys 0m20.668s root@deb:/etc# --- SATA SSD 500 Gb 6 Gb/s
Re: HDD vs SSD without explanation
On Mon, Jan 15, 2018 at 05:19:59PM -0800, Neto pr wrote:
> >> Can you reproduce the speed difference using dd ?
> >> time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K
> >> skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
> >
> > Still I would have expected somewhat similar results in the outcome, so yes,
> > it is weird that the SAS drive doubles the SSD performance. That is why I
> > think there must be something else going on during your tests on the SSD
> > server. It can also be that the SSD isn't working properly or you are
> > running an suboptimal OS+server+controller configuration for the drive.
>
> Ok.
>
> Can you help me to analyze the output of the command: dd if=/dev/sdX
> of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to
> optimal_io_size
You should run the "dd" without the DB or anything else using the drive. That
gets peformance of the drive, without the DB.
You should probably rerun the "dd" command using /dev/sdb1 if there's an
partition table on top (??).
I'm still wondering about these:
On Sun, Jan 14, 2018 at 09:09:41PM -0600, Justin Pryzby wrote:
> What about sdb partitions/FS?
> > > readahead? blockdev --getra
>
> > > If you're running under linux, maybe you can just send the output of:
> > > for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done
> > > or: tail
> > > /sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size}
Justin
Re: HDD vs SSD without explanation
2018-01-15 17:58 GMT-08:00 Justin Pryzby :
> On Mon, Jan 15, 2018 at 05:19:59PM -0800, Neto pr wrote:
>> >> Can you reproduce the speed difference using dd ?
>> >> time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K
>> >> skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
>> >
>> > Still I would have expected somewhat similar results in the outcome, so
>> > yes,
>> > it is weird that the SAS drive doubles the SSD performance. That is why I
>> > think there must be something else going on during your tests on the SSD
>> > server. It can also be that the SSD isn't working properly or you are
>> > running an suboptimal OS+server+controller configuration for the drive.
>>
>> Ok.
>>
>> Can you help me to analyze the output of the command: dd if=/dev/sdX
>> of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to
>> optimal_io_size
>
> You should run the "dd" without the DB or anything else using the drive. That
> gets peformance of the drive, without the DB.
Oh important observation,..
>
> You should probably rerun the "dd" command using /dev/sdb1 if there's an
> partition table on top (??).
>
> I'm still wondering about these:
See Below:
= SSD SATA 500GB 6 Gb/s
===--
root@hp2ml110deb:/etc# time sudo dd if=/dev/sdb of=/dev/null bs=1M
count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 71.0047 s, 484 MB/s
real1m11.109s
user0m0.008s
sys 0m16.584s
root@hp2ml110deb:/etc# time sudo dd if=/dev/sdb of=/dev/null bs=1M
count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 70.937 s, 484 MB/s
real1m11.089s
user0m0.012s
sys 0m16.312s
root@hp2ml110deb:/etc#
= HDD SAS 300GB 12 Gb/s
===--
root@deb:/home/user1# time sudo dd if=/dev/sda2 of=/dev/null bs=1M
count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 147.232 s, 233 MB/s
real2m27.277s
user0m0.036s
sys 0m23.096s
root@deb:/home/user1#
root@deb:/home/user1# time sudo dd if=/dev/sda2 of=/dev/null bs=1M
count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 153.698 s, 224 MB/s
real2m33.766s
user0m0.032s
sys 0m22.812s
root@deb:/home/user1#
- END
---
I had not spoken, but my SAS HDD is connected to the HBA Controler,
through a SATA adapter, because the cable kit I would have to use and
it would be correct, was no available at the supplier, so it sent the
SAS HDD with a SATA adapter. I found it strange that the speed of SAS
was below the SSD, and even then it can execute the query much faster.
>
> On Sun, Jan 14, 2018 at 09:09:41PM -0600, Justin Pryzby wrote:
>> What about sdb partitions/FS?
>
>> > > readahead? blockdev --getra
>>
>> > > If you're running under linux, maybe you can just send the output of:
>> > > for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done
>> > > or: tail
>> > > /sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size}
>
> Justin
Re: HDD vs SSD without explanation
On 16/01/18 13:18, Fernando Hevia wrote: The 6 Gb/s interface is capable of a maximum throughput of around 600 Mb/s. None of your drives can achieve that so I don't think you are limited to the interface speed. The 12 Gb/s interface speed advantage kicks in when there are several drives installed and it won't make a diference in a single drive or even a two drive system. But don't take my word for it. Test your drives throughput with the command Justin suggested so you know exactly what each drive is capable of: Can you reproduce the speed difference using dd ? time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size While common sense says SSD drive should outperform the mechanical one, your test scenario (large volume sequential reads) evens out the field a lot. Still I would have expected somewhat similar results in the outcome, so yes, it is weird that the SAS drive doubles the SSD performance. That is why I think there must be something else going on during your tests on the SSD server. It can also be that the SSD isn't working properly or you are running an suboptimal OS+server+controller configuration for the drive. I would second the analysis above - unless you see your read MB/s slammed up against 580-600MB/s contunuously then the interface speed is not the issue. We have some similar servers that we replaced 12x SAS with 1x SATA 6 GBit/s (Intel DC S3710) SSD...and the latter way outperforms the original 12 SAS drives. I suspect the problem is the particular SSD you have - I have benchmarked the 256GB EVO variant and was underwhelmed by the performance. These (budget) triple cell nand SSD seem to have highly variable read and write performance (the write is all about when the SLC nand cache gets full)...read I'm not so sure of - but it could be crappy chipset/firmware combination. In short I'd recommend *not* using that particular SSD for a database workload. I'd recommend one of the Intel Datacenter DC range (FWIW I'm not affiliated with Intel in any way...but their DC stuff works well). regards Mark
