I'd like to know if when I run a query, the database's contents are in my system's RAM. The dataset is approx 4.1 gb, my machine has 8gb of RAM. Am I reading from disk every time I run a SELECT or UPDATE query?
3 Answers
Aside from monitoring IO activity as others have suggested, you can also run a query to take advantage of PostgreSQL's stats tracking.
The following query will show your cache hit rate. If you hitting only cache, the hit rate should be somewhere around the .99 or higher range, if your doing a lot of disk reads, it'll be lower.
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;
This query, and other performance queries can be found here
2 Comments
Adam Gent
He can also look here for official doc on stat collection: postgresql.org/docs/9.1/static/monitoring-stats.html
jjanes
This provides asymmetric information. If the hit rate is very high, you know it coming from memory. But if it is low, you don't know whether it is truly coming from disk, or is coming from the OS managed cache.
All systems allows to track a IO activity. So you can use a system monitoring tools - there is a iotop for linux for example.
Comments
If that query is the only thing actively running on the system, use the system tools (vmstat, sar) to see if there is a spike in IO when it executes. If there is a lot of other things going on, it can be very hard to figure out what you want, as there is no easy way to distinguish data actually read from disk from data read from the OS's file system cache. You can turn on track_io_timing and see if the resulting times are consistent with the data coming from RAM.
UPDATEas your database will not be ACID compliant if it only wrote to memory as memory is not durable (the D in ACID).