List:General Discussion« Previous MessageNext Message »
From:Jatin Davey Date:June 3 2014 11:51am
Subject:Re: SHOW FULL COLUMNS QUERIES hogging my CPU
View as plain text  
On 6/2/2014 9:10 PM, Morgan Tocker wrote:
> Hi Jatin,
>
> On Jun 2, 2014, at 7:56 AM, Jatin Davey <jashokda@stripped> wrote:
>
>> I found this blog while trying to find a fix for my problem. Thought it might be
> useful to share.
>>
>> Here it is:
>>
>> http://whirlpool.net.au/blog/5
> To confirm - did you manage to fix your problem?
>
> I was about to comment that it looks like queries generated by an ORM or connector. 
> It looks like from your version string you have an MySQL enterprise, may I suggest
> creating a ticket with support?
>
> Regarding your most recent reply:
>
>> All the "SHOW FULL COLUMN" queries that we do on the respective tables are very
> small tables. They hardly cross 50 rows. Hence that is the reason whenever these queries
> are made i can see high cpu usage in %user_time. If it were very large tables then the cpu
> would be spending lot of time in waiting for I/O as the databases reads would have to do a
> lot of I/O to read the data from the disks.
> If it helps - I believe performance should be similar with large tables, since in the
> case of big table or small table, what is accessed here should just be meta data.
>
> Earlier versions of MySQL could rebuild InnoDB statistics on some SHOW commands
> (I’m not sure about SHOW FULL COLUMN), but this is now disabled by default:
>
> http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
>
> - Morgan
Hi Morgan

I think you may be correct. It could be an issue with the connector. I 
saw the code of the mysql Connector/J and found the getColumns() method 
building a stringBuffer object which evetually runs this "SHOW FULL 
COLUMNS" queries. I have to look at my code where ever this method is 
called and make sure we dont do it. But i have not nailed this down. It 
is still just an assumption that i have.

Another point is that i have the innodb_stats_on_metadata turned OFF

Thanks for the response.

Thanks
Jatin
Thread
SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
  • Re: SHOW FULL COLUMNS QUERIES hogging my CPUReindl Harald2 Jun 2014
    • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
      • Re: SHOW FULL COLUMNS QUERIES hogging my CPUMichael Dykman2 Jun 2014
        • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
      • Re: SHOW FULL COLUMNS QUERIES hogging my CPUSinger Wang2 Jun 2014
        • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
          • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
            • Re: SHOW FULL COLUMNS QUERIES hogging my CPUMorgan Tocker2 Jun 2014
              • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey3 Jun 2014
          • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJohan De Meersman2 Jun 2014
            • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJohan De Meersman3 Jun 2014
              • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey3 Jun 2014