4 ms·

Extended Statistics! I was following the replication changes, but have just discovered the extended statistics and am more excited about them. The directory re

Extended Statistics! I was following the replication changes, but have just discovered the extended statistics and am more excited about them. The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this?

anarazel 10y ago
> The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this? The background is that, over the years, a number of people deleted the pg_xlog and pg_clog directories when they noticed they're running out of space, thinking it's just server logs. Unfortunately that's the directories containing the database journal, and transaction status (committed/aborted/in-progress). Which means they'll loose data. The idea is to rename them to something that's less likely to be mistaken for unimportant data.
JoachimSchipper 10y ago
Having good directory names helps a lot; I'd be very surprised if other projects didn't also need to make it clear to admins what's happening. (On the other hand, some projects need to make things _less_ clear - https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db733dd38f96896cd825f/src/os.h#L43 https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db... - "users would (...) call [the developers] to wake them up at night and complain".)
frik 10y ago
It would be great if some Linux distros clear up the directory mess. There are directories in there with names that no ones remembers what they originally meant in the UNIX of 1970s, or what ever. For compatibility they could be just hard/soft-links to a more sane directory structure. Well the same goes for Windows. With Win95, WinNT 3.5, WinXP, WinVista they restructured the internal directory tree and renamed things. It was okay with WinXP, just the long user folder was trouble some because of 260 chars MAX_PATH limit. But with Vista and 64-bit support the fucked up and it's now a big mess in Win7+ (syswow64, system32, registry, winsxs, dotNet folders, ... such a big mess and sometimes also waste of HDD space by duplicates of files).
- barrkel 10y ago
  winsxs uses hard links - space wastage is more likely from more versions than just dupes. Also, many windows tools won't account t correctly for hard links in disk usage stats.
pgaddict 10y ago
To be fair, the extended statistics available in PG10 are about the most primitive ones possible. If the PG10 stats help with your queries, great, but otherwise it's mainly laying infrastructure for the more advanced stuff - histograms, MCV lists, expression statistics, and ultimately join statistics (which is about the main source of estimation issues). Oleg also mentioned it might be useful for JSON statistics, which would be cool. Hopefully those bits will get in faster - I first submitted the patch in 2014, just before pgconf.eu, I think. OTOH I can't really complain, because I'm shitty developer so the initial versions were far from committable. The quality requirements for PostgreSQL patches are damn high these days. BTW if you have examples of real-world queries hurt by poor estimates, report them to pgsql-performance mailing list. It's an important piece of information about what cases to look at first. Obviously, we already have already collected various queries, but having more is good.

anarazel 10y ago

> The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this? The background is that, over the years, a number of people deleted the pg_xlog and pg_clog directories when they noticed they're running out of space, thinking it's just server logs. Unfortunately that's the directories containing the database journal, and transaction status (committed/aborted/in-progress). Which means they'll loose data. The idea is to rename them to something that's less likely to be mistaken for unimportant data.

JoachimSchipper 10y ago

Having good directory names helps a lot; I'd be very surprised if other projects didn't also need to make it clear to admins what's happening. (On the other hand, some projects need to make things _less_ clear - https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db733dd38f96896cd825f/src/os.h#L43 https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db... - "users would (...) call [the developers] to wake them up at night and complain".)

frik 10y ago

It would be great if some Linux distros clear up the directory mess. There are directories in there with names that no ones remembers what they originally meant in the UNIX of 1970s, or what ever. For compatibility they could be just hard/soft-links to a more sane directory structure. Well the same goes for Windows. With Win95, WinNT 3.5, WinXP, WinVista they restructured the internal directory tree and renamed things. It was okay with WinXP, just the long user folder was trouble some because of 260 chars MAX_PATH limit. But with Vista and 64-bit support the fucked up and it's now a big mess in Win7+ (syswow64, system32, registry, winsxs, dotNet folders, ... such a big mess and sometimes also waste of HDD space by duplicates of files).

barrkel 10y ago
winsxs uses hard links - space wastage is more likely from more versions than just dupes. Also, many windows tools won't account t correctly for hard links in disk usage stats.

pgaddict 10y ago

To be fair, the extended statistics available in PG10 are about the most primitive ones possible. If the PG10 stats help with your queries, great, but otherwise it's mainly laying infrastructure for the more advanced stuff - histograms, MCV lists, expression statistics, and ultimately join statistics (which is about the main source of estimation issues). Oleg also mentioned it might be useful for JSON statistics, which would be cool. Hopefully those bits will get in faster - I first submitted the patch in 2014, just before pgconf.eu, I think. OTOH I can't really complain, because I'm shitty developer so the initial versions were far from committable. The quality requirements for PostgreSQL patches are damn high these days. BTW if you have examples of real-world queries hurt by poor estimates, report them to pgsql-performance mailing list. It's an important piece of information about what cases to look at first. Obviously, we already have already collected various queries, but having more is good.