commit e9138ee9e0cbcfe685e1f1d84d3d36f7494756b5 from: Sergey Bronnikov date: Tue Feb 4 10:53:17 2025 UTC Add postgresql-perf-tools Imported from https://github.com/ligurio/postgresql-perf-tools commit - af20b270334f2eaa1bd7d1f1a0605e2560d37bd0 commit + e9138ee9e0cbcfe685e1f1d84d3d36f7494756b5 blob - /dev/null blob + d6a93266f748d606b884f9434ff662fe80b9dc21 (mode 644) --- /dev/null +++ archive/postgresql-perf-tools/LICENSE @@ -0,0 +1,340 @@ +GNU GENERAL PUBLIC LICENSE + Version 2, June 1991 + + Copyright (C) 1989, 1991 Free Software Foundation, Inc., + 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA + Everyone is permitted to copy and distribute verbatim copies + of this license document, but changing it is not allowed. + + Preamble + + The licenses for most software are designed to take away your +freedom to share and change it. By contrast, the GNU General Public +License is intended to guarantee your freedom to share and change free +software--to make sure the software is free for all its users. This +General Public License applies to most of the Free Software +Foundation's software and to any other program whose authors commit to +using it. (Some other Free Software Foundation software is covered by +the GNU Lesser General Public License instead.) You can apply it to +your programs, too. + + When we speak of free software, we are referring to freedom, not +price. Our General Public Licenses are designed to make sure that you +have the freedom to distribute copies of free software (and charge for +this service if you wish), that you receive source code or can get it +if you want it, that you can change the software or use pieces of it +in new free programs; and that you know you can do these things. + + To protect your rights, we need to make restrictions that forbid +anyone to deny you these rights or to ask you to surrender the rights. +These restrictions translate to certain responsibilities for you if you +distribute copies of the software, or if you modify it. + + For example, if you distribute copies of such a program, whether +gratis or for a fee, you must give the recipients all the rights that +you have. You must make sure that they, too, receive or can get the +source code. And you must show them these terms so they know their +rights. + + We protect your rights with two steps: (1) copyright the software, and +(2) offer you this license which gives you legal permission to copy, +distribute and/or modify the software. + + Also, for each author's protection and ours, we want to make certain +that everyone understands that there is no warranty for this free +software. If the software is modified by someone else and passed on, we +want its recipients to know that what they have is not the original, so +that any problems introduced by others will not reflect on the original +authors' reputations. + + Finally, any free program is threatened constantly by software +patents. We wish to avoid the danger that redistributors of a free +program will individually obtain patent licenses, in effect making the +program proprietary. To prevent this, we have made it clear that any +patent must be licensed for everyone's free use or not licensed at all. + + The precise terms and conditions for copying, distribution and +modification follow. + + GNU GENERAL PUBLIC LICENSE + TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION + + 0. This License applies to any program or other work which contains +a notice placed by the copyright holder saying it may be distributed +under the terms of this General Public License. The "Program", below, +refers to any such program or work, and a "work based on the Program" +means either the Program or any derivative work under copyright law: +that is to say, a work containing the Program or a portion of it, +either verbatim or with modifications and/or translated into another +language. (Hereinafter, translation is included without limitation in +the term "modification".) Each licensee is addressed as "you". + +Activities other than copying, distribution and modification are not +covered by this License; they are outside its scope. The act of +running the Program is not restricted, and the output from the Program +is covered only if its contents constitute a work based on the +Program (independent of having been made by running the Program). +Whether that is true depends on what the Program does. + + 1. You may copy and distribute verbatim copies of the Program's +source code as you receive it, in any medium, provided that you +conspicuously and appropriately publish on each copy an appropriate +copyright notice and disclaimer of warranty; keep intact all the +notices that refer to this License and to the absence of any warranty; +and give any other recipients of the Program a copy of this License +along with the Program. + +You may charge a fee for the physical act of transferring a copy, and +you may at your option offer warranty protection in exchange for a fee. + + 2. You may modify your copy or copies of the Program or any portion +of it, thus forming a work based on the Program, and copy and +distribute such modifications or work under the terms of Section 1 +above, provided that you also meet all of these conditions: + + a) You must cause the modified files to carry prominent notices + stating that you changed the files and the date of any change. + + b) You must cause any work that you distribute or publish, that in + whole or in part contains or is derived from the Program or any + part thereof, to be licensed as a whole at no charge to all third + parties under the terms of this License. + + c) If the modified program normally reads commands interactively + when run, you must cause it, when started running for such + interactive use in the most ordinary way, to print or display an + announcement including an appropriate copyright notice and a + notice that there is no warranty (or else, saying that you provide + a warranty) and that users may redistribute the program under + these conditions, and telling the user how to view a copy of this + License. (Exception: if the Program itself is interactive but + does not normally print such an announcement, your work based on + the Program is not required to print an announcement.) + +These requirements apply to the modified work as a whole. If +identifiable sections of that work are not derived from the Program, +and can be reasonably considered independent and separate works in +themselves, then this License, and its terms, do not apply to those +sections when you distribute them as separate works. But when you +distribute the same sections as part of a whole which is a work based +on the Program, the distribution of the whole must be on the terms of +this License, whose permissions for other licensees extend to the +entire whole, and thus to each and every part regardless of who wrote it. + +Thus, it is not the intent of this section to claim rights or contest +your rights to work written entirely by you; rather, the intent is to +exercise the right to control the distribution of derivative or +collective works based on the Program. + +In addition, mere aggregation of another work not based on the Program +with the Program (or with a work based on the Program) on a volume of +a storage or distribution medium does not bring the other work under +the scope of this License. + + 3. You may copy and distribute the Program (or a work based on it, +under Section 2) in object code or executable form under the terms of +Sections 1 and 2 above provided that you also do one of the following: + + a) Accompany it with the complete corresponding machine-readable + source code, which must be distributed under the terms of Sections + 1 and 2 above on a medium customarily used for software interchange; or, + + b) Accompany it with a written offer, valid for at least three + years, to give any third party, for a charge no more than your + cost of physically performing source distribution, a complete + machine-readable copy of the corresponding source code, to be + distributed under the terms of Sections 1 and 2 above on a medium + customarily used for software interchange; or, + + c) Accompany it with the information you received as to the offer + to distribute corresponding source code. (This alternative is + allowed only for noncommercial distribution and only if you + received the program in object code or executable form with such + an offer, in accord with Subsection b above.) + +The source code for a work means the preferred form of the work for +making modifications to it. For an executable work, complete source +code means all the source code for all modules it contains, plus any +associated interface definition files, plus the scripts used to +control compilation and installation of the executable. However, as a +special exception, the source code distributed need not include +anything that is normally distributed (in either source or binary +form) with the major components (compiler, kernel, and so on) of the +operating system on which the executable runs, unless that component +itself accompanies the executable. + +If distribution of executable or object code is made by offering +access to copy from a designated place, then offering equivalent +access to copy the source code from the same place counts as +distribution of the source code, even though third parties are not +compelled to copy the source along with the object code. + + 4. You may not copy, modify, sublicense, or distribute the Program +except as expressly provided under this License. Any attempt +otherwise to copy, modify, sublicense or distribute the Program is +void, and will automatically terminate your rights under this License. +However, parties who have received copies, or rights, from you under +this License will not have their licenses terminated so long as such +parties remain in full compliance. + + 5. You are not required to accept this License, since you have not +signed it. However, nothing else grants you permission to modify or +distribute the Program or its derivative works. These actions are +prohibited by law if you do not accept this License. Therefore, by +modifying or distributing the Program (or any work based on the +Program), you indicate your acceptance of this License to do so, and +all its terms and conditions for copying, distributing or modifying +the Program or works based on it. + + 6. Each time you redistribute the Program (or any work based on the +Program), the recipient automatically receives a license from the +original licensor to copy, distribute or modify the Program subject to +these terms and conditions. You may not impose any further +restrictions on the recipients' exercise of the rights granted herein. +You are not responsible for enforcing compliance by third parties to +this License. + + 7. If, as a consequence of a court judgment or allegation of patent +infringement or for any other reason (not limited to patent issues), +conditions are imposed on you (whether by court order, agreement or +otherwise) that contradict the conditions of this License, they do not +excuse you from the conditions of this License. If you cannot +distribute so as to satisfy simultaneously your obligations under this +License and any other pertinent obligations, then as a consequence you +may not distribute the Program at all. For example, if a patent +license would not permit royalty-free redistribution of the Program by +all those who receive copies directly or indirectly through you, then +the only way you could satisfy both it and this License would be to +refrain entirely from distribution of the Program. + +If any portion of this section is held invalid or unenforceable under +any particular circumstance, the balance of the section is intended to +apply and the section as a whole is intended to apply in other +circumstances. + +It is not the purpose of this section to induce you to infringe any +patents or other property right claims or to contest validity of any +such claims; this section has the sole purpose of protecting the +integrity of the free software distribution system, which is +implemented by public license practices. Many people have made +generous contributions to the wide range of software distributed +through that system in reliance on consistent application of that +system; it is up to the author/donor to decide if he or she is willing +to distribute software through any other system and a licensee cannot +impose that choice. + +This section is intended to make thoroughly clear what is believed to +be a consequence of the rest of this License. + + 8. If the distribution and/or use of the Program is restricted in +certain countries either by patents or by copyrighted interfaces, the +original copyright holder who places the Program under this License +may add an explicit geographical distribution limitation excluding +those countries, so that distribution is permitted only in or among +countries not thus excluded. In such case, this License incorporates +the limitation as if written in the body of this License. + + 9. The Free Software Foundation may publish revised and/or new versions +of the General Public License from time to time. Such new versions will +be similar in spirit to the present version, but may differ in detail to +address new problems or concerns. + +Each version is given a distinguishing version number. If the Program +specifies a version number of this License which applies to it and "any +later version", you have the option of following the terms and conditions +either of that version or of any later version published by the Free +Software Foundation. If the Program does not specify a version number of +this License, you may choose any version ever published by the Free Software +Foundation. + + 10. If you wish to incorporate parts of the Program into other free +programs whose distribution conditions are different, write to the author +to ask for permission. For software which is copyrighted by the Free +Software Foundation, write to the Free Software Foundation; we sometimes +make exceptions for this. Our decision will be guided by the two goals +of preserving the free status of all derivatives of our free software and +of promoting the sharing and reuse of software generally. + + NO WARRANTY + + 11. BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY +FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN +OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES +PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED +OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF +MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS +TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE +PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, +REPAIR OR CORRECTION. + + 12. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING +WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR +REDISTRIBUTE THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU FOR DAMAGES, +INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING +OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED +TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY +YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER +PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE +POSSIBILITY OF SUCH DAMAGES. + + END OF TERMS AND CONDITIONS + + How to Apply These Terms to Your New Programs + + If you develop a new program, and you want it to be of the greatest +possible use to the public, the best way to achieve this is to make it +free software which everyone can redistribute and change under these terms. + + To do so, attach the following notices to the program. It is safest +to attach them to the start of each source file to most effectively +convey the exclusion of warranty; and each file should have at least +the "copyright" line and a pointer to where the full notice is found. + + {description} + Copyright (C) {year} {fullname} + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; either version 2 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License along + with this program; if not, write to the Free Software Foundation, Inc., + 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +Also add information on how to contact you by electronic and paper mail. + +If the program is interactive, make it output a short notice like this +when it starts in an interactive mode: + + Gnomovision version 69, Copyright (C) year name of author + Gnomovision comes with ABSOLUTELY NO WARRANTY; for details type `show w'. + This is free software, and you are welcome to redistribute it + under certain conditions; type `show c' for details. + +The hypothetical commands `show w' and `show c' should show the appropriate +parts of the General Public License. Of course, the commands you use may +be called something other than `show w' and `show c'; they could even be +mouse-clicks or menu items--whatever suits your program. + +You should also get your employer (if you work as a programmer) or your +school, if any, to sign a "copyright disclaimer" for the program, if +necessary. Here is a sample; alter the names: + + Yoyodyne, Inc., hereby disclaims all copyright interest in the program + `Gnomovision' (which makes passes at compilers) written by James Hacker. + + {signature of Ty Coon}, 1 April 1989 + Ty Coon, President of Vice + +This General Public License does not permit incorporating your program into +proprietary programs. If your program is a subroutine library, you may +consider it more useful to permit linking proprietary applications with the +library. If this is what you want to do, use the GNU Lesser General +Public License instead of this License. + blob - /dev/null blob + ea848826c8dd0ed21d9bc1b3b4171395371bd27c (mode 644) --- /dev/null +++ archive/postgresql-perf-tools/README.md @@ -0,0 +1,82 @@ +## PostgreSQL Performance Monitoring Tools + +This package includes three useful scripts aimed to help to pinpoint +performance issues on systems with PostgreSQL as database backend. + +All scritps are written in Python. +Requirements: +* Python 2.6+ +* psycopg2 module (http://initd.org/psycopg/) + +All scripts require a connect to PostgreSQL database. They take traditional +set of DB credentials: host address, port (5432 by default), database name, +database user and password. + +Note: you may need to modify pg_hba.conf file to allow the scripts to +connect to the server. Please refer to official documentaion: +http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html + +For detailed list of command line options use --help / -h option. + +### pg-top + +*pg-top.py* uses pg_stat_user_tables system table to get current statistics +of server activity. The information is represented in 'top'-like screen, +per table and total for the server, and updated dynamically. + +The following data is reported: +* total number of inserted/updated/deleted rows per sec +* number of inserted rows per second +* number of updated rows per second +* number of deleted rows per second +* number of rows updated with index update +* number of index scans per second +* number of sequential scans per second +* number of rows per second fetched by seq scans +* number of processes waiting for lock +* approximate number of rows in table + +### pg-stat + +*pg-stat.py* is a command-line tool to get advanced server statistics in +real-time. The information is represented in tabular form, similar to +'vmstat' output. By default, new data row is printed each 2 seconds. + +The following data is reported: +* size of database in kilobytes +* write operations: number of rows inserted/updated/deleted (into user tables) +* total number of index scans +* total number of sequential scans +* percentage of sequential scans +* total number of live rows fetched by seq scans +* cache: total number of shmem block hit/miss +* IO: percent of time spent on waiting to read/write the device (>=9.2) +* number of processes waiting for lock +* total number of deadlocks (>= 9.2) +* number of transactions committed/rolled back +* total number of 'idle in transaction' processes +* total number of live processes + +### pg-info + +*pg-info.py* script gathers static performance-related information +from the pg_stat_xxx tables and tries to identify potential problem sources. + +In particular, the following data is reported: +* Size of entire database on disk +* Top tables sorted by size on disk +* Large tables with missing indexes +* Less frequently accessed indexes ordered by size +* Most frequently modified tables + + +## Authors And Contributors + +These scripts were created as inhouse tools at Parallels (www.parallels.com), +by Alexander Andreev (aandreev@odin.com). + +Do not hesitate to send your patches, issues and proposals! + +## License + +Released under [GPLv2 License](LICENSE) blob - /dev/null blob + acf31b5d7f6719409b62cb151dc4fa0b35e07264 (mode 755) --- /dev/null +++ archive/postgresql-perf-tools/pg-info.py @@ -0,0 +1,391 @@ +#!/usr/bin/python + +import os +import sys +import time + +HAS_PA = True +oldpath = sys.path +try: + bindir, testscript= os.path.split(sys.argv[0]) + sys.path.append(os.path.join(bindir, "..", "lib")) + import pa_config +except ImportError: + HAS_PA = False +finally: + sys.path = oldpath + +import psycopg2 +from optparse import OptionParser, OptionGroup +import logging +HAS_SQLPARSE = True +try: + import sqlparse +except ImportError: + HAS_SQLPARSE = False + + +class DB: + def __init__(self, host, port, database, user, password): + self.host = host + self.port = port + self.database = database + self.user = user + self.password = password or "" + + def get_name(self): + return self.database + + def __str__(self): + return "%s@%s:%s db %s" % (self.user, self.host, self.port, self.database) + + def connect(self): + return psycopg2.connect(**(self.__dict__)) + + @staticmethod + def _execute_fetch(con, query, fetchfn, *args): + cur = con.cursor() + try: + if args is None or not len(args): + logging.debug(query) + cur.execute(query) + else: + logging.debug(query % args) + cur.execute(query, args) + return fetchfn(cur) + finally: + cur.close() + + @staticmethod + def execute_fetchone(con, query, *args): + return DB._execute_fetch(con, query, lambda cur : cur.fetchone(), *args) + + @staticmethod + def execute_fetchall(con, query, *args): + return DB._execute_fetch(con, query, lambda cur : cur.fetchall(), *args) + + @staticmethod + def execute_fetchval(con, query, *args): + ret = DB.execute_fetchone(con, query, *args) + if ret is not None and len(ret) > 0: + return ret[0] + + +class PgInfo: + def __init__(self, con, db_name, width, lines_limit, print_sqls=False): + self.con = con + self.db_name = db_name + self.w = width + self.lines_limit = lines_limit + self.print_sqls = print_sqls + + def _printSql(self, q): + if self.print_sqls: + self._printLine() + if HAS_SQLPARSE: + print sqlparse.format(q, reindent=True, keyword_case='upper') + else: + print q + + def _printHeader(self, text): + self._printLine("=") + print text + + def _printLine(self, char="-"): + print char * self.w + + def _printTable(self, fmt, sql_ret): + self._printLine() + for f in fmt: + if f[1] != 0: + print f[0].rjust(f[1]).upper(), + print + line = 0 + for s in sql_ret: + if self.lines_limit and line > self.lines_limit: + for n in xrange(0, len(fmt)): + if fmt[n][1] == 0: + continue + print "...".rjust(fmt[n][1]), + print + break + line += 1 + n = 0 + for v in s: + if fmt[n][1] != 0: + if len(fmt[n]) > 2 and fmt[n][2]: + try: + v = fmt[n][2] % v + except: + v = "0" + else: + v = str(v) + l = len(v) + maxl = fmt[n][1] + if l > maxl: + v = v[0:maxl-3] + "..." + print v.rjust(fmt[n][1]), + n += 1 + print + print + + def printPGInfo(self): + print DB.execute_fetchval(self.con, "SELECT version()") + print + + def printDBSize(self): + self._printHeader("All schemas sorted by TOTAL_SZ size on disk") + q = """ + SELECT + schema, + pg_size_pretty(total) AS total, + pg_size_pretty(relation) AS relation, + pg_size_pretty(indexes) AS indexes, + case when total > 0 + THEN + indexes / total + ELSE + 0 + END + FROM + ( + SELECT + schema, + sum(pg_total_relation_size(qual_table))::bigint AS total, + sum(pg_relation_size(qual_table))::bigint AS relation, + sum(pg_indexes_size(qual_table))::bigint AS indexes + FROM + ( + SELECT + schemaname AS schema, + tablename AS table, + ('"'||schemaname||'"."'||tablename||'"')::regclass AS qual_table + FROM + pg_tables + WHERE + schemaname NOT LIKE 'pg_%' + ) s + GROUP BY schema + ORDER BY total DESC + ) s""" + self._printSql(q) + ret = DB.execute_fetchall(self.con, q) + fmt = [('schema', 32), ('*total_sz', 11), ('data_sz', 11), ('index_sz', 11), ('index_sz%', 9, "%.0f%%")] + self._printTable(fmt, ret) + + def printTablesSize(self): + self._printHeader("All tables sorted by TOTAL_SZ size on disk") + q = """ + SELECT + relname, + pg_total_relation_size(C.oid) AS total_bytes, + pg_size_pretty(pg_total_relation_size(C.oid)) AS "total", + pg_size_pretty(pg_relation_size(C.oid)) AS "data_sz", + pg_size_pretty(pg_indexes_size(C.oid)) AS "index_sz", + case when pg_indexes_size(C.oid) > 0 + THEN + 100 * pg_indexes_size(C.oid) / pg_total_relation_size(C.oid) + ELSE + 0 + END, + reltuples, + columns, + indexes, + case when columns > 0 + THEN + 100 * indexes / columns + ELSE + 0 + END + FROM + pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) + LEFT JOIN ( + SELECT + table_name, count(*) AS columns + FROM + information_schema.columns + GROUP BY + table_name + ) S ON S.table_name = C.relname + LEFT JOIN ( + SELECT + tablename, count(tablename) AS indexes + FROM + pg_indexes + GROUP BY + tablename + ) I ON (I.tablename = C.relname) + WHERE + nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind = 'r' + ORDER BY + total_bytes DESC + """ + self._printSql(q) + ret = DB.execute_fetchall(self.con, q) + fmt = [('table', 32), + ('total_bytes', 0), + ('*total_sz', 11), ('data_sz', 11), ('index_sz', 11), ('index_sz%', 9, "%d%%"), + ("rows", 11, "%d"), ("columns", 7, "%d"), ("indexes", 7, "%d"), ("idx%", 4, "%d")] + + self._printTable(fmt, ret) + print " HINT: The bigger is table TOTAL_SZ the slower are all operations on this table\n" + + def printMissingIndexes(self, pg_rel_size_threshold): + if pg_rel_size_threshold: + self._printHeader("Tables with size > %dKB and missing indexes (lots of sequential scans)" % + (pg_rel_size_threshold / 1024)) + else: + self._printHeader("All tables with missing indexes") + + q = """ + SELECT + relname, seq_scan-idx_scan AS too_much_seq, + case when seq_scan-idx_scan > 0 + THEN + 'Missing Index?' + ELSE + 'OK' + END, + pg_relation_size(relname::regclass) + AS rel_size, seq_scan, idx_scan + FROM + pg_stat_all_tables + WHERE + schemaname='public' AND pg_relation_size(relname::regclass) >= %d + ORDER BY too_much_seq DESC""" % pg_rel_size_threshold + + self._printSql(q) + ret = DB.execute_fetchall(self.con, q) + fmt = [('table', 32), ('*too_much_seq', 14), ('case', 15), ('rel_size', 10), + ('seq_scan', 10), ('idx_scan', 10)] + self._printTable(fmt, ret) + print \ + " HINT: The more TOO_MUCH_SCAN value the more frequently sequential scan was performed on the\n" + \ + " given table\n" + + def printDeadIndexes(self): + self._printHeader("Less frequently accessed indexes ordered by IDX_SIZE") + q = """ + SELECT + relid::regclass AS table, + indexrelid::regclass AS index, + pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, + pg_relation_size(indexrelid::regclass) AS index_size_bytes, + idx_scan, + idx_tup_read, + idx_tup_fetch + FROM + pg_stat_user_indexes + JOIN pg_index USING (indexrelid) + WHERE + indisunique IS FALSE + ORDER BY idx_scan ASC, index_size_bytes DESC + """ + self._printSql(q) + ret = DB.execute_fetchall(self.con, q) + + fmt = [('table', 32), ('index', 40), ('*idx_size', 10), ('idx size bytes', 0), + ('*idx_scan', 9), ('tup_read', 9), ('tup_fetch', 9)] + self._printTable(fmt, ret) + + print \ + " HINT: The more IDX_SIZE value the more size is occupied for given index, if IDX_SCAN == 0 then\n" + \ + " this index is unused and can be just removed to increase INSERT/UPDATE performance\n" + + def printMostWritableTables(self): + self._printHeader("Most frequently modified tables") + q = """ + SELECT + relname AS TABLE, + pg_size_pretty( pg_relation_size(relid) ) AS tsize, + n_tup_upd + n_tup_ins + n_tup_del AS WRITE, + seq_scan + idx_scan AS READ, + case when seq_scan + idx_scan > 0 + THEN + 100 * (n_tup_upd + n_tup_ins + n_tup_del) / (n_tup_upd + n_tup_ins + n_tup_del + seq_scan + idx_scan) + ELSE + 0 + END, + n_tup_ins AS INS, + n_tup_upd AS UPD, + n_tup_del AS DEL + FROM + pg_stat_user_tables + ORDER BY + ( n_tup_upd + n_tup_ins + n_tup_del ) DESC + """ + self._printSql(q) + ret = DB.execute_fetchall(self.con, q) + + fmt = [('table', 32), ('tsize', 10), ('*writes', 11), ('reads', 11), + ('write%', 10, "%.0f%%"), ('ins', 10), ('upd', 10), ('del', 10)] + self._printTable(fmt, ret) + print " HINT: Tables with significant amount of WRITEs can reveal bad application design\n" + +def main(): + test_description = "%prog [options]" + + p = OptionParser(test_description) + p.add_option("-v", "--verbose", action="store_true", help="enable verbose mode") + p.add_option("-s", "--sql", action="store_true", help="print SQLs that were used to obtain the stats") + p.add_option("-l", "--lines", type=int, default=20, help="num of lines in output (default %default)") + p.add_option("", "--min-tab-size", type=int, default=(32*1024), + help="min size of tables when analyze missing indexes (default %default)") + + defdb = "" + defusr = "postgres" + if HAS_PA: + g = OptionGroup(p, "If you have pa.conf") + g.add_option("-c", "--config", type="string", default=None, help="PA config file [default: %default]") + g.add_option("", "--pba", action="store_true", help = "connect to PBA (POA is default)") + p.add_option_group(g) + defdb = "plesk" + defusr = "plesk" + + g = OptionGroup(p, "If you don't have pa.conf" if HAS_PA else "Database credentials") + g.add_option("", "--db-host", type="string", help="database hostname/IP") + g.add_option("", "--db-port", type="string", default=5432, help="database port") + g.add_option("", "--db-name", type="string", default=defdb, help="database name [default: %default]") + g.add_option("", "--db-user", type="string", default=defusr, help="database username [default: %default]") + g.add_option("", "--db-pass", type="string", default="", help="database password") + p.add_option_group(g) + + opts, args = p.parse_args() + loglevel = logging.DEBUG if opts.verbose else logging.WARNING + logging.basicConfig(level=loglevel, format="%(asctime)s - %(module)s - %(levelname)s - %(message)s") + + if HAS_PA: + if not opts.config and not opts.db_host: + p.error("either -c or --db-host option must be provided") + else: + if not opts.db_host: + p.error("--db-host option must be provided") + + if HAS_PA and opts.config: + pa_config.init(opts.config) + if opts.pba: + b = pa_config.get().pba_db + else: + b = pa_config.get().poa_db + + opts.db_vendor = b.db_vendor + opts.db_host = b.ip + opts.db_port = b.db_port + opts.db_name = b.db_name + opts.db_user = b.db_user + opts.db_pass = b.db_pass + + db = DB(opts.db_host, opts.db_port, opts.db_name, opts.db_user, opts.db_pass) + print "Connecting to %s ..." % str(db) + con = db.connect() + pi = PgInfo(con, opts.db_name, 110, opts.lines, opts.sql) + + pi.printPGInfo() + pi.printDBSize() + pi.printTablesSize() + pi.printMissingIndexes(opts.min_tab_size) + pi.printDeadIndexes() + pi.printMostWritableTables() + +if __name__ == "__main__": + main() blob - /dev/null blob + 8e7557d591b88d89dba74b027e3c42051c208658 (mode 755) --- /dev/null +++ archive/postgresql-perf-tools/pg-stat.py @@ -0,0 +1,508 @@ +#!/usr/bin/python + +import os +import sys +import time + +HAS_PA = True +oldpath = sys.path +try: + bindir, testscript= os.path.split(sys.argv[0]) + sys.path.append(os.path.join(bindir, "..", "lib")) + import pa_config +except ImportError: + HAS_PA = False +finally: + sys.path = oldpath + +import psycopg2 +from optparse import OptionParser, OptionGroup +import logging +import inspect + + +class DB: + def __init__(self, host, port, database, user, password): + self.host = host + self.port = port + self.database = database + self.user = user + self.password = password or "" + + def get_name(self): + return self.database + + def __str__(self): + return "%s@%s:%s db %s" % (self.user, self.host, self.port, self.database) + + def connect(self): + return psycopg2.connect(**(self.__dict__)) + + @staticmethod + def _execute_fetch(con, query, fetchfn, *args): + cur = con.cursor() + try: + if args is None or not len(args): + logging.debug(query) + cur.execute(query) + else: + logging.debug(query % args) + cur.execute(query, args) + return fetchfn(cur) + finally: + cur.close() + + @staticmethod + def execute_fetchone(con, query, *args): + return DB._execute_fetch(con, query, lambda cur : cur.fetchone(), *args) + + @staticmethod + def execute_fetchall(con, query, *args): + return DB._execute_fetch(con, query, lambda cur : cur.fetchall(), *args) + + @staticmethod + def execute_fetchval(con, query, *args): + ret = DB.execute_fetchone(con, query, *args) + if ret is not None and len(ret) > 0: + return ret[0] + + +class PgVersion: + def __init__(self, con): + self.str = DB.execute_fetchval(con, "SELECT version()") + self.maj = DB.execute_fetchval(con, "SELECT substring(version() from $$(\d+)\.\d+\.\d+$$)::smallint;") + self.min = DB.execute_fetchval(con, "SELECT substring(version() from $$\d+\.(\d+)\.\d+$$)::smallint;") + + def ge(self, maj, min): + return self.maj > maj or (self.maj >= maj and self.min >= min) + + +opts = None +con = None + +class PgStatStore: + def __init__(self, table=None, cols=None): + self.table = table + self.cols = cols + + def update(self): + query = ["SUM(%s)" % c for c in self.cols] + + ret = DB.execute_fetchone(con, "SELECT %s FROM %s" % (", ".join(query), self.table)) + self.store = {} + n = 0 + for c in self.cols: + self.store[c] = ret[n] + n += 1 + + def append(self, column): + self.cols.append(column) + +class PgStatStoreBigUserTables(PgStatStore): + def __init__(self, cols): + self.cols = cols + + def update(self): + query = ["SUM(p.%s)" % c for c in self.cols] + + ret = DB.execute_fetchone(con, + "SELECT %s FROM pg_stat_user_tables p, pg_class c " + "WHERE p.relname = c.relname AND c.reltuples > %d" % + (", ".join(query), int(opts.scan_threshold))) + self.store = {} + n = 0 + for c in self.cols: + self.store[c] = ret[n] + n += 1 + +class PgStatStoreProc(PgStatStore): + def update(self): + ret = DB.execute_fetchall(con, + "SELECT query, COUNT(*) FROM pg_stat_activity " + \ + "WHERE datname = '%s' GROUP BY query" % opts.db_name) + + self.store = {} + for row in ret: + if "pg_stat_activity" in row[0]: + continue + self.store[row[0]] = row[1] + +class DbStatCounter: + width = 5 + rate_fmt = None + absolute = False + def __init__(self, store=None): + metric_len = len(self.metric) + if not self.absolute: + metric_len += 2 # '/s' + self.width = max(self.width, len(self.title), metric_len) + self.store = store + + self.val_initial = 0 + self.val = 0 + self.rate = 0 + self.time = 0 + + def update(self): + prev_val = float(self.val) + self.update_action() + prev_time = self.time + self.time = time.time() + logging.debug("%s raw val: %d" % (self.title, self.val)) + self.rate = 0 + if self.absolute: + self.rate = self.val + else: + if prev_time: + dt = self.time - prev_time + if dt: + self.rate = (float(self.val) - prev_val) / dt + if not self.val_initial: + self.val_initial = self.val + con.commit() + + def update_action(self): + # virtual + self.val = 0 + + def abs(self): + if self.absolute: + return self.val + return self.val - self.val_initial + +class pgsDbSize(DbStatCounter): + title = "DBSize" + metric = "KB" + width = 8 + help = "size of database in kilobytes" + def update_action(self, **kwargs): + self.val = DB.execute_fetchval(con, "select pg_database_size('%s')" % opts.db_name) + self.val /= 1024 + +class pgsWrIns(DbStatCounter): + title = "INS" + metric = "rows" + help = "number of rows inserted into user tables [pg_stat_user_tables.n_tup_ins]" + rate_fmt = "%.1f" + def update_action(self): + self.val = self.store.store["n_tup_ins"] + +class pgsWrUpd(DbStatCounter): + title = "UPD" + metric = "rows" + help = "number of rows updated in the user tables [pg_stat_user_tables.n_tup_upd]" + rate_fmt = "%.1f" + def update_action(self): + self.val = self.store.store["n_tup_upd"] + +class pgsWrDel(DbStatCounter): + title = "DEL" + metric = "rows" + help = "number of rows deleted in the user tables [pg_stat_user_tables.n_tup_del]" + rate_fmt = "%.1f" + def update_action(self): + self.val = self.store.store["n_tup_del"] + +class pgsScanIdx(DbStatCounter): + title = "IDX" + metric = "scan" + help = "total number of index scans [pg_stat_database.idx_scan]" + def update_action(self): + self.val = self.store.store["idx_scan"] + +class pgsScanSeq(DbStatCounter): + title = "SEQ" + metric = "scan" + help = "total number of sequential scans [pg_stat_database.seq_scan]" + def update_action(self): + self.val = self.store.store["seq_scan"] + +class pgsScanIdxPerc(DbStatCounter): + title = "SEQ%" + metric = "scan%" + help = "percentage of sequential scans [100 * pg_stat_database.seq_scan / (.idx_scan + .seq_scan)]" + absolute = True + def update_action(self): + idx = int(self.store.store['idx_scan']) + seq = int(self.store.store['seq_scan']) + + if hasattr(self, 'prev_idx'): + d_idx = idx - self.prev_idx + if d_idx < 0: + d_idx = 0 + d_seq = seq - self.prev_seq + if d_seq < 0: + d_seq = 0 + tot = d_idx + d_seq + self.val = ((100 * int(d_seq)) / float(tot)) if tot else 0 + else: + self.val = 0 + self.prev_idx = idx + self.prev_seq = seq + + +class pgsScanSeqRows(DbStatCounter): + title = "SEQ_ROWS" + metric = "rows" + help = "total number of live rows fetched by seq scans [pg_stat_database.seq_tup_read]" + rate_fmt = "%.0f" + def update_action(self): + self.val = self.store.store["seq_tup_read"] + +class pgsCacheMiss(DbStatCounter): + title = "MISS" + metric = "blk" + help = "total number of shmem block miss [pg_stat_database.blks_read - pg_stat_database.blks_hit]" + def update_action(self): + self.val = self.store.store["blks_read"] + +class pgsCacheHit(DbStatCounter): + title = "HIT" + metric = "blk" + width = 6 + help = "total number of shmem block hits [pg_stat_database.blks_hit]" + def update_action(self): + self.val = self.store.store['blks_hit'] + +class pgsIoReadWa(DbStatCounter): + title = "READWA" + metric = "wait%" + help = "percent of time spent on IO read's wait [100 * pg_stat_database.blk_read_time / wall_time] (>= 9.2)" + def update_action(self): + dt = time.time() - self.time + wa = self.store.store['blk_read_time'] + self.val = 100 * wa / dt if dt else 0 + +class pgsIoWriteWa(DbStatCounter): + title = "WRITEWA" + metric = "wait%" + help = "percent of time spent on IO write's wait [100 * pg_stat_database.blk_write_time / wall_time] (>= 9.2)" + def update_action(self): + dt = time.time() - self.time + wa = self.store.store['blk_write_time'] + self.val = 100 * wa / dt if dt else 0 + +class pgsTxnCommit(DbStatCounter): + title = "COMMIT" + metric = "txn" + help = "number of committed transactions [pg_stat_database.xact_commit]" + def update_action(self): + self.val = self.store.store['xact_commit'] + +class pgsTxnRollback(DbStatCounter): + title = "RLLBCK" + metric = "txn" + help = "number of rolled back transactions [pg_stat_database.xact_rollback]" + def update_action(self): + self.val = self.store.store['xact_rollback'] + +class pgsLockWait(DbStatCounter): + width = 5 + title = "LOCK" + metric = "cnt" + help = "number of processes waiting for lock [COUNT(*) FROM pg_locks WHERE NOT granted]" + rate_fmt = "%d" + absolute = True + def update_action(self): + self.val = DB.execute_fetchval(con, "SELECT COUNT(*) FROM pg_locks WHERE NOT granted") + +class pgsDeadlocks(DbStatCounter): + title = "DEADLOCK" + metric = "cnt" + help = "total number of deadlocks [pg_stat_database.deadlocks] (>= 9.2)" + def update_action(self): + self.val = self.store.store['deadlocks'] + +class pgsProcsIdletxn(DbStatCounter): + width = 5 + title = "PROC" + metric = "idltxn" + help = "total number of 'idle in transaction' processes" + absolute = True + rate_fmt = "%d" + def update_action(self): + key = " in transaction" + self.val = int(self.store.store[key]) if self.store.store.has_key(key) else 0 + +class pgsProcsLive(DbStatCounter): + width = 3 + title = "PROC" + metric = "live" + help = "total number of live processes" + absolute = True + rate_fmt = "%d" + def update_action(self): + self.val = 0 + for key, val in self.store.store.items(): + if key.startswith(""): + continue + self.val += int(val) + +class PgStats: + def __init__(self): + self.sep = " |" + self.hdr_titles = "" + self.hdr_metrics = "" + self.fmt = "" + + pg_ver = PgVersion(con) + print pg_ver.str + + s_db = PgStatStore("pg_stat_database", ["xact_commit", "xact_rollback", "blks_read", "blks_hit"]) + s_ut = PgStatStore("pg_stat_user_tables", ["n_tup_ins", "n_tup_upd", "n_tup_del"]) + s_utb = PgStatStoreBigUserTables(["idx_scan", "seq_scan", "seq_tup_read"]) + s_pr = PgStatStoreProc() + + if pg_ver.ge(9, 2): + s_db.append("deadlocks") + s_db.append("blk_read_time") + s_db.append("blk_write_time") + + self.stores = [s_db, s_ut, s_utb, s_pr] + + self.groups = [ + ("DataBase", [pgsDbSize()]), + ("Write Ops", [pgsWrIns(s_ut), pgsWrUpd(s_ut), pgsWrDel(s_ut)]), + ("Scan (tables with >%dK rows)" % (opts.scan_threshold / 1000), + [pgsScanIdx(s_utb), pgsScanSeq(s_utb), pgsScanIdxPerc(s_utb), pgsScanSeqRows(s_utb)]), + ("CacheRead", [pgsCacheHit(s_db), pgsCacheMiss(s_db)]), + ("Locks", [pgsLockWait()] + ([pgsDeadlocks(s_db)] if pg_ver.ge(9, 2) else [])), + ("Transactions", [pgsTxnCommit(s_db), pgsTxnRollback(s_db)]), + ("Proc", [pgsProcsIdletxn(s_pr), pgsProcsLive(s_pr)]), + ] + + if pg_ver.ge(9, 2): + self.groups.append(("Disk Wait", [pgsIoReadWa(s_db), pgsIoWriteWa(s_db)])) + + self.init() + + def init(self): + self.counters = [] + + for group in self.groups: + for c in group[1]: + self.counters.append(c) + self.hdr_metrics += " " + c.title.rjust(c.width) + self.fmt += " %%%ds" % c.width + self.hdr_titles += " " + group[0].rjust(sum(c.width + 1 for c in group[1]) - 1) + self.sep + self.hdr_metrics += self.sep + self.fmt += self.sep + + def header(self): + print "=" * len(self.hdr_titles) + print self.hdr_titles + print self.hdr_metrics + if opts.abs: + metrics = [c.metric for c in self.counters] + else: + metrics = [c.metric if c.absolute else "%s/s" % c.metric for c in self.counters] + print self.fmt % tuple(metrics) + print "+" * len(self.hdr_titles) + + def print_row(self): + if opts.abs: + vals = ["%d" % c.abs() for c in self.counters] + else: + vals = [] + for c in self.counters: + r = c.val if c.absolute else c.rate + fmt = c.rate_fmt if c.rate_fmt else ("%.1f" if r < 100 else "%.0f") + vals.append(fmt % r) + print self.fmt % tuple(vals) + + def update(self): + for s in self.stores: + s.update() + for c in self.counters: + c.update() + +def pg_usage(): + ps = PgStats() + ps.header() + ps.update() + try: + i = 0 + while True: + time.sleep(opts.delay) + ps.update() + ps.print_row() + if opts.count: + i += 1 + if opts.count <= i: + break + except KeyboardInterrupt, e: + pass + +def main(): + global opts + global con + + test_description = "%prog [options]" + + epilog = "\nCounters description:" + for name, obj in inspect.getmembers(sys.modules[__name__]): + if inspect.isclass(obj) and issubclass(obj, DbStatCounter) and hasattr(obj, "title"): + epilog += "\n%18s - %s" % (obj.title + " (" + obj.metric + ")", obj.help) + + class PgOptParser(OptionParser): + def format_epilog(self, formatter): + return self.epilog + "\n" + + p = PgOptParser(test_description, epilog=epilog) + p.add_option("-v", "--verbose", action="store_true", help="enable verbose mode") + p.add_option("-d", "--delay", type=int, default=2, help="delay between database poll (sec)") + p.add_option("-n", "--count", type=int, default=0, help="exit after COUNT iterations") + p.add_option("-a", "--abs", action="store_true", help="show absolute values, not rates") + p.add_option("-r", "--scan-threshold", type=int, default=5000, + help="skip tables with fewer rows when collect IDX and SEQ scan stats") + + defdb = "" + defusr = "postgres" + if HAS_PA: + g = OptionGroup(p, "If you have pa.conf") + g.add_option("-c", "--config", type="string", default=None, help="PA config file [default: %default]") + g.add_option("", "--pba", action="store_true", help = "connect to PBA (POA is default)") + p.add_option_group(g) + defdb = "plesk" + defusr = "plesk" + + g = OptionGroup(p, "If you don't have pa.conf" if HAS_PA else "Database credentials") + g.add_option("", "--db-host", type="string", help="database hostname/IP") + g.add_option("", "--db-port", type="string", default=5432, help="database port") + g.add_option("", "--db-name", type="string", default=defdb, help="database name [default: %default]") + g.add_option("", "--db-user", type="string", default=defusr, help="database username [default: %default]") + g.add_option("", "--db-pass", type="string", default="", help="database password") + p.add_option_group(g) + + opts, args = p.parse_args() + loglevel = logging.DEBUG if opts.verbose else logging.WARNING + logging.basicConfig(level=loglevel, format="%(asctime)s - %(module)s - %(levelname)s - %(message)s") + + if HAS_PA: + if not opts.config and not opts.db_host: + p.error("either -c or --db-host option must be provided") + else: + if not opts.db_host: + p.error("--db-host option must be provided") + + if HAS_PA and opts.config: + pa_config.init(opts.config) + if opts.pba: + b = pa_config.get().pba_db + else: + b = pa_config.get().poa_db + + opts.db_vendor = b.db_vendor + opts.db_host = b.ip + opts.db_port = b.db_port + opts.db_name = b.db_name + opts.db_user = b.db_user + opts.db_pass = b.db_pass + + db = DB(opts.db_host, opts.db_port, opts.db_name, opts.db_user, opts.db_pass) + print "Connecting to %s ..." % str(db) + con = db.connect() + pg_usage() + +if __name__ == "__main__": + main() blob - /dev/null blob + 65b7591325957779ac91c4e9b70044515c5c8688 (mode 755) --- /dev/null +++ archive/postgresql-perf-tools/pg-top.py @@ -0,0 +1,513 @@ +#!/usr/bin/python + +import os +import sys +import time + +HAS_PA = True +oldpath = sys.path +try: + bindir, testscript= os.path.split(sys.argv[0]) + sys.path.append(os.path.join(bindir, "..", "lib")) + import pa_config +except ImportError: + HAS_PA = False +finally: + sys.path = oldpath + +import psycopg2 +from optparse import OptionParser, OptionGroup +import logging +import inspect + +import threading +import curses +import mutex +import StringIO +import traceback +import copy + + +class DB: + def __init__(self, host, port, database, user, password): + self.host = host + self.port = port + self.database = database + self.user = user + self.password = password or "" + + def get_name(self): + return self.database + + def __str__(self): + return "%s@%s:%s db %s" % (self.user, self.host, self.port, self.database) + + def connect(self): + return psycopg2.connect(**(self.__dict__)) + + @staticmethod + def _execute_fetch(con, query, *args): + cur = con.cursor() + + @staticmethod + def _execute_fetch(con, query, fetchfn, *args): + cur = con.cursor() + try: + if args is None or not len(args): + logging.debug(query) + cur.execute(query) + else: + logging.debug(query % args) + cur.execute(query, args) + return fetchfn(cur) + finally: + cur.close() + + @staticmethod + def execute_fetchone(con, query, *args): + return DB._execute_fetch(con, query, lambda cur : cur.fetchone(), *args) + + @staticmethod + def execute_fetchall(con, query, *args): + return DB._execute_fetch(con, query, lambda cur : cur.fetchall(), *args) + + @staticmethod + def execute_fetchval(con, query, *args): + ret = DB.execute_fetchone(con, query, *args) + if ret is not None and len(ret) > 0: + return ret[0] + + +KEY_LEFT = 68 +KEY_RIGHT = 67 + +USER_COL_NAME = 0 +USER_COL_WIDTH = 1 +USER_COL_TYPE = 2 +USER_COL_ABS = 3 +USER_COL_METRIC = 4 +USER_COL_SQL_NAME = 5 +USER_COL_HELP = 6 + +user_cols_def = [ + # title #width #type #abs #metric #sql_name #help + ["Table", 0, "str", True, "", "tablename", "table name"], + ["DB", 5, "str", True, "", "dbname", "database"], + ["Write", 6, "int", False, "row/s", "writes", "total number of inserted/updated/deleted rows per sec"], + ["Ins", 6, "int", False, "row/s", "n_tup_ins", "number of inserted rows per second"], + ["Upd", 6, "int", False, "row/s", "n_tup_upd", "number of updated rows per second"], + ["Del", 6, "int", False, "row/s", "n_tup_del", "number of deleted rows per second"], + ["UpdIdx", 8, "float", False, "row/s", "n_tup_idx_upd", "number of rows updated with index update"], + ["IdxScan", 9, "int", False, "scan/s", "idx_scan", "number of index scans per second"], + ["SeqScan", 9, "int", False, "scan/s", "seq_scan", "number of sequential scans per second"], + ["SeqRows", 9, "int", False, "row/s", "seq_tup_read", "number of rows per second fetched by seq scans"], + ["Locks", 6, "int", True, "count", "locks", "number of processes waiting for lock"], + ["Reltuples", 10, "int", True, "count", "reltuples", "approximate number of rows in table"] +] + +user_cols_select_query = """ +SELECT + %s +FROM + pg_stat_user_tables U + + LEFT JOIN ( + SELECT + case WHEN schemaname = 'public' + THEN + relname + ELSE + schemaname || '.' || relname + END tablename, + schemaname AS T_schemaname, + relname AS T_relname, + current_database() dbname, + (n_tup_ins + n_tup_upd + n_tup_del) writes, + (n_tup_upd - n_tup_hot_upd) n_tup_idx_upd + FROM + pg_stat_user_tables + ) T ON (T_relname = relname AND T_schemaname = U.schemaname) + + LEFT JOIN ( + SELECT + relname AS L_relname, + nspname AS L_schemaname, + reltuples, + case WHEN L_locks > 0 + THEN + L_locks + ELSE + 0 + END locks + FROM + pg_class C + + LEFT JOIN ( + SELECT + relation, + COUNT(*) L_locks + FROM + pg_locks + WHERE + NOT granted + GROUP BY + relation + ) L ON L.relation = C.oid + + LEFT JOIN pg_namespace n ON n.oid = C.relnamespace + + ) L ON (L_relname = relname AND L_schemaname = U.schemaname) +""" + +user_cols_select_query_for_schema = user_cols_select_query + "WHERE U.schemaname = '%s'" + + +class PgTop: + def __init__(self): + self.scr = None + self.opts = None + self.con = [] + + self.paused = 0 + self.terminate = False + + self.mutex = threading.Lock() + self.prev_time = 0 + + if sys.stderr.isatty(): + sys.stderr = StringIO.StringIO() + + self.init_user_cols() + + def init_user_cols(self): + self.user_cols_sorted = 0 + self.user_cols_hash = {} + self.user_cols_data_prev = {} + self.user_cols_meta = [] + self.user_cols_view_data = [] + self.user_cols_view_ctime = time.ctime() + + for col in user_cols_def: + + # hide dB if there is only one DB + if col[USER_COL_NAME].lower() == "db" and len(self.con) == 1: + continue + + self.user_cols_meta.append(col) + + for n in xrange(0, len(self.user_cols_meta)): + self.user_cols_hash[self.user_cols_meta[n][USER_COL_NAME]] = n + if self.opts and self.user_cols_meta[n][USER_COL_NAME] == self.opts.sort: + self.user_cols_sorted = n + + def init(self, scr, con, opts): + self.scr = scr + self.con = con + self.opts = opts + self.init_user_cols() + + def fetch_user_cols(self): + cols = ", ".join([c[USER_COL_SQL_NAME] for c in self.user_cols_meta]) + data = [] + for name, con in self.con.items(): + if self.opts.schema: + data += DB.execute_fetchall(con, user_cols_select_query_for_schema % (cols, self.opts.schema)) + else: + data += DB.execute_fetchall(con, user_cols_select_query % cols) + con.commit() + return data + + def update_user_cols_view(self): + self.user_cols_view_ctime = time.ctime() + sql_data = self.fetch_user_cols() + + total = [0] * len(self.user_cols_meta) + total[0] = "Total" + + for data in sql_data: + for n in xrange(1, len(data)): + if self.user_cols_meta[n][USER_COL_TYPE] == "str": + total[n] = "" + else: + total[n] += data[n] if data[n] else 0 + sql_data = [total] + sql_data + + user_data = {} + for r in sql_data: + user_data[r[0]] = r + + if not self.prev_time: + self.user_cols_data_prev = user_data + self.prev_time = time.time() + return None + + self.user_cols_view_data = [] + + t = time.time() + for data in sql_data: + out = [] + + table = data[0] + + for n in xrange(0, len(data)): + if self.user_cols_meta[n][USER_COL_TYPE] == "str": + s = str(data[n]) + w = self.user_cols_meta[n][USER_COL_WIDTH] + if len(s) > w: + s = s[0:w-3] + "..." + out.append(s) + elif self.user_cols_meta[n][USER_COL_ABS]: + out.append(data[n]) + else: + new = data[n] if data[n] else 0 + old = self.user_cols_data_prev[table][n] if self.user_cols_data_prev.has_key(table) and \ + self.user_cols_data_prev[table][n] else 0 + out.append(new - old) + + for n in xrange(0, len(out)): + if self.user_cols_meta[n][USER_COL_METRIC].endswith("/s"): + if t - self.prev_time: + out[n] = int(out[n]) / (t - self.prev_time) + if self.user_cols_meta[n][USER_COL_TYPE] == "int": + out[n] = round(out[n]) + + self.user_cols_view_data.append(out) + + self.user_cols_data_prev = user_data + self.prev_time = t + + def get_user_cols_view_data(self): + return sorted(self.user_cols_view_data, key=lambda x: + (x[self.user_cols_sorted], + x[self.user_cols_hash['Write']], + x[self.user_cols_hash['Reltuples']]), + reverse=True) + + def _refresh(self): + if not self.scr or self.terminate: + return + (max_y, max_x) = self.scr.getmaxyx() + + s = sum([c[1] + 1 for c in self.user_cols_meta]) + s -= self.user_cols_meta[0][USER_COL_WIDTH] + self.user_cols_meta[0][USER_COL_WIDTH] = max_x - s + + fmt = [] + for c in self.user_cols_meta: + if c[USER_COL_TYPE] == "int": + fmt.append("%%%dd" % c[1]) + elif c[USER_COL_TYPE] == "float": + fmt.append("%%%d.1f" % c[1]) + else: + fmt.append("%%%ds" % c[1]) + fmt_data = " ".join(fmt) + fmt_header = " ".join(["%%%ds" % c[USER_COL_WIDTH] for c in self.user_cols_meta]) + + self.scr.erase() + self.scr.addstr(0, 0, "%s | Use: 'left' and 'right' keys - select sortable col; 'p' pause; 'q' quit; 'space' refresh" + % self.user_cols_view_ctime) + if self.paused: + self.scr.addstr(0, 0, "PAUSED! ") + self.scr.addstr(1, 0, "=" * max_x) + columns = [] + metrics = [] + + for n in xrange(0, len(self.user_cols_meta)): + c = self.user_cols_meta[n] + metrics.append(c[USER_COL_METRIC]) + if self.user_cols_sorted == n: + columns.append("*" + c[USER_COL_NAME]) + else: + columns.append(c[USER_COL_NAME]) + + self.scr.addstr(2, 0, fmt_header % tuple(columns)) + self.scr.addstr(3, 0, fmt_header % tuple(metrics)) + + if not self.paused: + self.update_user_cols_view() + view = self.get_user_cols_view_data() + + if view == None: + return + + self.scr.addstr(4, 0, "-" * max_x) + r = 4 + for row in view: + r += 1 + if r == max_y: + break + self.scr.addstr(r, 0, fmt_data % tuple(row)) + + self.scr.refresh() + + def refresh(self): + self.mutex.acquire() + try: + self._refresh() + except: + self.mutex.release() + raise + self.mutex.release() + + def shift_sorted_col(self, shift): + self.user_cols_sorted = (len(self.user_cols_meta) + self.user_cols_sorted + shift) % len(self.user_cols_meta) + + def handle_key(self, key): + if ord(key) == KEY_LEFT: + self.shift_sorted_col(-1) + elif ord(key) == KEY_RIGHT: + self.shift_sorted_col(1) + elif key == 'p': + self.paused = self.paused ^ 1 + elif key == ' ': + if self.paused: + self.paused = 0 + else: + return + self.refresh() + + def getkey(self): + try: + # return chr(self.scr.getch()) - thread unsafe + key = sys.stdin.read(1) + except KeyboardInterrupt: + raise + except: + return chr(0) + + self.handle_key(key) + return key + + def handle_exc(self): + print >> sys.stderr, traceback.format_exc() + self.mutex.acquire() + curses.endwin() + self.mutex.release() + self.deinit() + + def deinit(self): + if isinstance(sys.stderr, StringIO.StringIO): + print sys.stderr.getvalue() + sys.stderr = sys.stdout + sys.stdout.flush() + + def __del__(self): + self.deinit() + + +def main_loop(pgt): + try: + pgt.refresh() + time.sleep(0.3) + while 1: + if not pgt.paused: + pgt.refresh() + time.sleep(pgt.opts.delay) + if pgt.terminate: + return + except: + pgt.handle_exc() + os._exit(1) + +def pg_top(scr, pgt, con, opts): + + curses.noecho() # disable echo + curses.cbreak() # keys are read directly, without hitting Enter +# curses.curs_set(0) # disable mouse + + pgt.init(scr, con, opts) + t = threading.Thread(target=main_loop, args=(pgt,)) + t.daemon = True + t.start() + + while 1: + try: + key = pgt.getkey() + if key == 'q': + pgt.terminate = True + break + except KeyboardInterrupt: + break + pgt.terminate = True + +def main(): + test_description = "%prog [options]" + pgt = PgTop() + + epilog = "\nCounters description:" + for c in pgt.user_cols_meta: + epilog += "\n%9s - %s" % (c[USER_COL_NAME], c[USER_COL_HELP]) + + class PgOptParser(OptionParser): + def format_epilog(self, formatter): + return self.epilog + "\n" + + p = PgOptParser(test_description, epilog=epilog) + p.add_option("-v", "--verbose", action="store_true", help="enable verbose mode") + p.add_option("-d", "--delay", type=int, default=2, help="delay between database poll (sec)") + p.add_option("-n", "--count", type=int, default=0, help="exit after COUNT iterations") + p.add_option("-a", "--abs", action="store_true", help="show absolute values, not rates") + p.add_option("-s", "--sort", type="choice", default="Write", + choices=tuple([c[USER_COL_NAME] for c in user_cols_def]), help="sort by given column (default is '%default')") + p.add_option("-S", "--schema", type="string", + help="take into account only given schema (default: all schemas)") + + defdb = "" + defusr = "postgres" + if HAS_PA: + g = OptionGroup(p, "If you have pa.conf") + g.add_option("-c", "--config", type="string", default=None, help="PA config file [default: %default]") + g.add_option("", "--poa", action="store_true", help = "connect to POA only") + g.add_option("", "--pba", action="store_true", help = "connect to PBA only") + p.add_option_group(g) + defdb = "plesk" + defusr = "plesk" + + g = OptionGroup(p, "If you don't have pa.conf" if HAS_PA else "Database credentials") + g.add_option("", "--db-host", type="string", help="database hostname/IP") + g.add_option("", "--db-port", type="string", default=5432, help="database port") + g.add_option("", "--db-name", type="string", default=defdb, help="database name [default: %default]") + g.add_option("", "--db-user", type="string", default=defusr, help="database username [default: %default]") + g.add_option("", "--db-pass", type="string", default="", help="database password") + p.add_option_group(g) + + opts, args = p.parse_args() + loglevel = logging.DEBUG if opts.verbose else logging.WARNING + logging.basicConfig(level=loglevel, format="%(asctime)s - %(module)s - %(levelname)s - %(message)s") + + dbs = [] + con = {} + + if HAS_PA: + if not opts.config and not opts.db_host: + p.error("either -c or --db-host option must be provided") + else: + if not opts.db_host: + p.error("--db-host option must be provided") + + if HAS_PA and opts.config: + non = not opts.pba and not opts.poa + pa_config.init(opts.config) + cfg = pa_config.get() + if (non or opts.pba) and cfg.pba_db.host: + dbs.append(DB(cfg.pba_db.ip, cfg.pba_db.db_port, cfg.pba_db.db_name, cfg.pba_db.db_user, cfg.pba_db.db_pass)) + if (non or opts.poa) and cfg.poa_db.host: + dbs.append(DB(cfg.poa_db.ip, cfg.poa_db.db_port, cfg.poa_db.db_name, cfg.poa_db.db_user, cfg.poa_db.db_pass)) + else: + dbs.append(DB(opts.db_host, opts.db_port, opts.db_name, opts.db_user, opts.db_pass)) + + for db in dbs: + print "Connecting to %s..." % str(db) + try: + con[db.get_name()] = db.connect() + except Exception, x: + print "failed to connect: ", type(x), str(x) + + if con: + try: + curses.wrapper(pg_top, pgt, con, opts) + except: + pgt.handle_exc() + +if __name__ == "__main__": + main()