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)
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)
Reporter | ||
Comment 1•10 years ago
|
||
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).
Comment 2•10 years ago
|
||
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)
Reporter | ||
Comment 3•10 years ago
|
||
\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
Comment 4•10 years ago
|
||
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)
Comment 5•10 years ago
|
||
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
Comment 6•10 years ago
|
||
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.
Reporter | ||
Updated•10 years ago
|
Whiteboard: [server optimization]
Updated•10 years ago
|
Whiteboard: [server optimization] → [data: server optimization]
Updated•10 years ago
|
Product: mozilla.org → Data & BI Services Team
Updated•10 years ago
|
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.
Description
•