Percona Server moved to https://jira.percona.com/projects/PS

Add status counters for tuning innodb_concurrency_tickets

Registered by aeva black

Right now there is no way to tell how innodb_concurrency_tickets is affecting performance, and whether it should be adjusted up/down. It would be helpful to add the following innodb status counters.

1) # of times a thread has slept before joining queue
  this tells us how often we reach innodb_thread_concurrency and make a thread wait before
starting

2) # of times a thread has used up all available tickets and been put into queue
  this tells us how often innodb_concurrency_tickets is smaller than necessary for a
single statement

3) # of times OS_THREAD_MAX_N was met and thread entered innodb anyway
  this would indicate something bad??

4) total time waited by all threads
  how much queuing was affecting performance

Blueprint information

Status:
Not started
Approver:
None
Priority:
Medium
Drafter:
None
Direction:
Approved
Assignee:
None
Definition:
New
Series goal:
None
Implementation:
Unknown
Milestone target:
None

Related branches

Sprints

Whiteboard

I believe these can be answered at the following lines of file srv/srv0srv.c, using line
numbers from latest 5.1.45 source release

1) add after line 1060

1055 /* If the transaction is not holding resources, let it sleep
1056 for SRV_THREAD_SLEEP_DELAY microseconds, and try again then */
1057
1058 if (!has_slept && !trx->has_search_latch
1059 && NULL == UT_LIST_GET_FIRST(trx->trx_locks)) {
1060
1061 has_slept = TRUE; /* We let it sleep only once to avoid
1062 starvation */
1063
1064 srv_conc_n_waiting_threads++;

2) add "if (trx->n_tickets_to_enter_innodb == 0) at line 1024

1019 /* If trx has 'free tickets' to enter the engine left, then use one
1020 such ticket */
1021
1022 if (trx->n_tickets_to_enter_innodb > 0) {
1023 trx->n_tickets_to_enter_innodb--;
1024
1025 return;
1026 }
1027

3) add before line 1109

1099 if (i == OS_THREAD_MAX_N) {
1100 /* Could not find a free wait slot, we must let the
1101 thread enter */
1102
1103 srv_conc_n_threads++;
1104 trx->declared_to_be_inside_innodb = TRUE;
1105 trx->n_tickets_to_enter_innodb = 0;
1106
1107 os_fast_mutex_unlock(&srv_conc_mutex);
1108
1109 return;
1110 }

4) calculate difference of microtime between lines 1133 and 1135

1125 srv_conc_n_waiting_threads++;
1126
1127 os_fast_mutex_unlock(&srv_conc_mutex);
1128
1129 /* Go to wait for the event; when a thread leaves InnoDB it will
1130 release this thread */
1131
1132 trx->op_info = "waiting in InnoDB queue";
1133
1134 os_event_wait(slot->event);
1135
1136 trx->op_info = "";
1137
1138 os_fast_mutex_lock(&srv_conc_mutex);
1139
1140 srv_conc_n_waiting_threads--;

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.
Edit subscription

Subscribers

No subscribers.