Re: HDD vs SSD without explanation - Mailing list pgsql-performance

2018-01-15 3:04 GMT-08:00 Neto pr <netopr9@gmail.com>:
> 2018-01-14 19:09 GMT-08:00 Justin Pryzby <pryzby@telsasoft.com>:
>> 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