List:Cluster« Previous MessageNext Message »
From:Raymond Peachey Date:October 14 2013 3:08pm
Subject:Re: Data retrieval causes query times to spike
View as plain text  
No, there was no ALTER TABLE done recently. The job that runs, however,
does create tables, and does indeed do a INSERT .. SELECT from the change
tracking table and the master tables to the result tables. However, NDB is
READ COMMITTED/ROW Based logging, so all reads should be consistent reads,
non locking (correct me if I'm wrong, perhaps it's different with NDB?).

We are currently running version: 5.5.29-ndb-7.2.10.

There are a lot of queries that query against INFORMATION_SCHEMA to
determine primary keys etc for the change tracking tables. The main culprit
here probably is the query that joins the trigger tables to the master
tables per job run:

REPLACE result_table
SELECT <column_list>
FROM db.master_table AS P LEFT OUTER JOIN db.trigger_table AS CT ON
p.pk_col = CT.pk_col
WHERE timestamp_column > 'datetime';

The number of records varies, but we have these jobs running every 5
minutes, and so it is typically only ~500 records per table, so in this
case, a total of about ~5000 per job run, and there is a little parallelism
going on in that there's actually two jobs running simultaneously (against
two separate databases), that make up that 5000 records processed.

I could try removing the frm files and see if that helps. Additionally, I
will look into increasing the execution threads today to see if that helps.


On Thu, Oct 10, 2013 at 1:32 PM, Johan Andersson <johan@stripped>wrote:

> Hi,
>
> Did you recently do an alter table on TABLE_XYZ and is it the same table
> you run the query on?
>
> Not sure what that error message means "NDB online alter table commit:
> distributing TABLE_XYZ timed out. Ignoring..."
> But usually when i have problems like this, do stop the mysql server,
> remove all the .frm files belonging to NDB tables, and start the server
> again.
> This way the server gets new fresh table meta data from the dictionary in
> the data nodes.
>
> Run the query again, does it work?
>
> What does the query look like? Is the query trying to acquire locks?
>
> If you are not locking any records then the 233 error is super strange
> (have you upgraded to the latest 7.2.12 ?).
> How many records are you trying to read?
>
> Best regards
> johan
>
>
>
>
> On Thu, Oct 10, 2013 at 6:47 PM, Raymond Peachey <raypeachey@stripped>wrote:
>
>> In the error log, I see this:
>>
>> 131009 20:02:53 [ERROR] NDB online alter table commit: distributing
>> TABLE_XYZ timed out. Ignoring...
>> 131009 20:03:52 [ERROR] Got error 233 when reading table
>>
>> I see the first error a lot -- the second error I see specifically during
>> the trouble periods. 233 I think has to do with reaching a max limit of
>> concurrent operations.
>>
>> We do not have TransactionDeadlockDetectionTimeout defined, which means
>> it should be at it's default of 1200 ms.
>>
>> SHOW GLOBAL VARIABLES LIKE '%timeout%' ;
>> # Variable_name, Value
>> connect_timeout, 10
>> delayed_insert_timeout, 300
>> innodb_lock_wait_timeout, 50
>> innodb_rollback_on_timeout, OFF
>> interactive_timeout, 28800
>> lock_wait_timeout, 31536000
>> net_read_timeout, 30
>> net_write_timeout, 60
>> slave_net_timeout, 3600
>> wait_timeout, 28800
>>
>> Additionally, here's other settings we have configured, that I think may
>> help:
>>
>> MaxNoOfConcurrentOperations=1000000
>> MaxNoOfConcurrentTransactions=16384
>>
>>
>>
>> On Thu, Oct 10, 2013 at 11:38 AM, Johan Andersson <johan@stripped
>> > wrote:
>>
>>> Hi,
>>>
>>> What is the exact error code you get from the mysql server and do you
>>> have anything in the error log of the mysql server?
>>>
>>> What is "TransactionDeadlockDetectionTimeout" set to in config.ini
>>> What does
>>>   SHOW GLOBAL VARIABLES LIKE '%timeout%'
>>> from the mysql server in question print out?
>>>
>>>
>>> Best regards,
>>> johan
>>>
>>> Severalnines AB
>>>
>>>
>>> On Thu, Oct 10, 2013 at 4:31 PM, Raymond Peachey
> <raypeachey@stripped>wrote:
>>>
>>>> Hello Hendrik,
>>>>
>>>> The setting is currently configured to 30 on each MYSQLD node, with a
>>>> total
>>>> of 60 API connections in the config.ini (other than the 4 data node
>>>> connections of course). I feel that the execution threads is the only
>>>> possible culprit here. Unless advised otherwise, I will be rolling back
>>>> my
>>>> previous change for the send/receive buffers and will be increasing
>>>> execution threads.
>>>>
>>>> Also, for reference, the two MYSQLD nodes are 32 core (w/HT) boxes.
>>>>
>>>>
>>>> On Thu, Oct 10, 2013 at 1:07 AM, Hendrik Woltersdorf <
>>>> hendrik.woltersdorf@stripped> wrote:
>>>>
>>>> > Hi,
>>>> >
>>>> > whats your setting of "ndb-cluster-connection-pool" (in my.cnf) ?
>>>> > With only 2 api nodes increasing MaxNoOfExecutionThreads makes only
>>>> sense
>>>> > (to me), if you have more than one connection between api nodes and
>>>> data
>>>> > nodes.
>>>> > (You need to add additional api slots in config.ini for the
> additional
>>>> > connections).
>>>> >
>>>> > regards
>>>> > Hendrik Woltersdorf
>>>> >
>>>> > XCOM AG
>>>> > Softwareentwicklung
>>>> > Banksystem Front- und Middleoffice
>>>> > Peter-Breuer-Str. 13
>>>> > 08056 Zwickau
>>>> > Germany
>>>> >
>>>> > Telefon +49 375 27008-580
>>>> > Telefax +49 375 27008-25
>>>> > Mobil   +49 152 22999 584
>>>> > hendrik.woltersdorf@stripped
>>>> > http://www.xcom.de
>>>> >
>>>> > schrieb Raymond Peachey:
>>>> >
>>>> >> Increased it to 64MB send/receive buffers to no avail. I'm
> curious if
>>>> >> maybe
>>>> >> upping the Max Execution threads could help? We have it set to 8
> at
>>>> the
>>>> >> moment. Any insight on how to gauge thread usage to see the
> possible
>>>> >> effects of increasing this setting?
>>>> >>
>>>> >>
>>>> >> On Wed, Oct 9, 2013 at 11:05 AM, Wagner Bianchi
>>>> >> <wagnerbianchijr@stripped>**wrote:
>>>>
>>>> >>
>>>> >>  Could you give it a try and let us know what you've got from
> the new
>>>> >>> execution? Cheers!
>>>> >>>
>>>> >>>
>>>> >>> --
>>>> >>> *Wagner Bianchi, +55.31.8654.9510*
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>> 2013/10/9 Raymond Peachey <raypeachey@stripped>
>>>> >>>
>>>> >>>  The query typically takes 1.3 seconds. When my data
> retrieval job
>>>> runs
>>>> >>>> (separate process), the query will start to exceed 5
> seconds,
>>>> hitting
>>>> >>>> the timeout.
>>>> >>>>
>>>> >>>> I'd like to understand if there is anything I can tune
> that will
>>>> >>>> improve this situation. I mentioned the Buffer Memory
> which I think
>>>> >>>> might help with this, but I'm not sure.
>>>> >>>>
>>>> >>>> Sent from my iPhone
>>>> >>>>
>>>> >>>>
>>>> >
>>>> > --
>>>> > MySQL Cluster Mailing List
>>>> > For list archives: http://lists.mysql.com/cluster
>>>> > To unsubscribe:    http://lists.mysql.com/cluster
>>>> >
>>>> >
>>>>
>>>
>>>
>>
>

Thread
Data retrieval causes query times to spikeRaymond Peachey7 Oct
  • Re: Data retrieval causes query times to spikeMagnus BlĂ„udd9 Oct
    • Re: Data retrieval causes query times to spikeRaymond Peachey9 Oct
      • Re: Data retrieval causes query times to spikeWagner Bianchi9 Oct
        • Re: Data retrieval causes query times to spikeRaymond Peachey10 Oct
          • Re: Data retrieval causes query times to spikeHendrik Woltersdorf10 Oct
            • Re: Data retrieval causes query times to spikeRaymond Peachey10 Oct
              • Re: Data retrieval causes query times to spikeJohan Andersson10 Oct
                • Re: Data retrieval causes query times to spikeRaymond Peachey10 Oct
                  • Re: Data retrieval causes query times to spikeJohan Andersson10 Oct
                    • Re: Data retrieval causes query times to spikeRaymond Peachey14 Oct
                      • Re: Data retrieval causes query times to spikeHendrik Woltersdorf15 Oct
                        • Re: Data retrieval causes query times to spikeRaymond Peachey15 Oct
                          • Re: Data retrieval causes query times to spikeJohan Andersson15 Oct
                            • Re: Data retrieval causes query times to spikeRaymond Peachey16 Oct
                              • Re: Data retrieval causes query times to spikeRaymond Peachey21 Oct