List:Cluster« Previous MessageNext Message »
From:Raymond Peachey Date:October 10 2013 4:47pm
Subject:Re: Data retrieval causes query times to spike
View as plain text  
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