|
Hi All, SA 17.0.10.5866 for WIndows Is it possible to use databases with different page sizes under one server ? Or is it better to bring all databases to the same page size ? And another small question. The documentation for SA 17.0.10 says: "File size for NTFS: 2 TB for 8 KB pages" And what is the size limit for a file with a page of 16 kb or 32 kb ? |
|
It's possible to run databases with different page sizes in the same server but it's best not to do so. The database cache is made up of fixed-size buffers and each one can only hold one database page. If the database's page size is smaller than the cache's page size, you waste memory. IF the the database's page size is larger than the cache's page size, you cannot start the database. The cache page size is configurable with the '-gp' switch or automatically adjusted based on databases specified at startup. So, suppose you want to load two databases and one uses 32K page size and the other uses 4K page size so you set the cache page size to 32K. When a 4K page is stored in the cache, it will still take up 32K -- wasting 28K of memory. File size limit is 2^28 * pagesize (assume the underlying filesystem support files of that size). So 16K page size has a max file size of 4TB, 32K page size has a max file size of 8TB. Also remember that you have a total of 12 dbspaces that you can use. Your answer is clear. But I want to clarify it as applied to my situation: My database has a page size of 4096 and its size has almost reached 1 TB. And I'm afraid that soon the server will start to report exceeding the maximum size of the database file. Are there any solutions to this problem besides re-creating the database with a large page size ? And what if, the main size of this database depends on one table, in which archives with documents are stored in the BLOB field, after all, one table cannot be in several dbspace or can it ?
(29 Apr '20, 04:52)
Stalker
Replies hidden
A single table can only be in one dbspace and you are approaching a 1TB limit. Changing database page size is probably the most straight-forward option. Anything else would require some architecture changes to your schema and/or applications. For example, compressing the blobs (if not already done), using multiple tables/multiple dbspaces (such as via surrogate keys to blobs stored in other tables), etc.
(29 Apr '20, 05:03)
John Smirnios
As a workaround, you might also just reload the database to see if the new database is significantly smaller, say because currently rows are split over several pages because they have been updated and could fit in one single page when freshly inserted. That might help to get along with 4K pages for a while. See here for some queries on extension pages... I would check that before doing a reload.
(29 Apr '20, 13:17)
Volker Barth
1
We faced a similar problem where databases were growing rapidly due to the volume of documents attached to records - 90% and more of the database ending up in a single table. The approach we took is to have an option to store the actual files outside the database in a file structure that is managed by database procedures. The Client apps are unaware of the storage method as both internal and externally held blobs are saved and retrieved through the procedures in exactly the same way. The external (and internal) blobs are stored zipped. We use a dedicated OS user account to run the database service and only that user account has access to the file structure. After doing this our largest database was reduced to around 200GB but now has approaching 4TB of compressed blobs. Performance-wise, things are much the same except that database back-ups etc are much faster. We have written some automated validation and management functions to check that the files are consistent with the database records.
(30 Apr '20, 06:09)
Justin Willey
|
