How to find CPU and RAM usage for running a query in PostgreSQL and MySQL

Question

I am trying to calculate the RAM and CPU usage in PostgreSQL and MySQL installed in ubuntu 18.04. While running the below query in both the databases, having same workload (TPCH).

select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from LINEITEM where l_shipdate <= date '1998-12-01' - interval '108' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;

In PostgreSQL, i used below command to capture changes.

ps -a -U root -u postgres -0 pid,cmd,pcpu,pmem,thcount,psr|grep "postgres: 10/main: postgres"|grep -v "grep"

Captured measurements for postgreSQL are:

Response time: 6270,516 ms
%RAM: 0,45
%CPU: 44,009
Threads:1 
Cores used: 4

In Mysql, i used the below command to capture changes.

ps -e -0 pid,cmd,pcpu,pmem,thcount,psr|grep "/usr/sbin/mysqld --daemoniz"|grep -v "grep"

Captured measurements for Mysql are:

Response time: 13348,161 ms
%RAM: 3.82
%CPU: 99.76
Threads:28
Cores used: 4

If i capture the metrics again, there are changes in %CPU for both the databases (variance of around 10 to 30%), what is the reason for this change ?

The percentage RAM, CPU and response time for the query in Mysql is very high compared to postgreSQL, What would be reason behind it ? or is there another way to do it ?

Please consider posting A) SHOW CREATE TABLE lineitems; and B) EXPLAIN SELECT (your query) so we can see how the optimizer thinks your query will be processed for each system. — Wilson Hauck
With ubuntu OS, from command prompt, could you use top to determine CPU% used for the process when it is running? — Wilson Hauck
@WilsonHauck,'top' and 'ps' are giving the same values, i am using 'ps' command specifically to, grab the DBMS process using its process name. — jovel
Is there a reason pid and cmd were left off the posted clip? Please consider posting A) SHOW CREATE TABLE lineitems; and B) EXPLAIN SELECT (your query) so we can see how the optimizer thinks your query will be processed for each system. — Wilson Hauck

Oded BD Oded BD · Accepted Answer · 2020-01-04T17:32:14

The differences could be related to caching and to different optimizations by each DB engine.

Here is how to clear cache in Postgres:

service postgresql stop
sync
echo 3 > /proc/sys/vm/drop_caches
service postgresql start

and in MYSQL:

RESET QUERY CACHE;

To monitor the process over time you can use the methods suggested in this post

How to find CPU and RAM usage for running a query in PostgreSQL and MySQL

1 Answers