Optimize userstat code

Registered by Alexey Kopytov on 2013-07-15

Converting an internal email by Alexey Stroganov to a blueprint.

I've profiled mysqld with oprofile, see below some functions re: userstat:

samples %
245479 0.4232 mysqld update_global_user_stats(THD*,
bool, long)
233103 0.4019 mysqld get_key_index_stats
195836 0.3376 mysqld
handler::update_global_table_stats()
186488 0.3215 mysqld
handler::update_global_index_stats()
64076 0.1105 mysqld get_key_user_stats
62424 0.1076 mysqld get_key_table_stats

I've annotated sources and it seems that we:
- quite often use strlen:
...
 58285 0.1005 : *length= strlen(user_stats->user);
 59393 0.1024 : *length= strlen(table_stats->table);
220833 0.3807 : *length= strlen(index_stats->index);
...

- accessing thd:
...
 38274 0.0660 : user_stats->connected_time+= now -
thd->last_global_update_time;
 43263 0.0746 : user_stats->rows_updated+=
thd->diff_total_updated_rows;
...

Blueprint information

Status:
Complete
Approver:
Alexey Kopytov
Priority:
High
Drafter:
Alexey Stroganov
Direction:
Approved
Assignee:
Sergei Glushchenko
Definition:
Approved
Series goal:
Accepted for 5.6
Implementation:
Implemented
Milestone target:
milestone icon 5.6.15-63.0
Started by
Alexey Kopytov on 2013-09-16
Completed by
Alexey Kopytov on 2013-12-16

Whiteboard

Targetting to 5.6 GA, but any work done that also applies to 5.5 should be done there too and will need creating separate blueprints/bug reports.

Sergei has questions (based on 5.5 branch):

- It's easy to get rid of that much strlen's, just to save lengths in
  *_STAT structs. The whole point of this feature is to get stats from
  THD and aggregte them grouped by user, thread, table, etc. I don't see
  a good way how to ret rid o accessing THD. Is there one?

KAlexey> saving lengths in _STAT structs looks like a good idea. Though I'm not sure accessing THD per se has any measurable overhead. It's more likely that incrementing the _STAT structure members suffers from false sharing (since they are shared). But it needs further investigation.

- Functions add_user_stats, add_thread_stats are never used, can we just
  remove them?

KAlexey> yes, this is bug #1082333.

- Condition opt_userstat in update_global_user_stats seems never
  become false, what is point of it's "else" branch?

KAlexey> Agree, that check is redundant.

Laurynas: the MP was merged in its current form (eliminate strlen(), couple of unused functions, one redundant if). If the THD accesses need to be addressed, this will have to be done in a new userstat benchmarking round and a follow-up blueprint.

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.