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