1

Topic: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

2

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog;
The simple answer yes, but that you disturbs a question smile

3

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog;
https://www.sqlskills.com/blogs/jonatha … with-ssds/

4

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

aleksrov, thanks. Article is mandatory I esteem.
TaPaK, I so understand. The defragmentation of indexes cites to that, the data on a disk, concerning one index, are allocated sequentially, as though by "one piece". Therefore at their reading with HDD the mechanical head will not run from the track on the track, from disk edge on edge, and sequentially to read out one track and to pass to the following. I.e. for HDD we, first of all, save time for time of driving of a head. In SSD there is no mechanical relocation, therefore, as it seems to me, the defragmentation of indexes any more so is basic.

5

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog;
the link laziness to open? Except reading there is still a place (for SSD it $) and an amount of operations.

6

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog wrote:

In SSD there is no mechanical relocation, therefore, as it seems to me, the defragmentation of indexes any more so is basic.

the Fragmentation happens logical (exterior) and physical (internal).
You, in the reasonings, do not consider physical which always leads to magnification of logical reads at scanning.

7

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

The table - a broad gull of events. An index with fill_factor = 100. Events are written with sequentially increasing id and datetime. Changes in the table (update, delete) it is not produced.

8

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog wrote:

the Table - a broad gull of events. An index with fill_factor = 100. Events are written with sequentially increasing id and datetime. Changes in the table (update, delete) it is not produced.

Here other question is more pertinent:
Whether there is a sense to do a defragmentation of indexes if fragmentations practically are not present?
And that here still a good question: whether it is necessary to defragment the table right after truncate? (-33)

9

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog;
Whether

wrote:

There is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

wrote:

the Table - a broad gull of events. An index with fill_factor = 100. Events are written with sequentially increasing id and datetime. Changes in the table (update, delete) it is not produced.

Sailors do not have questions more...

10

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

For the information too most that under the link but instead of SSD basis on HDD, for comparing
' async_io_completed ', ' GuidHighFragmentation ', 149,
' async_io_requested ', ' GuidHighFragmentation ', 149,
' file_read ', ' GuidHighFragmentation ', 149,
' file_read_completed ', ' GuidHighFragmentation ', 149,
' physical_page_read ', ' GuidHighFragmentation ', 771,
' async_io_completed ', ' GuidLowFragmentation ', 3,
' async_io_requested ', ' GuidLowFragmentation ', 3,
' file_read ', ' GuidLowFragmentation ', 3,
' file_read_completed ', ' GuidLowFragmentation ', 3,
' physical_page_read ', ' GuidLowFragmentation ', 451

11

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

The registrar;
QueryTable, read_size_kb, read_size_pages, occurences
' GuidHighFragmentation ', 104, 13, 1,
' GuidHighFragmentation ', 88, 11, 1,
' GuidHighFragmentation ', 80, 10, 4,
' GuidHighFragmentation ', 72, 9, 8,
' GuidHighFragmentation ', 64, 8, 27,
' GuidHighFragmentation ', 56, 7, 12,
' GuidHighFragmentation ', 48, 6, 12,
' GuidHighFragmentation ', 40, 5, 13,
' GuidHighFragmentation ', 32, 4, 18,
' GuidHighFragmentation ', 24, 3, 23,
' GuidHighFragmentation ', 16, 2, 27,
' GuidHighFragmentation ', 8, 1, 3,
' GuidLowFragmentation ', 3288, 411, 1,
' GuidLowFragmentation ', 256, 32, 1,
' GuidLowFragmentation ', 64, 8, 1

12

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

SSD the disk uses algorithms of uniform "dispersion" of record on chips of non-volatile storage what to prolong disk service life. Unconditionally, read operations and records for  tables on SSD a disk will be faster fulfilled, than on a hard disk. That Mahlo, on SSD a disk (the majority of applications) can to pay attention to a file fragmentation, i.e. now it is possible to allocate safely on one disk (read - an array) some data files and that was considered as "terry" sedition,  logs earlier.
The nature of a fragmentation of data pages and lines on pages is a bit another. Such fragmentation leads to magnification of number of input-output operations irrespective of the location of pages. From here it is quite logical that compact storage will be more effective than search and  the laminated pages. However, the scoring will not be such big, as on hard disks as the addressings of page built in strict sequence on SSD a disk physically will be scattered on all physical space of a disk. Practice shows that requirements to level of a fragmentation for SSD essentially decrease.

13

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

wrote:

now it is possible to allocate safely on one disk (read - an array) some data files and that was considered as "terry" sedition,   logs

earlier
And what  in respect of survivability?
If the disk dies, and on it both the data, and log all the same die all at once.
And if the data from logs separated;
The first something would die one.
And if it is the data then it is possible to remove tail of the log backup and to be recovered without loss of the data.
So I refuse to understand, what for it is broad gulls dataful on one disk to push.
Nevertheless not at all AWAG, truth?

14

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

*AOAG

15

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

o-o;
imho, here only about productivity and a defragmentation, about survivability speech did not go

16

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Alexander Gladchenko, thanks. Everything that you wrote, I did not know, therefore for me it is very valuable information.
o-o, at us files dataful and the transaction log file are allocated on different arrays.
But then there were new questions.
Array RAID5 from 8 SSD. We will not carp to RAID5, but we have mean values: for Avg. Disk Queue Length - 121, Avg. Disk sec/Transfer - 0,055.
By analogy with HDD it is divisible 121 on 8 it is received ~15 that much more 2.
1 a question.
How much for SSD  division Avg. Disk Queue Length on kol-in disks in  and comparing with 2? Managers state that for SSD such method is not necessary also digit 121 it quite normally.
2 a question.
For arrays HDD disks were much more  on record/reading, than buses/channels on which the data on/with a disk was transferred. Therefore the magnification of an amount of disks in an array led magnification of speed of record/reading from a disk.
For fast SSD as it seems to me, buses/channels can already appear a bottleneck. And, therefore, the magnification of an amount of disks in an array does not lead to productivity growth.
Whether if any limit on amount SSD of disks for PCIe and optical fibers 2-16 Gbps after which the magnification of an amount of disks in an array does not give an increase in speed?

17

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog wrote:

For fast SSD as it seems to me, buses/channels can already appear a bottleneck. And, therefore, the magnification of an amount of disks in an array does not lead to productivity growth.
Whether if any limit on amount SSD of disks for PCIe and optical fibers 2-16 Gbps after which the magnification of an amount of disks in an array does not give an increase in speed?

So even for not fast  - speed of record of the order of 500 MB in second, or 4 Gigabits per second.
So an optical fiber in 2 Gigabits already in 2 times already a disk.
And 8 disks in RAID10 - are already wider, than PCIex
Besides that the modern disks already closely come nearer to gigabyte a second on record/reading.

18

Re: Whether there is a sense to do a defragmentation of the indexes allocated on an array from SSD of disks?

Prolog wrote:

Array RAID5 from 8 SSD. We will not carp to RAID5, but we have mean values: for Avg. Disk Queue Length - 121, Avg. Disk sec/Transfer - 0,055.

And on what was specific loading (reading, record) such queue?
[quote =] 2 a question.
For arrays HDD disks were much more  on record/reading, than buses/channels on which the data on/with a disk was transferred. Therefore the magnification of an amount of disks in an array led magnification of speed of record/reading from a disk.
For fast SSD as it seems to me, buses/channels can already appear a bottleneck. And, therefore, the magnification of an amount of disks in an array does not lead to productivity growth.
Whether if any limit on amount SSD of disks for PCIe and optical fibers 2-16 Gbps after which the magnification of an amount of disks in an array does not give an increase in speed?

There is an emphasis in RAID - the controler, more precisely, in it  (which considers parity). For example, controlers SAS 6G in the majority on amount SSD over 4 noticeable growth of productivity in similar arrays (RAID5, RAID6) did not give. And that first generation (LSI 2108, for example - everyones MegaRAID 9260/9280 and others) - and on RAID10 too. That is - yes if at you the old RAID-controler - the emphasis can be in it.
Not in channels/tyres (SATA SSD in the majority all the same it is more 540-550  on reading and 500 on record do not give) - namely in the controler.
And on the contrary, being connected to  on FC you simply import additional (though also almost imperceptible in a case with FC) latency: the interface to disks from controlers at all of them it is equal SAS, and at the majority - 6G, 12G have only rather new models.
Well and cost SAS SSD for  will be more, than is noticeable.