Closed Bug 1038505 Opened 10 years ago Closed 10 years ago

A few changes we could do to make our postgres CLI more human readable?

Categories

(Data & BI Services Team :: DB: MySQL, task)

x86
macOS
task
Not set
normal

Tracking

(Not tracked)

RESOLVED WONTFIX

People

(Reporter: bjohnson, Unassigned)

Details

(Whiteboard: [data: server optimization])

I had to do some query debugging in our postgres environment today on live queries due to high load on socorro1. It was pretty difficult due to a few stock psql CLI setbacks. I'd like to seek the team's feedback on adding somethings to our postgres environment to make live query debugging easier. If you have an easier way of doing this, please feel free to inform me. ~/.psqlrc settings - \pset pager off - removes the "MORE" from showing up every time you run a query with a large resultset. - \timing - turns time to execute for queries on. - \pset format unaligned - sets the format to something more human readable. well formatted queries can tend to be very unreadable when using format aligned. Please let me know if you have any objections or better alternatives. I find these things are very helpful (at least for me). If I need to set this in my own psql as you guys don't like it I'd be happy to. But I feel they're a good baseline for psql console.
Flags: needinfo?(scabral)
Flags: needinfo?(mpressman)
We should also raise the postgres variable "track_activity_query_size", it would allow pg_stat_activity to show the full text for longer queries. Right now longer queries tend to be truncated. (kind of the equivalent to show processlist vs show full processlist).
with "\pset pager off", does it just remove "MORE" for larger results, or does it eliminate the pager entirely? that gives it a more MySQL feel, that all results regardless of length get displayed on screen with no paging. I think the postgres default is saner, to be honest. I'm fine with the timing. Can you give me an example of what you mean with the formatting?
Flags: needinfo?(scabral)
\pset pager off turns off the pager. The pager can be set via environment variable to define what program is used as the pager. We do not define the pager, and therefore it uses the system default pager, which is MORE. So in effect, it's only turning MORE off. As for syntax of aligned vs unaligned, below is the same query (structure, not parameters) of query record from pg_stat_activity in aligned and unaligned format. aligned (larger and/or multi-lined queries break aligned structure) - 16408 | breakpad | 2518 | 16385 | breakpad_rw | | | | -1 | 2014-07-15 16:35:32.365872+00 | 2014-07-15 16:35:32.368074+00 | 2014-07-15 16:35:32.368992+00 | 2014-07-15 16:35:32.369002+00 | f | active | + | | | | | | | | | | | | | | | /* socorro.external.postgresql.correlations.Correlations.get(modules)*/ + | | | | | | | | | | | | | | | WITH total_for_sig AS ( + | | | | | | | | | | | | | | | SELECT + | | | | | | | | | | | | | | | sum(total) + | | | | | | | | | | | | | | | FROM correlations_module + | | | | | | | | | | | | | | | JOIN product_versions USING (product_version_id) + | | | | | | | | | | | | | | | JOIN signatures USING (signature_id) + | | | | | | | | | | | | | | | WHERE report_date = '2014-07-13T00:00:00+00:00'::timestamptz + | | | | | | | | | | | | | | | AND product_name = 'Firefox' + | | | | | | | | | | | | | | | AND os_name = 'Windows' + | | | | | | | | | | | | | | | AND version_string = '31.0' + | | | | | | | | | | | | | | | AND signature = 'js::gc::IsAboutToBeFinalized<js::gc::Cell>' + | | | | | | | | | | | | | | | ), + | | | | | | | | | | | | | | | total_for_os AS ( + | | | | | | | | | | | | | | | SELECT + | | | | | | | | | | | | | | | sum(total) + | | | | | | | | | | | | | | | FROM correlations_module + | | | | | | | | | | | | | | | JOIN product_versions USING (product_version_id) + | | | | | | | | | | | | | | | WHERE report_date = '2014-07-13T00:00:00+00:00'::timestamptz + | | | | | | | | | | | | | | | AND product_name = 'Firefox' + | | | | | | | | | | | | | | | AND os_name = 'Windows' + | | | | | | | | | | | | | | | AND version_string = '31.0' + | | | | | | | | | | | | | | | ), + | | | | | | | | | | | | | | | crashes_for_sig AS ( + | | | | | | | | | | | | | | | unaligned - 16408|breakpad|2415|16385|breakpad_rw||||-1|2014-07-15 16:34:44.371108+00|2014-07-15 16:34:44.373194+00|2014-07-15 16:34:44.374074+00|2014-07-15 16:34:44.37408+00|f|active| WITH crashes AS ( SELECT os_version_string AS category , sum(report_count) AS report_count FROM signature_summary_os JOIN signatures USING (signature_id) WHERE signatures.signature = 'js::gc::IsAboutToBeFinalized<JSObject>' AND report_date >= '2014-07-08T00:00:00+00:00'::timestamptz AND report_date < '2014-07-15T00:00:00+00:00'::timestamptz AND product_name IN ('Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox', 'Firefox') AND version_string IN ('31.0b5', '31.0b4', '31.0b7', '31.0b6', '31.0b1', '31.0b3', '31.0b2', '31.0b9', '31.0b8', '30.0', '31.0b', '32.0a2', '33.0a1') GROUP BY category ), totals AS ( SELECT category
Feel free to set it however you feel most comfortable. My preference is to not see the full output as I can see that in the socorro activitylog. I've found the vast majority of work I've encountered can be determined by the statements prefix and if more info is needed, using the activitylog. All queries should now be prefixed with a comment of the namespace that references the query. At any rate, if you find it easier, I don't see why changing the default is an issue as I can and do make settings as I see best fits the situation.
Flags: needinfo?(mpressman)
Additionally, the only other primary user is selana and I believe she does the same thing I do by setting params based on the work she's doing
I like the unaligned format for sure! Turning "more" off means that there is no pager, and if you have 1000 results they'll all come flooding in. I'd rather have the safety of "more" by default. If you know you'll have more than a screenful of results, you can turn the pager off in your environment.
Whiteboard: [server optimization]
Whiteboard: [server optimization] → [data: server optimization]
Product: mozilla.org → Data & BI Services Team
Status: NEW → RESOLVED
Closed: 10 years ago
Resolution: --- → WONTFIX
You need to log in before you can comment on or make changes to this bug.