Postgresql – Monitoring Amazon RDS with pgtop

The first thing that comes to mind is the Server Model : db.m1.large

What limiting factors are placed on a MySQL RDS?

If you spin up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

• max_connections
• innodb_buffer_pool_size

Here is a Chart I posted Last Month

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I posted this in my other posts in the DBA StackExchange

• Local database vs Amazon RDS
• Scaling Percona datacenters: setup and replication

You are using m1.large. Since the InnoDB Buffer Pool is 3/4 of the Instance. That's means you have 7.2G but only 1.8 is usable. That model is capable of having up to 623 connections. Each connection can consume memory because of

• join buffers
• sort buffers
• read buffers
• thread info

Amazon RDS is simply micromanaging resources. Since DB Connections can consume RAM, connections are probably being disallowed due to the lack of RAM needed.

SUGGESTIONS

• Try reducing InnoDB log IO innodb_flush_log_at_trx_commit=0 during the mass creation
• Make sure you are not doing large transactions during any automatic backups or snapshots
• Try a bigger server model m1.xlarge or m2.xlarge

That's not really a PostgreSQL monitoring tool, that's PostgreSQL's internal facilities for diagnostics, monitoring and database statistics.

For host monitoring you want a tool like Icinga (modernized Nagios) or Zabbix. When monitoring PostgreSQL the check_postgres tool is likely to be of interest.

If you're interested in getting the CPU use of a particular PostgreSQL backend process from your own custom programs, you must SELECT pg_backend_pid() or get the pid of the task of interest from pg_stat_activity then use OS-level tools and APIs to collect the desired information.

If you desired, you could write a PostgreSQL C extension function to collect the information and return it as a row or rowset over the PostgreSQL protocol, so you could SELECT my_system_stats(). See:

• C-language functions
• Extensions

Essentially, you write a library in C using the PostgreSQL APIs, compile it using the pgxs make helper and have the server dynamically load the resulting shared object. Then you tell it what C functions are callable from SQL using CREATE FUNCTION. You can then call them like any other function. C extensions can call OS-level APIs or do pretty much whatever they want, though it is generally very unwise for them to create threads, write directly to PostgreSQL files/filehandles, or read/write PostgreSQL sockets.