From: Justin Pryzby <pryzby@telsasoft.com>
To: Neto pr <netopr9@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: HDD vs SSD without explanation
Date: Sun, 14 Jan 2018 21:09:41 -0600
Message-ID: <20180115030941.GG32403@telsasoft.com> (raw)
In-Reply-To: <CA+wPC0M3ez3bnsg+kCXcc7czUkJX5v1kUCneaaKUujJwMLkEzQ@mail.gmail.com> <CA+wPC0OJzVq8H5UABkH2+gC11a8P6EHFWWp=1Fs0qEauMaoU_g@mail.gmail.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?
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 ?
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
> > 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, netopr9@gmail.com
Subject: Re: HDD vs SSD without explanation
In-Reply-To: <20180115030941.GG32403@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox