[Maria-developers] Changes to table_statistics for mariadb 5.2

28 Jul 2011

      Hi,

I've been working on some improvements to the table and index
statistics in mariadb that allow tracking on the session and query
level. I've been pushing the changes to
lp:~provenscaling-eric/maria/tivo The long form version of where I'm
at in my hacking is at

http://ebergen.net/wordpress/2011/07/28/second-update-of-modifying-table-sta...

I want to poll this list for ides on a few places I'm stuck and get
the review process started to hopefully get some of these changes
merged back into mainline mariadb. My first issue is around allocation
of query ids for both show profiles and show table_statistics; Table
statistics are enabled by userstat and profiles are controlled by
profiling. Enabling these options at different times makes some
confusing results:

mysql> use test;
Database changed
mysql> set query_statistics_history_size=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t limit 5;
+-------+--------+
| t     | u      |
+-------+--------+
| 14515 | 282874 |
| 14521 | 258653 |
| 14573 | 113276 |
| 14577 | 826475 |
| 14585 | 444645 |
+-------+--------+
5 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        7 | 0.00052300 | select * from t limit 5 |
+----------+------------+-------------------------+
1 row in set (0.00 sec)

mysql> set profiling=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t limit 10;
+-------+--------+
| t     | u      |
+-------+--------+
| 14515 | 282874 |
| 14521 | 258653 |
| 14573 | 113276 |
| 14577 | 826475 |
| 14585 | 444645 |
| 14612 | 792545 |
| 14626 | 483300 |
| 14842 | 447267 |
| 15325 | 38865  |
| 15340 | 744424 |
+-------+--------+
10 rows in set (0.00 sec)

mysql> show query table_statistics;
+----------+--------------+------------+-----------+--------------+-------------------------+
| Query_id | Table_schema | Table_name | Rows_read | Rows_changed |
Rows_changed_x_#indexes |
+----------+--------------+------------+-----------+--------------+-------------------------+
|        2 | #temp#       | #temp#     |        15 |           15 |
                  15 |
|        7 | test         | t          |         5 |            0 |
                   0 |
|       10 | test         | t          |        10 |            0 |
                   0 |
+----------+--------------+------------+-----------+--------------+-------------------------+
3 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        7 | 0.00052300 | select * from t limit 5 |
+----------+------------+-------------------------+
1 row in set (0.00 sec)

My current thought is to create a show query history command that
unifies both show profiles and user stats but only table stats or
profiles will be available for any given query depending on the flags
set when it ran. I can also create a separate show queries command
only for table statistics. Any ideas on how to unify the two different
profiling methods or split up the syntax?

-- 
Eric Bergen
eric.bergen@gmail.com
http://www.ebergen.net

Eric Bergen

Sergei Golubchik

Eric Bergen

Sergei Golubchik

Michael Widenius

tags

participants (3)