Postgresql – RDS Postgres – pg_stat_statements is missing queryid column

amazon-rdspostgresql

I upgraded my RDS postgres instances from 9.3 -> 9.4 -> 9.5 several months ago. When installing the pganalyzer-collector tool, I got a funny error that the "queryid" column does not exist in the pg_stat_statements table. It looks like that column was added in version 9.4. I'm now running 9.5 and expect to see this column in that table.

How could this have happened? And how can I make this column appear in the pg_stat_statements table?

Best Answer

Update: Lukas from pganalyze helped me. How nice! The answer was as follows:

DROP FUNCTION pganalyze.get_stat_statements();
ALTER EXTENSION pg_stat_statements UPDATE;
CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
/* pganalyze-collector */ SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Related Solutions

Mysql – CPU usage on RDS instance monotonically increasing with no change to query volume

I have some queries for you regarding table sizes that you can run in MySQL during these spikes

Database size in terms of StorageEngine (MB)

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Database size in terms of Databases (MB)

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT
IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Database size in terms of Database/StorageEngine (MB)

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(REPLACE(FORMAT(
B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Pay attention to certain markers

Innodb_buffer_pool_pages_dirty
Innodb_data_reads
Innodb_data_writes

I recommend downloading MySQL Administrator (I know, it's old but I still you it for quick and dirty "I WANNA SEE STATS NOW" moments of day) and set it up. I customized my own graphs to watch the size of the InnoDB Buffer Pool and its dirty pages. You could also just use the Connection Health tab.

Mysql – moving to AWS RDS (InnoDB vs MyISAM

Without knowing your dataset size, or your usage (read or write heavy? queries per second?), here are some generalities:

InnoDB has improved drastically in even the last few years performance-wise. This, along with the (mostly) ACID compliance and row-level locking, is why it's now the default in 5.5+
If you're not using full text indexes in your MyISAM table, you should be fine switching to InnoDB
Make sure you know the changes to the mysql version you're going to on RDS. Performance might not be the only 'gotcha' on moving to InnoDB at RDS. If RDS is newer than your current application, make sure there's not any features that changed. You do this by:
Test it!! Don't make the move on your application until you've tested it out.

One method to test it is to turn on the general query log for a week or so (if the performance hit won't be an issue) and then run it against the RDS instance to see how it runs.

Disclaimer: I'm not an expert on RDS, so I don't know the best way to test against it.

Best Answer

Related Solutions

Mysql – CPU usage on RDS instance monotonically increasing with no change to query volume

Mysql – moving to AWS RDS (InnoDB vs MyISAM

Related Question