Page size has always been a grey area and we have never risked pushing above 4k although that said we haven't revisited for a number of years and things are remarkable different these days what with SSD arrays, IOPS are through the roof, plus the amount of RAM typically allocated to servers.

If we have a large (in our case) db file, say 750GB which has maybe 50 tables, some of which hold just 4 or 5 columns of small string data, others which hold large binary PDF data and XML content in the 100,000,000's of rows should we be looking at using a larger page size?

asked 02 May '23, 12:53

pcollins's gravatar image

pcollins
1913612
accept rate: 0%


See my answer to this FAQ. The size of your database as you describe it should not be an issue so you should likely simply pick a database page size equal to the file system sector size (which should be set to the underlying raw allocation page size). Even though SSDs are fast, it is still best to match the sizes across all three layers.

However, see my note at the end of the FAQ (last two paragraphs): ie. "It depends"! You have not described your work load - rate of inserts, updates, or deletes on the various tables - so you may want to consider doing some performance testing using your specific workload mix and as a result choose a database page size which is twice the size of the file system sector size. FWIW I doubt you will see much of a difference if you are using an SSD.

permanent link

answered 02 May '23, 22:52

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

Thanks Mark, I had read your post although I was curious if things had changed given hardware performance increases but I guess not.

(03 May '23, 02:43) pcollins

Any plans to support bigger page sizes than 32k in the future?

(29 Jan, 03:05) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14

question asked: 02 May '23, 12:53

question was seen: 908 times

last updated: 29 Jan, 03:05