List:Cluster« Previous MessageNext Message »
From:Hendrik Woltersdorf Date:November 23 2010 1:16pm
Subject:reason for slow query ?
View as plain text  
Hi,

I have a MySQL cluster version 7.0.13 running on 4 machines (2x mysqld as
API node, 2x data node with ndbmtd). One API node logged a slow query:

# Time: 101123 13:33:40
# User@Host: pitase[pitase] @  [62.192.204.14]
# Query_time: 3.446779  Lock_time: 0.000057 Rows_sent: 0  Rows_examined: 0
SET timestamp=1290515620;
SELECT  *  FROM tan_list WHERE (SEC_TYPE='Card' AND TAN_LIST_NUMBER='1' AND
USER_ID='1023029' AND CLIENT_ID='1' );

When I execute this query manually it is _very_ fast and "explain extended"
tells me, that it uses the primary key of the table:

CREATE TABLE `tan_list` (
  `client_id` varchar(10) NOT NULL,
  `sec_type` varchar(10) NOT NULL,
  `user_id` varchar(30) NOT NULL,
  `state` int(11) NOT NULL,
  `tan_list_number` varchar(8) NOT NULL,
  `tan_data` blob,
  `tan_count` int(11) DEFAULT NULL,
  `open_tan_count` int(11) DEFAULT NULL,
  `error_count` int(11) DEFAULT NULL,
  `letter_request_time` datetime DEFAULT NULL,
  `activation_time` datetime DEFAULT NULL,
  `letter_recipient` varchar(1024) DEFAULT NULL,
  `letter_request_type` varchar(50) DEFAULT NULL,
  `time_insert` datetime DEFAULT NULL,
  `letter_sent_time` datetime DEFAULT NULL,
  `card_number` varchar(16) DEFAULT NULL,
  `ref_id` varchar(36) DEFAULT NULL,
  `ref_id_next` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`client_id`,`sec_type`,`user_id`,`tan_list_number`),
  KEY `tan_list` (`user_id`,`client_id`,`sec_type`,`tan_list_number`),
  KEY `tan_list_idx1` (`card_number`),
  KEY `tan_list_idx2` (`state`),
  KEY `tan_list_idx3` (`open_tan_count`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

This table contains > 194000 rows. There were no other slow queries logged
at this time on the two API nodes.

The API nodes use ndb-cluster-connection-pool=4.

Does anyone have an idea, what causes this long query time ?

kind regards

Hendrik Woltersdorf
XCOM AG
Email: Hendrik.Woltersdorf@xcom.de


*** XCOM AG Legal Disclaimer ***

Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist allein
für den Gebrauch durch den vorgesehenen Empfaenger bestimmt. Dritten ist
das Lesen, Verteilen oder Weiterleiten dieser E-Mail untersagt. Wir bitten,
eine fehlgeleitete E-Mail unverzueglich vollstaendig zu loeschen und uns
eine Nachricht zukommen zu lassen.

This email may contain material that is confidential and for the sole use
of the intended recipient. Any review, distribution by others or forwarding
without express permission is strictly prohibited. If you are not the
intended recipient, please contact the sender and delete all copies.

Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900,
www.xcom.de
Handelsregister: Amtsgericht Krefeld, HRB 10340
Vorstand: Matthias Albrecht, Dirk Franzmeyer, Dr. Rainer Fuchs, Marco
Marty, Dirk Werner
Vorsitzender des Aufsichtsrates: Stefan H. Tarach
Thread
reason for slow query ?Hendrik Woltersdorf23 Nov
  • Re: reason for slow query ?Johan Andersson25 Nov
    • Antwort: Re: reason for slow query ?Hendrik Woltersdorf25 Nov
      • RE: Antwort: Re: reason for slow query ?Andrew Armstrong25 Nov
        • Antwort: RE: Antwort: Re: reason for slow query ?Hendrik Woltersdorf26 Nov