How to get transaction metrics?

Help
1

Hello. I have implemented memSQL to monitor my system performance.
But unfortunately, I am not able to find the transaction-related metrics. For example the number of transactions committed/read/written etc. Is there any option to obtain those metrics without the help of grafana or Prometheus (Perhaps a table maintaining such data and can be queried?) ?
Also, I have implemented the same in MySQL with the help of innodb. Is there any equivalent of innodb in memSQL?

Any help/pointers will be appreciated.
Thank you.

2

Hi

Thank you for reaching out!

Are you using MemSQL’s monitoring solution link below? Our solution does collect this information from the cluster and surfaces it one of the Grafana dashboards we provide.

Regardless, it sounds like you are trying to find the source of this information directly in the database?

For read/write query rate and rows/read written, we use the following values from the information_schema.mv_global_status table.

Note these tables provide a one-time snapshot, which is why using MemSQL’s monitoring solution is useful because our exporter can collect this information and calculate the deltas to present the rate over time.

For the number of successful read/write queries:

memsql> select * from information_schema.mv_global_status where variable_name like "%f%_queries%";
+---------+--------------+------+-----------+--------------------------+----------------+
| NODE_ID | IP_ADDR      | PORT | NODE_TYPE | VARIABLE_NAME            | VARIABLE_VALUE |
+---------+--------------+------+-----------+--------------------------+----------------+
|       1 | 172.31.77.34 | 3306 | MA        | Successful_read_queries  | 396            |
|       1 | 172.31.77.34 | 3306 | MA        | Successful_write_queries | 0              |
|       1 | 172.31.77.34 | 3306 | MA        | Failed_read_queries      | 0              |
|       1 | 172.31.77.34 | 3306 | MA        | Failed_write_queries     | 0              |
|       2 | 172.31.77.34 | 3307 | LEAF      | Successful_read_queries  | 20192          |
|       2 | 172.31.77.34 | 3307 | LEAF      | Successful_write_queries | 0              |
|       2 | 172.31.77.34 | 3307 | LEAF      | Failed_read_queries      | 0              |
|       2 | 172.31.77.34 | 3307 | LEAF      | Failed_write_queries     | 0              |
+---------+--------------+------+-----------+--------------------------+----------------+

For the number of rows/read written:

memsql> select * from information_schema.mv_global_status where variable_name like "rows%";
+---------+--------------+------+-----------+-------------------------+----------------+
| NODE_ID | IP_ADDR      | PORT | NODE_TYPE | VARIABLE_NAME           | VARIABLE_VALUE |
+---------+--------------+------+-----------+-------------------------+----------------+
|       2 | 172.31.77.34 | 3307 | LEAF      | Rows_returned_by_reads  | 21584          |
|       2 | 172.31.77.34 | 3307 | LEAF      | Rows_affected_by_writes | 0              |
|       1 | 172.31.77.34 | 3306 | MA        | Rows_returned_by_reads  | 332            |
|       1 | 172.31.77.34 | 3306 | MA        | Rows_affected_by_writes | 0              |
+---------+--------------+------+-----------+-------------------------+----------------+

Let me know if you have any other questions.

Best,
Roxanna

1 Like
3

Hello Roxanna,

Thank you very much for your response. It is very helpful and this solves my problem.

But, I have another question. Which I think is already implied by your answer. Still, I just want to be sure.

Is there any way where we can reset the counters and statistics maintained by these tables without having to restart the database?

Also, I see that the ‘Queries’ column is not the sum of (Successful_read_queries, Successful_write_queries, Failed_read_queries, Failed_write_queries) for each node. Queries value is always greater than the sum as I have observed in my setup. Apart from read/write queries, what other statistics does this include?

Thanks and Regards,
Meghana Deshmukh

1 Like
4

Hi Meghana,

  1. For your first question, your assumption is correct; there is no way to reset the stats unless you restart.

  2. For your second question; that is expected. The “Queries” number is implemented differently and also includes DDL statements.

Best,
Roxanna

1 Like
5

Hi Roxanna,

Thank you very much for the response! :slight_smile:

Regards,
Meghana

6

Happy to help! Let us know if you have any other questions.

Cheers,
Roxanna