commit - af20b270334f2eaa1bd7d1f1a0605e2560d37bd0
commit + e9138ee9e0cbcfe685e1f1d84d3d36f7494756b5
blob - /dev/null
blob + d6a93266f748d606b884f9434ff662fe80b9dc21 (mode 644)
--- /dev/null
+++ archive/postgresql-perf-tools/LICENSE
+GNU GENERAL PUBLIC LICENSE
+ Version 2, June 1991
+
+ Copyright (C) 1989, 1991 Free Software Foundation, Inc., <http://fsf.org/>
+ 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
+## 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
+#!/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
+#!/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 = "<IDLE> 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("<IDLE>"):
+ 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
+#!/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()
