SAW 17: Question on the system load of file handling functions (xp_write_file, xp_read_file) ?

[Stalker4]

Hi,

SAP SQL AnyWhere 17.0.10 (8 cores), Windows Server 2016.

There's a large base:
- size - approximately 6 TB
- number of simultaneous connections - 300-500

The main size of the database falls on the texts of documents (word, excel, pdf, pictures), which are stored in the database itself (fields of long binary type), in separate tables located in separate tablespaces.

Users actively write to the database or read from the database, including quite active reading/writing of document texts. This creates quite a heavy load on the disk subsystem, which in turn leads to periodically noticeably lower responsiveness of the database server itself.

I have an option to change the way I store document texts, from storing them in the database to storing them in the file system (NTFS). In this case I will save them to the server using the system function xp_write_file, and read them using xp_read_file.

But this raises some questions: How will xp_write_file and xp_read_file behave under heavy load (when a couple hundred document files can be requested at the same time as several dozen other document files are being written) ?

Will transferring the storage of texts of documents from the database to the file system reduce the load on the disk subsystem, and if so, by how much ? and if yes, by how much ?

[VolkerBarth]

Hm, I cannot imagine that anyone without knowing your system, environment and use cases can answer these questions – other than you, doing some tests on your own.

[Stalker4]

I understand that for a precise (complete) answer, you need to know my system well and conduct tests on it.

But I counted on the answer in the style - I also had a database in which directly stored the texts of documents, but I redesigned it to store the texts of documents in the form of external files on the disk subsystem, now I read/write them using functions xp_read_file/xp_write_file and here are the pros/cons of such redesign I noticed ...

Or alternatively general guidelines for using the file functions xp_read_file/xp_write_file ...

[johnsmirnios]

Much will depend on the size of the files you are read/writing and what you are doing with them. Transactional semantics are also important to consider. Here are some things to think about: 

xp_write_file just writes a file sequentially in 32K chunks using POSIX IO. That means that the data written will also go into your filesystem cache. There are scenarios where that could be better for you (lots of memory to spare / db server is not using almost all memory) or worse (filesystem cache is competing with the database server for memory).

xp_read_file: The same competition for memory mentioned for xp_write_file also applies. If not using 'lazy' mode, it immediately reads the whole file into a database string, 32K at a time, using buffered IO. 
With 'lazy', we use something that we call 'ropes' that will only pull in the data that is needed when it is needed. For example, if you ask for a SUBSTR starting at offset 1,000,000, we don't read the first 1,000,000 bytes

read_server_file is like xp_read_file but always uses lazy mode and allows you to specify a 64-bit start offset and max length. Essentially, that's a built-in SUBSTR.

When storing the file contents in the database (eg, INSERT), that data will be removed if the transaction rolls back. When storing the content externally, you might get orphaned files. For example: eg, xp_write_file( 'foo.dat', 'blob' ); INSERT INTO known_files( 'foo.dat' ); ROLLBACK". You also may need to deal with cases where someone deletes a known file when the database is still tracking it, disk full situations, etc.

When a blob is in the database, you might benefit from the fancy async writes we do. It's really hard to tell without specifics about the application. At a guess, if you blobs are really small it might just be easier to keep them in the database. If they are enormous, it might be easier to keep them outside - particularly if your application already uploads them to a managed location and you don't need to duplicate the data into a database. In between those extremes, the scales will tip a some point -- possibly even at multiple points.

[Stalker4]

Files of various sizes, ranging from 500 KB to 20-40 MB, are stored in the database. The average size of a file stored in the database is about 3 MB.

This is a document management database, and document cards are stored in the database (a card consists of a number of fields that are stored in linked tables - master-detail). These document texts are stored in a separate table located in a separate table space in the BLOB field.

Simply put, some users save these document texts to the database, while other users download these document texts from the database to the client's computer for viewing, if necessary.

In this situation, I am interested in the load on the database server in the context of writing/reading these files from (to) BLOB by a large number of users (100-400 simultaneous connections, although not all of these users necessarily view these document texts).

Which is less resource-intensive for the database server: loading these files from the table from the BLOB field or reading them from the file system via xp_read_file and then returning them to the user ?