select statment hangs on sending data for big table

Description

In some environments with large amounts of data we seen hanging statement on "sending data" state.
For example for table

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index_comment

piwik_log_link_visit_action

0

PRIMARY

1

idlink_va

A

2

NULL

NULL

BTREE

piwik_log_link_visit_action

1

index_idvisit

1

idvisit

A

NULL

NULL

NULL

YES

BTREE

piwik_log_link_visit_action

1

index_idsite_servertime

1

idsite

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

index_idsite_servertime

2

server_time

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url

1

idaction_url

A

NULL

NULL

NULL

YES

BTREE

piwik_log_link_visit_action

1

transitions_url

2

idsite

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url

3

server_time

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url_ref

1

idaction_url_ref

A

NULL

NULL

NULL

YES

BTREE

piwik_log_link_visit_action

1

transitions_url_ref

2

idsite

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url_ref

3

server_time

A

NULL

NULL

NULL

BTREE

query below performed manually returns the result immediately, but from app hanging for 20 secons on sending data state

on larger databases (3 billions records and more) the same simple query hanging already for several minutes

The same situation occurred on different hardware , system (debian,redhat) and database (mariadb,percona) configurations.

Environment

None

Activity

Show:
Former user
April 15, 2016, 8:08 AM

Thank you for reply. I talked with our developers, they will try to get rid of DESC and LIMIT to see if your assumptions regarding are right. I'll inform you as soon as we rewrite this query and do some testing.

Former user
April 15, 2016, 11:24 AM

Hi again,

I confirm that after changing query to not use desc and limit application seems to work, and there is no query that hangs in "sending data" state.

George Lorch
April 15, 2016, 2:54 PM

Thank you for the feedback Michal. This issue is on our 'short list' to fix as soon as possible but I can not provide you with a time frame at this point other than we are targeting 2nd quarter 2016. I will keep this issue open and link them. Please post back if the behavior changes or disproves this theory.

George Lorch
April 15, 2016, 11:27 PM

Also see DB-534, another possible dupliate

Jacek Lubzinski
April 22, 2016, 1:12 PM

Thank you for your help and guidance!
We were able to eliminate the DESC and LIMIT from query as you suggest. Currently we are tested this at several larger databases and everything works, but we still have to check and confirm it on a single database where the problem was most noticeable. After that we will be able to share this quick fix with the piwik community.

Assignee

George Lorch

Reporter

Jacek Lubzinski

Labels

External issue ID

None

Freshdesk Tickets

None

Priority

Major
Configure