question Re: Any thoughts on why MySQL is utilizing over 100% CPU in Archives of Support Questions (Read Only)

Any thoughts on why MySQL is utilizing over 100% CPU

marc_fussell — Fri, 16 Sep 2022 10:11:13 GMT

This is new install for development purposes. The install completed fine, I can login to Ambari (HDP-2.3.4.0-3485) and the dashboard looks fine (all green). However, the server on which I have installed Ambari (and MySQL 5.6) is getting killed by the MySQL process.

I turned on SQL logging to see what was happening and it appears to simply be 100's (or 1000's) of SQL statements. (I did turn MySQL loggin back off as I know that has an impact for performance.)

Is this normal or am I missing something?

No one is using the system yet - meaning Hadoop (or any other server) is not being used by anyone yet. However, the MySQL process is just hammering the CPU - which seems odd to me.

I have let it run for over 24 hours (thinking it would slow down at some point, but after 24 hour of no one using the system the CPU is still pegged.

Re: Any thoughts on why MySQL is utilizing over 100% CPU

aparanjape — Tue, 29 Mar 2016 00:10:05 GMT

Hi @marksf, what are the system specs? CPU, RAM, etc. Not that this is the root cause of the problem but it can help as we start to suggest options. Was this MySQL service a fresh install with HDP or was it existing?

Re: Any thoughts on why MySQL is utilizing over 100% CPU

marc_fussell — Tue, 29 Mar 2016 01:16:33 GMT

Thanks for the reply. It is a two core CPU system with 8 GM RAM. 100% clean install running CentOS 6.7. I installed MySQL and HDP at the same time - just after the OS install.

Re: Any thoughts on why MySQL is utilizing over 100% CPU

nsabharwal — Tue, 29 Mar 2016 01:23:51 GMT

@marksf

Do this:

1) run top as root

2) locate top pid and then see what process those pid belongs to.

Once you are 100% sure that mysql causing the issue and those sql are not related to hadoop or ambari then follow this

Re: Any thoughts on why MySQL is utilizing over 100% CPU

marc_fussell — Tue, 29 Mar 2016 01:45:13 GMT

So, I am rather certain the problem somehow pertains to ambari or my setup/config.

To give you an example, within MySQL I turned on SQL logging for 30 seconds to catch a snapshot of what was happening within MySQL. In that 30 seconds, 35,650 queries were ran against the DB from Ambari.

Nearly all 35,650 queries look very similar to this:

Connect	ambari@hdptsrv1.test.rb.net on ambari

Query	/* mysql-connector-java-5.1.17-SNAPSHOT ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'

Query	/* mysql-connector-java-5.1.17-SNAPSHOT ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment

Query	SHOW COLLATION

Query	SET NAMES latin1

Query	SET character_set_results = NULL

Query	SET autocommit=1

Query	SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'

Query	SELECT @@session.tx_isolation

That is right at 1,200 queries per second that are being ran by Ambari.

Re: Any thoughts on why MySQL is utilizing over 100% CPU

marc_fussell — Tue, 29 Mar 2016 02:48:27 GMT

I have done some more reading and I have found out these queries are the result of the JDBC driver not caching the connection setting (which is the default). If I were to add "cacheServerConfiguration=true" to the connection string then these would go away. However, I am guessing that connection string is somewhere in the code for Ambari and there might be another fix for this.

Currently, asking the driver to connect with default configuration results in those statements being issued for every connection.

...I am still uncertain of the solution/fix, but I wanted to share what I have learned as I make progress.

Re: Any thoughts on why MySQL is utilizing over 100% CPU

marc_fussell — Tue, 29 Mar 2016 04:29:28 GMT

It appears this was a bug that was filed with MySQL pertaining to the JDBC driver version I was using. I was originally using mysql-connector-java-5.1.17.jar. I have since upgraded to mysql-connector-java-5.1.38-bin.jar and this issue has gone away.