How to find statistics of how many queries have run on my mariadb database?

Question

I have a website that enables end users to search a Fulltext Index in mariadb database using terms that they input.

The queries are like (select columnnames from tablename where match from tablename where match(column_names) against USER_input

Using the results returned from that query, they can click on a result that returns a particular record (a row with several columns).

How can I find out how many SELECT queries have been run on this database?

I just want to know how many queries have been run on the database and if there is also a way in mariadb where I can also parse this statistic by month, day, and year, that would be great.

I do have userstat enabled.

Answer 1

mysql> SHOW GLOBAL STATUS LIKE 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 9669  |
+---------------+-------+

Says (in MySQL or MariaDB) that 9669 SELECTs have been run since mysqld was last started.

The is no breakdown available by time.

The "General log" can be used to record all queries. But this log would need to be parsed, etc, to come up with the details you like. And, this log would rapidly fill up the disk; so don't consider using it for months. (Even one day is risky, space-wise.)

For MariaDB and Percona versions:

For an individual user, it is something like

SELECT SELECT_COMMANDS
    FROM INFORMATION_SCHEMA.USER_STATISTICS
    WHERE user = '...';

For all users:

SELECT user, SELECT_COMMANDS
    FROM INFORMATION_SCHEMA.USER_STATISTICS;