SHOW FULL COLUMNS QUERIES hogging my CPU
Hi All I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system. As a result it is killing my system and i am unable to scale for more load. I looked if there is any code in my app that is doing these queries and found nothing. Could some one let me know how i can get rid of these queries being sent to the DB. I am using mysql server version: 5.6.10-enterprise-commercial-advanced Please let me know if any more information is required. Appreciate your help in this regard. Thanks Jatin
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
Am 02.06.2014 15:35, schrieb Jatin Davey: I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system fix your application - there is no single reason to run such queries 10 million times because the result won't change all the time and avoid like in general signature.asc Description: OpenPGP digital signature
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
On 6/2/2014 7:18 PM, Reindl Harald wrote: Am 02.06.2014 15:35, schrieb Jatin Davey: I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system fix your application - there is no single reason to run such queries 10 million times because the result won't change all the time and avoid like in general Our application does not send such queries to the DB. I have searched through my entire code and we dont run such queries. It has something to do with a layer below our application. But i am not sure as to where it is. Thanks Jatin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
The advice to 'avoid LIKE in general' is a little strong. LIKE is very useful and does not always cause inefficient queries, although the possibility is there. However, there is one form which must be avoided at all costs: the one where the glob-text matcher is the first character in that string. LIKE '%' or LIKE '%foobar' are both equally costly as, no matter what index there might be on that column, the query will have to visit every single row to test the match, therefore inducing a full table scan. putting it early in the expression is equally dangerous, but how dangerous depends on how much data you have: LIKE 'a%' avoids visiting every row but it still has to test against a significant subset of all rows: If you have 100 million rows, this will still cause your query to visit a very large number of them. So, I would have to ask: how many records are in that table? How many columns? is it a table or a view? On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey jasho...@cisco.com wrote: On 6/2/2014 7:18 PM, Reindl Harald wrote: Am 02.06.2014 15:35, schrieb Jatin Davey: I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system fix your application - there is no single reason to run such queries 10 million times because the result won't change all the time and avoid like in general Our application does not send such queries to the DB. I have searched through my entire code and we dont run such queries. It has something to do with a layer below our application. But i am not sure as to where it is. Thanks Jatin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
What's your technology stack? On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey jasho...@cisco.com wrote: On 6/2/2014 7:18 PM, Reindl Harald wrote: Am 02.06.2014 15:35, schrieb Jatin Davey: I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system fix your application - there is no single reason to run such queries 10 million times because the result won't change all the time and avoid like in general Our application does not send such queries to the DB. I have searched through my entire code and we dont run such queries. It has something to do with a layer below our application. But i am not sure as to where it is. Thanks Jatin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
Certain part of our code uses DataNucleas while other parts of the code use JDBC to access the DB. I would say that 70% of our code uses DataNucleas while the remaining 30% of our code uses JDBC for databases related operations. Thanks Jatin On 6/2/2014 8:17 PM, Singer Wang wrote: What's your technology stack? On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey jasho...@cisco.com mailto:jasho...@cisco.com wrote: On 6/2/2014 7:18 PM, Reindl Harald wrote: Am 02.06.2014 15:35, schrieb Jatin Davey: I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system fix your application - there is no single reason to run such queries 10 million times because the result won't change all the time and avoid like in general Our application does not send such queries to the DB. I have searched through my entire code and we dont run such queries. It has something to do with a layer below our application. But i am not sure as to where it is. Thanks Jatin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
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. Thanks Jatin On 6/2/2014 8:13 PM, Michael Dykman wrote: The advice to 'avoid LIKE in general' is a little strong. LIKE is very useful and does not always cause inefficient queries, although the possibility is there. However, there is one form which must be avoided at all costs: the one where the glob-text matcher is the first character in that string. LIKE '%' or LIKE '%foobar' are both equally costly as, no matter what index there might be on that column, the query will have to visit every single row to test the match, therefore inducing a full table scan. putting it early in the expression is equally dangerous, but how dangerous depends on how much data you have: LIKE 'a%' avoids visiting every row but it still has to test against a significant subset of all rows: If you have 100 million rows, this will still cause your query to visit a very large number of them. So, I would have to ask: how many records are in that table? How many columns? is it a table or a view? On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey jasho...@cisco.com wrote: On 6/2/2014 7:18 PM, Reindl Harald wrote: Am 02.06.2014 15:35, schrieb Jatin Davey: I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system fix your application - there is no single reason to run such queries 10 million times because the result won't change all the time and avoid like in general Our application does not send such queries to the DB. I have searched through my entire code and we dont run such queries. It has something to do with a layer below our application. But i am not sure as to where it is. Thanks Jatin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
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 Thanks Jatin On 6/2/2014 8:22 PM, Jatin Davey wrote: Certain part of our code uses DataNucleas while other parts of the code use JDBC to access the DB. I would say that 70% of our code uses DataNucleas while the remaining 30% of our code uses JDBC for databases related operations. Thanks Jatin On 6/2/2014 8:17 PM, Singer Wang wrote: What's your technology stack? On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey jasho...@cisco.com mailto:jasho...@cisco.com wrote: On 6/2/2014 7:18 PM, Reindl Harald wrote: Am 02.06.2014 15:35, schrieb Jatin Davey: I am no expert with mysql and databases. Hence seeking out some help on this forum. Basically i got a query dump of my application during its operation. I had collected the queries for about 4 hours. Ran some scripts on the number of queries being sent to the databases. The query file was a whopping 4 GB is size. Upon analyzing the queries i found that there were a total of 30 million queries made to the Database out of which 10 million queries were only doing SHOW FULL COLUMN queries. The SHOW FULL COLUMN queries were of the format as below: SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM `db_private_admin` LIKE '%' This is causing considerable cpu usage in %user_time in my system fix your application - there is no single reason to run such queries 10 million times because the result won't change all the time and avoid like in general Our application does not send such queries to the DB. I have searched through my entire code and we dont run such queries. It has something to do with a layer below our application. But i am not sure as to where it is. Thanks Jatin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
Hi Jatin, On Jun 2, 2014, at 7:56 AM, Jatin Davey jasho...@cisco.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
- Original Message - From: Jatin Davey jasho...@cisco.com Subject: Re: SHOW FULL COLUMNS QUERIES hogging my CPU Certain part of our code uses DataNucleas while other parts of the code A data persistence product... there's your problem. Persisting objects into a relational database is like fitting a square peg in a round hole: it works if you have a big enough hammer, but something is going to break. I'm not going to go into the myriad ways such layers add inefficiencies that only get worse with scale; but I suggest you log a ticket with your vendor; they're bound to have encountered the problem before. There's probably some setting that caches the db metadata instead of requesting it over and over again. In any case, this is nothing that can be fixed on the database level. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
