mysql query stuck in statistics state
I was working on a customer case where I ran into an interesting problem - Query joining about 23 tables got stuck in statistics stage.
The customer need to generate a report in pipe separated values format from their database. Since the database is hosted as AWS MySQL RDS, using SELECT ... INTO OUTFILE was not an option. Therefore I decided to dump the data by using mysql command line tool along with sed command to convert tab delimited to pipe delimited column values.
I executed the mysql command on the shell and waited. But when even after some time it does not get completed, I decided to check what is going on inside mysql.
I logged into mysql and executed the SHOW processlist command. What I saw was that that the query inside mysql is stuck in statistics stage.
To understand the situation lets have a little background on how mysql receives and works on client requests.
MySQL create a thread for each connecting client (in one-thread-per-connection mode). Thread run transactions. Transaction run statements. Statement generates stages. Stage generate waits. Hence it is like:
Threads => Transactions => Statements => Stages => Waits
Every statement in MySQL goes through a Parse => Optimize => Execute phase. Our issue lies in the Optimize phase.
The task of the query optimizer is to find an optimal plan for executing the SQL query. Because the difference in performance between "good" and "bad" plans can be orders of magnitude (that is, seconds versus hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially with the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10) this is not a problem. However, when larger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.
The statistics state is where MySQL figures out which indexes to use and which order to join tables in, based on that information. The reason that it is slow is because MySQL has to figure out the optimal order in which to join the tables. This is basically a brute force algorithm, where MySQL tries every single order and figures out the best one. One can see how many possible orders there are by using N!, where N is the number of tables in the query. Such as 11! needs to examine 39 million possibilities and 12 would be 468 million. As you can imagine it can take a very long time to calculate this.
The solution for this problem is to keep your statistics up to date OR tell MySQL the order in which to join tables OR provide optimizer hints.
There are also some system variables that are related to this according to the MySQL manual.
There is a nice article MySQL: Controlling Query Optimizer to choose the Best Execution Plan on this same topic.
I choose to tell MySQL, the order in which to join the tables by using STRAIGHT_JOIN optimizer hint.
The change works in my case and MySQL started to send data right away.
The customer need to generate a report in pipe separated values format from their database. Since the database is hosted as AWS MySQL RDS, using SELECT ... INTO OUTFILE was not an option. Therefore I decided to dump the data by using mysql command line tool along with sed command to convert tab delimited to pipe delimited column values.
mysql -e "select te.EId AS \"EID\", tea.AId AS \"AID\", tea.ActivationState AS \"Status\", tp.PRDName AS \"Product\", tea.FamilyId AS \"Product Family\", tp.Ver AS \"Product Version\", tc.CSTMRName AS \"Customer Name\", tc.CSTMRIdentifier AS \"Customer ID\", tea.ActivationDateTime AS \"Activation Date\", tea.quantity AS \"Activated Quantity\", caswuid.AttrValue AS \"SWUID\", cadm.AttrValue AS \"Device Model\", cahs.AttrValue AS \"Hardware SKU\", cart.AttrValue AS \"RMA Type\", carn.AttrValue AS \"RMA Number\", carc.AttrValue AS \"RMA Comments\", cars.AttrValue AS \"Renewal Status\", caaui.AttrValue AS \"Activator User ID\", casi.AttrValue AS \"Site ID\", casn.AttrValue AS \"Site Name\", camri.AttrValue AS \"Managing Reseller ID\", camrn.AttrValue AS \"Managing Reseller Name\", caos.AttrValue AS \"Old Serial\", caoss.AttrValue AS \"Old Serial SKU\", te.EXTERNALID AS \"SSRN\", can.AttrValue AS \"Notes\", cacn.AttrValue AS \"Case Number\", carcd.AttrValue AS \"Reason Code\", cakt.AttrValue AS \"Key Type\" from T_ENT te inner join T_CSTMR tc on tc.CSTMRId = te.CSTMRId inner join T_ENT_LINE_ITEM teli on teli.ENTId = te.ENTId inner join T_ENT_ACTIVATION tea on tea.LineItemId = teli.LineItemId inner join T_PRD tp on tp.PRDId = tea.VariantID inner join T_TMPL_ATTR_VALUE caswuid on caswuid.EntityId = tea.ActivationId and caswuid.AttrName = 'SWUID' and caswuid.TemplateType = 9 inner join T_TMPL_ATTR_VALUE cadm on cadm.EntityId = tea.ActivationId and cadm.AttrName = 'Device Model' and cadm.TemplateType = 9 inner join T_TMPL_ATTR_VALUE cahs on cahs.EntityId = tea.ActivationId and cahs.AttrName = 'Hardware SKU' and cahs.TemplateType = 9 inner join T_TMPL_ATTR_VALUE cart on cart.EntityId = tea.ActivationId and cart.AttrName = 'RMA Type' and cart.TemplateType = 9 inner join T_TMPL_ATTR_VALUE carn on carn.EntityId = tea.ActivationId and carn.AttrName = 'RMA Number' and carn.TemplateType = 9 inner join T_TMPL_ATTR_VALUE carc on carc.EntityId = tea.ActivationId and carc.AttrName = 'RMA Comments' and carc.TemplateType = 9 inner join T_TMPL_ATTR_VALUE cars on cars.EntityId = tea.ActivationId and cars.AttrName = 'Renewal Status' and cars.TemplateType = 9 inner join T_TMPL_ATTR_VALUE caaui on caaui.EntityId = tea.ActivationId and caaui.AttrName = 'Activator User ID' and caaui.TemplateType = 9 inner join T_TMPL_ATTR_VALUE casi on casi.EntityId = tea.ActivationId and casi.AttrName = 'Site ID' and casi.TemplateType = 9 inner join T_TMPL_ATTR_VALUE casn on casn.EntityId = tea.ActivationId and casn.AttrName = 'Site Name' and casn.TemplateType = 9 inner join T_TMPL_ATTR_VALUE camri on camri.EntityId = tea.ActivationId and camri.AttrName = 'Managing Reseller ID' and camri.TemplateType = 9 inner join T_TMPL_ATTR_VALUE camrn on camrn.EntityId = tea.ActivationId and camrn.AttrName = 'Managing Reseller Name' and camrn.TemplateType = 9 inner join T_TMPL_ATTR_VALUE caos on caos.EntityId = tea.ActivationId and caos.AttrName = 'Old Serial' and caos.TemplateType = 9 inner join T_TMPL_ATTR_VALUE caoss on caoss.EntityId = tea.ActivationId and caoss.AttrName = 'Old Serial SKU' and caoss.TemplateType = 9 inner join T_TMPL_ATTR_VALUE can on can.EntityId = tea.ActivationId and can.AttrName = 'Notes' and can.TemplateType = 9 inner join T_TMPL_ATTR_VALUE cacn on cacn.EntityId = tea.ActivationId and cacn.AttrName = 'Case Number' and cacn.TemplateType = 9 inner join T_TMPL_ATTR_VALUE carcd on carcd.EntityId = tea.ActivationId and carcd.AttrName = 'Reason Code' and carcd.TemplateType = 9 inner join T_TMPL_ATTR_VALUE cakt on cakt.EntityId = tea.ActivationId and cakt.AttrName = 'Key Type' and cakt.TemplateType = 9 where te.TXNTYPE = 1 and teli.TXNTYPE = 1;" mydb | sed 's/\t/|/g' > my_activations_report_data.psv
I executed the mysql command on the shell and waited. But when even after some time it does not get completed, I decided to check what is going on inside mysql.
I logged into mysql and executed the SHOW processlist command. What I saw was that that the query inside mysql is stuck in statistics stage.
To understand the situation lets have a little background on how mysql receives and works on client requests.
MySQL create a thread for each connecting client (in one-thread-per-connection mode). Thread run transactions. Transaction run statements. Statement generates stages. Stage generate waits. Hence it is like:
Threads => Transactions => Statements => Stages => Waits
Every statement in MySQL goes through a Parse => Optimize => Execute phase. Our issue lies in the Optimize phase.
The task of the query optimizer is to find an optimal plan for executing the SQL query. Because the difference in performance between "good" and "bad" plans can be orders of magnitude (that is, seconds versus hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially with the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10) this is not a problem. However, when larger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.
The statistics state is where MySQL figures out which indexes to use and which order to join tables in, based on that information. The reason that it is slow is because MySQL has to figure out the optimal order in which to join the tables. This is basically a brute force algorithm, where MySQL tries every single order and figures out the best one. One can see how many possible orders there are by using N!, where N is the number of tables in the query. Such as 11! needs to examine 39 million possibilities and 12 would be 468 million. As you can imagine it can take a very long time to calculate this.
The solution for this problem is to keep your statistics up to date OR tell MySQL the order in which to join tables OR provide optimizer hints.
There are also some system variables that are related to this according to the MySQL manual.
There is a nice article MySQL: Controlling Query Optimizer to choose the Best Execution Plan on this same topic.
I choose to tell MySQL, the order in which to join the tables by using STRAIGHT_JOIN optimizer hint.
mysql -e "select te.EId AS \"EID\", tea.AId AS \"AID\", tea.ActivationState AS \"Status\", tp.PRDName AS \"Product\", tea.FamilyId AS \"Product Family\", tp.Ver AS \"Product Version\", tc.CSTMRName AS \"Customer Name\", tc.CSTMRIdentifier AS \"Customer ID\", tea.ActivationDateTime AS \"Activation Date\", tea.quantity AS \"Activated Quantity\", caswuid.AttrValue AS \"SWUID\", cadm.AttrValue AS \"Device Model\", cahs.AttrValue AS \"Hardware SKU\", cart.AttrValue AS \"RMA Type\", carn.AttrValue AS \"RMA Number\", carc.AttrValue AS \"RMA Comments\", cars.AttrValue AS \"Renewal Status\", caaui.AttrValue AS \"Activator User ID\", casi.AttrValue AS \"Site ID\", casn.AttrValue AS \"Site Name\", camri.AttrValue AS \"Managing Reseller ID\", camrn.AttrValue AS \"Managing Reseller Name\", caos.AttrValue AS \"Old Serial\", caoss.AttrValue AS \"Old Serial SKU\", te.EXTERNALID AS \"SSRN\", can.AttrValue AS \"Notes\", cacn.AttrValue AS \"Case Number\", carcd.AttrValue AS \"Reason Code\", cakt.AttrValue AS \"Key Type\" from T_ENT te straight_join T_CSTMR tc on tc.CSTMRId = te.CSTMRId straight_join T_ENT_LINE_ITEM teli on teli.ENTId = te.ENTId straight_join T_ENT_ACTIVATION tea on tea.LineItemId = teli.LineItemId straight_join T_PRD tp on tp.PRDId = tea.VariantID straight_join T_TMPL_ATTR_VALUE caswuid on caswuid.EntityId = tea.ActivationId and caswuid.AttrName = 'SWUID' and caswuid.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE cadm on cadm.EntityId = tea.ActivationId and cadm.AttrName = 'Device Model' and cadm.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE cahs on cahs.EntityId = tea.ActivationId and cahs.AttrName = 'Hardware SKU' and cahs.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE cart on cart.EntityId = tea.ActivationId and cart.AttrName = 'RMA Type' and cart.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE carn on carn.EntityId = tea.ActivationId and carn.AttrName = 'RMA Number' and carn.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE carc on carc.EntityId = tea.ActivationId and carc.AttrName = 'RMA Comments' and carc.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE cars on cars.EntityId = tea.ActivationId and cars.AttrName = 'Renewal Status' and cars.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE caaui on caaui.EntityId = tea.ActivationId and caaui.AttrName = 'Activator User ID' and caaui.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE casi on casi.EntityId = tea.ActivationId and casi.AttrName = 'Site ID' and casi.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE casn on casn.EntityId = tea.ActivationId and casn.AttrName = 'Site Name' and casn.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE camri on camri.EntityId = tea.ActivationId and camri.AttrName = 'Managing Reseller ID' and camri.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE camrn on camrn.EntityId = tea.ActivationId and camrn.AttrName = 'Managing Reseller Name' and camrn.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE caos on caos.EntityId = tea.ActivationId and caos.AttrName = 'Old Serial' and caos.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE caoss on caoss.EntityId = tea.ActivationId and caoss.AttrName = 'Old Serial SKU' and caoss.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE can on can.EntityId = tea.ActivationId and can.AttrName = 'Notes' and can.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE cacn on cacn.EntityId = tea.ActivationId and cacn.AttrName = 'Case Number' and cacn.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE carcd on carcd.EntityId = tea.ActivationId and carcd.AttrName = 'Reason Code' and carcd.TemplateType = 9 straight_join T_TMPL_ATTR_VALUE cakt on cakt.EntityId = tea.ActivationId and cakt.AttrName = 'Key Type' and cakt.TemplateType = 9 where te.TXNTYPE = 1 and teli.TXNTYPE = 1;" mydb | sed 's/\t/|/g' > my_activations_report_data.psv
The change works in my case and MySQL started to send data right away.
Comments
Post a Comment