Public
You ever wonder what the most optimal block size for a database system on a spindle drive or SSD was?
ANSWER: In every case, regardless of drive type, filesystem block size or underlying filesystem it looks to be: 4KB
In every test of PostgreSQL executed on 24 different filesystem configurations (BTFS, XFS, EXT2, EXT3, etc.) all with varying filesystem block sizes, the PostgreSQL configurations using 4KB blocks to read/write table data always performed the fastest.
http://goo.gl/cfYpD
UPDATE: Use this link if you are having 414 problems (click Check All, then Compare at bottom):
http://www.fuzzy.cz/bench/index.php?type=pgbench
ANSWER: In every case, regardless of drive type, filesystem block size or underlying filesystem it looks to be: 4KB
In every test of PostgreSQL executed on 24 different filesystem configurations (BTFS, XFS, EXT2, EXT3, etc.) all with varying filesystem block sizes, the PostgreSQL configurations using 4KB blocks to read/write table data always performed the fastest.
http://goo.gl/cfYpD
UPDATE: Use this link if you are having 414 problems (click Check All, then Compare at bottom):
http://www.fuzzy.cz/bench/index.php?type=pgbench
View 5 previous comments
+Riyad Kalla , he means the page size set by the OS. On most x86 systems it's 4KB, but support for larger page sizes exists (you can pick it as a compile option in the Linux kernel). More info: http://lwn.net/Articles/374424/Jan 22, 2012
+Riyad Kalla Basically, your operating system will optimize all memory access based on a certain unit where the translation of virtual to physical addresses is handled. Common *nix operating systems will use 4KB pages by default. If you remove the disk I/O from the equation the DBMS will basically stream memory from a secondary storage to main memory and from there to CPU. If the access is not aligned (e.g. by memory page boundary) additional penalties will occur. So, it could be interesting to look at different memory page sizes as another dimension of the problem.Jan 22, 2012
That link returns the 414 error for me as well. I am using a proxy called squid with squidguard and havp antivirus on my pfsense firewall. Temporarily disabling it has solved the problem. I'll maybe poke around with the settings a little.Jan 22, 2012
+Mike O'Neill and others getting 4xx's -- the original link is here: http://www.fuzzy.cz/bench/index.php?type=pgbench
Just check all or the ones you want and hit "Compare" at the bottom to get a customized view.
Hope that helps!Jan 23, 2012
Like in many Google services (including notably Search), the links shown on Google+ are not the direct links you would think they are. Often, they are links to Google servers with the original URL passed as a parameter, where the hits are tallied before your browsers is silently redirected to the original URL.
In the present 414 error case, the Google servers are refusing to handle this huge URL parameter, but the URL itself is just fine. If you try again (maybe reloading the page), you should get a raw URL that works. Right-clicking and copy-pasting the URL in a new browser tab should work too.Jan 23, 2012
Graphs would be great if the wouldn't be missing the units everywhere.Jan 23, 2012
Add a comment...