[Maria-developers] Changes to table_statistics for mariadb 5.2
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
Hi, Eric! On Jul 28, Eric Bergen wrote:
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:
Why wouldn't you introduce a persistent session query counter? Like, put it in thd, increment on every query, and show this number in profiling and userstat.
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?
Ah, good idea. Indeed, both profiling and userstat need to keep the list of queries. It would make sense to have internal query history API, something like: enable_query_history() - can be called by profiling or userstat disable_query_history() get_query_str(id) get_query_count() you can keep it completely internal, without SHOW command. but both profiling and userstat will get access to query text pointers via get_query_str(). So, they'll have consistent query ids, they won't copy query text twice, etc. Note that enable_query_history() can be called many times, like set profiling=1; -> enable_query_history(); set userstat=1; -> enable_query_history(); set profiling=0; -> disable_query_history(); ^^^ this should not disable history, as userstat needs it. probably enable/disable should maintain a counter... Regards, Sergei
Hi Serg On Tue, Aug 23, 2011 at 3:47 AM, Sergei Golubchik <serg@askmonty.org> wrote:
Hi, Eric!
On Jul 28, Eric Bergen wrote:
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:
Why wouldn't you introduce a persistent session query counter? Like, put it in thd, increment on every query, and show this number in profiling and userstat.
I did that. It functions fine but it means that someone enabling profiling mid way through a session won't start with profile id 1 like they used to.
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?
Ah, good idea. Indeed, both profiling and userstat need to keep the list of queries. It would make sense to have internal query history API, something like:
enable_query_history() - can be called by profiling or userstat disable_query_history() get_query_str(id) get_query_count()
you can keep it completely internal, without SHOW command. but both profiling and userstat will get access to query text pointers via get_query_str(). So, they'll have consistent query ids, they won't copy query text twice, etc.
Note that enable_query_history() can be called many times, like
set profiling=1; -> enable_query_history();
set userstat=1; -> enable_query_history();
set profiling=0; -> disable_query_history(); ^^^ this should not disable history, as userstat needs it.
probably enable/disable should maintain a counter...
If everyone is ok with the profile id issue I highlighted above and having this change impact both profiling and table statistics I can go ahead and implement it.
Regards, Sergei
-- Eric Bergen eric.bergen@gmail.com http://www.ebergen.net
Hi, Eric! On Aug 25, Eric Bergen wrote:
Why wouldn't you introduce a persistent session query counter? Like, put it in thd, increment on every query, and show this number in profiling and userstat.
I did that. It functions fine but it means that someone enabling profiling mid way through a session won't start with profile id 1 like they used to.
I think that's fine.
Indeed, both profiling and userstat need to keep the list of queries. It would make sense to have internal query history API, something like:
enable_query_history() - can be called by profiling or userstat disable_query_history() get_query_str(id) get_query_count()
If everyone is ok with the profile id issue I highlighted above and having this change impact both profiling and table statistics I can go ahead and implement it.
Regards, Sergei
Hi!
"Sergei" == Sergei Golubchik <serg@askmonty.org> writes:
Sergei> Hi, Eric! Sergei> On Aug 25, Eric Bergen wrote:
Why wouldn't you introduce a persistent session query counter? Like, put it in thd, increment on every query, and show this number in profiling and userstat.
I did that. It functions fine but it means that someone enabling profiling mid way through a session won't start with profile id 1 like they used to.
Sergei> I think that's fine. I agree. After all, 'show processlist' also gives you a unique global id that is incrementing from the time of server start and not since the start of your session. I am still a bit confused why the 'Id' can't be same we use in show processlist. Having two id's will be confusing for anyone doing: 'show processlist' 'show profiles' 'show query table_statistics' Why not have all of these the same ? (Yes, I know that we talked about this, but if we add this to MariaDB we need to at least document, with a good reason, why these are different).
Indeed, both profiling and userstat need to keep the list of queries. It would make sense to have internal query history API, something like:
enable_query_history() - can be called by profiling or userstat disable_query_history() get_query_str(id) get_query_count()
If everyone is ok with the profile id issue I highlighted above and having this change impact both profiling and table statistics I can go ahead and implement it.
Please go ahead and do it and send an email to maria-developers@ when you want us to review it! Regards, Monty
participants (3)
-
Eric Bergen -
Michael Widenius -
Sergei Golubchik