Hi Clemens,
We checked and there are no faulty sectors.
I do have some information on the disks, both are SSD's. Supposed that
it is a disk problem, then the problematic and non problematic ones are
as below (not sure if this then still has to be in the sqlite mailing
list).
I'm also going to try the query tonight in the fifteen minutes that
we're not inserting records into the table.
Thanks!
Gunnar
The problematic one:
/dev/sdb:
ATA device, with non-removable media
Model Number: INTEL SSDSC2BB240G4
Serial Number: BTWL342202LD240NGN
Firmware Revision: D2010355
Media Serial Num:
Media Manufacturer:
Transport: Serial, ATA8-AST, SATA 1.0a, SATA II
Extensions, SATA Rev 2.5, SATA Rev 2.6
Standards:
Used: unknown (minor revision code 0x0029)
Supported: 8 7 6 5
Likely used: 8
Configuration:
Logical max current
cylinders 16383 16383
heads 16 16
sectors/track 63 63
--
CHS current addressable sectors: 16514064
LBA user addressable sectors: 268435455
LBA48 user addressable sectors: 468862128
Logical Sector size: 512 bytes
Physical Sector size: 512 bytes
Logical Sector-0 offset: 0 bytes
device size with M = 1024*1024: 228936 MBytes
device size with M = 1000*1000: 240057 MBytes (240 GB)
cache/buffer size = unknown
Form Factor: 2.5 inch
Nominal Media Rotation Rate: Solid State Device
Capabilities:
LBA, IORDY(can be disabled)
Queue depth: 32
Standby timer values: spec'd by Standard, no device specific minimum
R/W multiple sector transfer: Max = 1 Current = 1
DMA: mdma0 mdma1 mdma2 udma0 udma1 udma2 udma3 udma4 udma5 *udma6
Cycle time: min=120ns recommended=120ns
PIO: pio0 pio1 pio2 pio3 pio4
Cycle time: no flow control=120ns IORDY flow control=120ns
Commands/features:
Enabled Supported:
* SMART feature set
Security Mode feature set
* Power Management feature set
* Write cache
* Look-ahead
* Host Protected Area feature set
* WRITE_BUFFER command
* READ_BUFFER command
* NOP cmd
* DOWNLOAD_MICROCODE
SET_MAX security extension
* 48-bit Address feature set
* Mandatory FLUSH_CACHE
* FLUSH_CACHE_EXT
* SMART error logging
* SMART self-test
* General Purpose Logging feature set
* WRITE_{DMA|MULTIPLE}_FUA_EXT
* 64-bit World wide name
* IDLE_IMMEDIATE with UNLOAD
* WRITE_UNCORRECTABLE_EXT command
* {READ,WRITE}_DMA_EXT_GPL commands
* Segmented DOWNLOAD_MICROCODE
unknown 119[6]
* Gen1 signaling speed (1.5Gb/s)
* Gen2 signaling speed (3.0Gb/s)
* unknown 76[3]
* Native Command Queueing (NCQ)
* Phy event counters
* unknown 76[15]
* Software settings preservation
* SMART Command Transport (SCT) feature set
* SCT LBA Segment Access (AC2)
* SCT Error Recovery Control (AC3)
* SCT Features Control (AC4)
* SCT Data Tables (AC5)
* Data Set Management TRIM supported
* reserved 69[4]
* reserved 69[5]
* Deterministic read after TRIM
Security:
Master password revision code = 65534
supported
not enabled
not locked
not frozen
not expired: security count
supported: enhanced erase
2min for SECURITY ERASE UNIT. 2min for ENHANCED SECURITY ERASE UNIT.
Logical Unit WWN Device Identifier: 55cd2e404b4ee650
NAA : 5
IEEE OUI : 5cd2e4
Unique ID : 04b4ee650
Checksum: correct
And the not problematic one:
/dev/sdb:
ATA device, with non-removable media
Model Number: INTEL SSDSC2BB300G4
Serial Number: BTWL3315087F300PGN
Firmware Revision: D2010355
Media Serial Num:
Media Manufacturer:
Transport: Serial, ATA8-AST, SATA 1.0a, SATA II
Extensions, SATA Rev 2.5, SATA Rev 2.6
Standards:
Used: unknown (minor revision code 0x0029)
Supported: 8 7 6 5
Likely used: 8
Configuration:
Logical max current
cylinders 16383 16383
heads 16 16
sectors/track 63 63
--
CHS current addressable sectors: 16514064
LBA user addressable sectors: 268435455
LBA48 user addressable sectors: 586072368
Logical Sector size: 512 bytes
Physical Sector size: 512 bytes
Logical Sector-0 offset: 0 bytes
device size with M = 1024*1024: 286168 MBytes
device size with M = 1000*1000: 300069 MBytes (300 GB)
cache/buffer size = unknown
Form Factor: 2.5 inch
Nominal Media Rotation Rate: Solid State Device
Capabilities:
LBA, IORDY(can be disabled)
Queue depth: 32
Standby timer values: spec'd by Standard, no device specific minimum
R/W multiple sector transfer: Max = 1 Current = 1
DMA: mdma0 mdma1 mdma2 udma0 udma1 udma2 udma3 udma4 udma5 *udma6
Cycle time: min=120ns recommended=120ns
PIO: pio0 pio1 pio2 pio3 pio4
Cycle time: no flow control=120ns IORDY flow control=120ns
Commands/features:
Enabled Supported:
* SMART feature set
Security Mode feature set
* Power Management feature set
* Write cache
* Look-ahead
* Host Protected Area feature set
* WRITE_BUFFER command
* READ_BUFFER command
* NOP cmd
* DOWNLOAD_MICROCODE
SET_MAX security extension
* 48-bit Address feature set
* Mandatory FLUSH_CACHE
* FLUSH_CACHE_EXT
* SMART error logging
* SMART self-test
* General Purpose Logging feature set
* WRITE_{DMA|MULTIPLE}_FUA_EXT
* 64-bit World wide name
* IDLE_IMMEDIATE with UNLOAD
* WRITE_UNCORRECTABLE_EXT command
* {READ,WRITE}_DMA_EXT_GPL commands
* Segmented DOWNLOAD_MICROCODE
unknown 119[6]
* Gen1 signaling speed (1.5Gb/s)
* Gen2 signaling speed (3.0Gb/s)
* unknown 76[3]
* Native Command Queueing (NCQ)
* Phy event counters
* unknown 76[15]
* Software settings preservation
* SMART Command Transport (SCT) feature set
* SCT LBA Segment Access (AC2)
* SCT Error Recovery Control (AC3)
* SCT Features Control (AC4)
* SCT Data Tables (AC5)
* Data Set Management TRIM supported
* reserved 69[4]
* reserved 69[5]
* Deterministic read after TRIM
Security:
Master password revision code = 65534
supported
not enabled
not locked
not frozen
not expired: security count
supported: enhanced erase
2min for SECURITY ERASE UNIT. 2min for ENHANCED SECURITY ERASE UNIT.
Logical Unit WWN Device Identifier: 55cd2e404b422218
NAA : 5
IEEE OUI : 5cd2e4
Unique ID : 04b422218
Checksum: correct
On 09/25/2015 03:58 PM, gunnar wrote:
> Hi Clemens,
>
> Here are some of the settings and the integrity check that we always
> prints at start up of our process:
>
> [query:PRAGMA synchronous=OFF][changes:0][total changes:0]
> [query:PRAGMA foreign_keys=ON][changes:0][total changes:0]
> [query:PRAGMA cache_size=10000][changes:0][total changes:0]
> [query:PRAGMA journal_mode=WAL][changes:0][total changes:0]
> OrderCallbackStorage::checkDatabaseIntegrity: row 1 [ok]
>
>
> No virtualization and also no network file system.
>
> What do you exactly mean with "But in any case, as others have already
> said, it is not possible for a write transaction to lock out a read
> transaction _in the middle_."? I do see that records are being
> inserted while I made those stack traces.
>
> I have a fifteen minute window / 24hours, is it enough for VACUUM? the
> database file and wal file are at the moment around 700KiB and 500KiB
> resp. Probably that can't be answered and I should just try it.
>
> We'll check the disk for bad sector(s).
>
>
> Thanks for your help!
> Gunnar
>
>
>
> On 09/25/2015 03:40 PM, Clemens Ladisch wrote:
>> gunnar wrote:
>>> (select uuid from session where date = (select max(date) from session))
>> This can be optimized to
>> (select uuid from session order by date desc limit 1)
>> but the speed of this subquery does not matter.
>>
>>> (SELECT max(cb_seq_num) FROM ordercallback WHERE
>>> server_order_id=cb.server_order_id AND sessionuuid=cb.sessionuuid
>>> AND working=1)
>>> 3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3
>>> (server_order_id=? AND sessionuuid=? AND working=?)
>> You could try to speed this up with a covering index by adding the
>> cb_seq_num column to the index.
>>
>>
>> But in any case, as others have already said, it is not possible for
>> a write transaction to lock out a read transaction _in the middle_.
>>
>> Are you using WAL? Some network file system? Virtualization?
>>
>> If neither the CPU nor the disk are busy, but SQLite is not sleeping,
>> then what is it waiting for? This sounds like a defective disk sector.
>>
>> Try running "PRAGMA integrity_check" on (a copy of) the DB.
>> Try VACUUM.
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
