Re: HDD vs SSD without explanation

2018-01-14 19:09 GMT-08:00 Justin Pryzby <pryzby(at)telsasoft(dot)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(at)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