KBEC-00356 - MySQL Configuration Recommendations

MySQL was formerly packaged with ElectricCommander 4.1.x and earlier. Although MySQL is no longer directly packaged with the ElectricFlow installer, it is still one of the supported databases as listed in the ElectricFlow Installation Guide at http://docs.electric-cloud.com/eflow_doc/FlowIndex.html.

Following are some recommended settings for MySQL usage with ElectricCommander 5.x and ElectricFlow 6.x and higher with MySQL 5.5.12, 5.6, 5.7 or higher.

 

Setting the Parameters in the MySQL Configuration File

In the [mysqld] section in the my.cnf file, set the following parameters. The my.cnf file is in the directory named basedir.

The values for size are minimum recommendations. You might need to increase these values depending on your usage. Consult your database administrator or Oracle support before adjusting these values.

The paths for the basedir, datadir, and other parameters in the file will differ for your installation.

...
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking is for MyISAM. Commander needs InnoDB so commenting this
#skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
#
# * Fine Tuning
#key_buffer is for MyISAM. Commander needs InnoDB so commenting this
#key_buffer = 16M
#The default of 192KB (256KB for 64-bit systems) is large enough for normal operation.
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam-recover = BACKUP
#
# * Query Cache Configuration
#
#query_cache_limit is removed in MySQL 8.0.3 as per https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html
#query_cache_limit = 1M
#query_cache_size is removed in MySQL 8.0.3 as per https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html
#query_cache_size = 32M

#default-character-set and default-collation is deprecated in Mysql 5.5.43+. Was used in MySQL <5.5.3 
#default-character-set=utf8
#default-collation=utf8_unicode_ci
#init_connect commented as per NMB-26591 and init_connect value is not executed for users who have the SUPER privilege
#init_connect='SET collation_connection = utf8_unicode_ci, NAMES utf8'
#added as per NMB-26591 to set character settings to utf-8 with character-set-server and collation-server at server startup for MySQL >=5.5.3
character-set-server=utf8
collation-server=utf8_unicode_ci

 


#skip-character-set-client-handshake commented as per NMB-26591
#skip-character-set-client-handshake
# Storage engine by default is MyISAM which does not work well for commander
default_storage_engine=InnoDB
# Increase innodb_lock_wait_timeout to 300 seconds as per NMB-13898, if you see a lot of org.hibernate.exception.LockTimeoutException in the commander logs as default 50 sec is too low.
innodb_lock_wait_timeout=300
transaction_isolation=REPEATABLE-READ
#If using Mysql 5.7 table_cache should be renamed to table_open_cache . See https://dba.stackexchange.com/questions/104025/mysql-unknown-variable-table-cache-64
table_open_cache=1024
sort_buffer_size=6M
tmp_table_size=64M
max_heap_table_size=32M
read_rnd_buffer_size=256K
#The innodb_additional_mem_pool_size is removed in Mysql 5.7.20+
#innodb_additional_mem_pool_size=2M
# On 64-bit MySQL, you can now bump innodb_buffer_pool_size to over 1500 MB if you have enough memory available.
# In zd-177165 one customer had this value as innodb_buffer_pool_size=8G
innodb_buffer_pool_size=512M
innodb_file_per_table
# We usually bump the connect_timeout way up, on the assumption that Commander is the only user of the DB
connect_timeout=14400
#For large commander property operations to work
max_allowed_packet=1024M
#Commander needs at least 200 connections.
max_connections=210
...

Note: The query_cache_limit and query_cache_size system variables are deprecated as of MySQL version 8.0.3.

Restarting the MySQL Server

After adjusting any parameters in the my.cnf file, you must restart the MySQL server as the user running the MySQL service.

Locating the MySQL Configuration File

The MySQL configuration file is named my.cnf on Linux and my.ini on Windows.There are two methods to find this file on your system.

Method 1

As per http://serverfault.com/questions/346647/mysql-wheres-the-my-cnf-path, enter:

[oracle@ora-db-test2 ~]$ strace -fe open /etc/init.d/mysql start 2>&1|grep my.cnf

This displays the system call that is used to open the file. For example:

[pid 25317] open("/usr/my.cnf", O_RDONLY) = 3
[pid 25347] open("/usr/my.cnf", O_RDONLY) = 3
[pid 25576] open("/usr/my.cnf", O_RDONLY) = 3

This example shows that MySQL uses /usr/my.cnf.

Note: The above strace command will not start MySQL if you lack permissions. But if you run it as follows:

[oracle@ora-db-test2 ~]$ sudo strace -fe open /etc/init.d/mysql start 2>&1|grep my.cnf

It will start MySQL, output the following lines, and wait until MySQL is stopped:

[pid 27220] open("/usr/my.cnf", O_RDONLY) = 3
[pid 27250] open("/usr/my.cnf", O_RDONLY) = 3
[pid 27479] open("/usr/my.cnf", O_RDONLY) = 3
[pid 27548] open("/usr/my.cnf", O_RDONLY) = 3

Then you can stop MySQL by entering:

sudo /etc/init.d/mysql stop

If you have an /etc/my.cnf file, then it will override the settings in /usr/my.cnf as shown below:

[oracle@ora-db-test2 ~]$ strace -fe open /etc/init.d/mysql start 2>&1|grep my.cnf
[pid 26290] open("/etc/my.cnf", O_RDONLY) = 3
[pid 26290] open("/usr/my.cnf", O_RDONLY) = 3
[pid 26326] open("/etc/my.cnf", O_RDONLY) = 3
[pid 26326] open("/usr/my.cnf", O_RDONLY) = 3
[pid 26785] open("/etc/my.cnf", O_RDONLY) = 3
[pid 26785] open("/usr/my.cnf", O_RDONLY) = 3

For more information, see the my.cnf settings precedence order as described in the “Order of Precedence for Using the my.cnf File” section below.

Method 2

If MySQL is running, enter:

ps -ef | grep -i mysql.

Output similar to the following appears:

[oracle@ora-db-test2 mysql]$ ps -ef | grep -i mysql
root 3505 1 0 Sep05 ? 00:00:00 sudo /usr/bin/mysqld_safe --user=mysql
root 3506 3505 0 Sep05 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --user=mysql
mysql 3693 3506 0 Sep05 ? 01:16:42 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/ora-db-test2.electric-cloud.com.err --pid-file=/var/lib/mysql/ora-db-test2.electric-cloud.com.pid --socket=/var/lib/mysql/mysql.sock

So on the ora-db-test2 machine, (which has MySQL installed), you can see that the MySQL basedir = /usr. Otherwise, from the above output, you can see the location of the mysqld file (in this example, it is in /usr/sbin/mysqld).

So you can enter the following command to help find the file as described below:

/usr/sbin/mysqld --verbose --help | grep my.cnf > 1.txt

In the 1.txt, file that you just created, lines such as the following appear, which indicate that the default options are read from the following files and also indicate the order in which they are read:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

For example:

2015.11.02 16:46:3931632 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root! 
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf /export/electriccloud/mysql/my.cnf ~/.my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf /export/electriccloud/mysql/my.cnf ~/.my.cnf 
                      my.cnf, @MYSQL_TCP_PORT, /etc/services, built-in default
2015.11.02 16:48:39 31632 [ERROR] Aborting

Then you can find the my.cnf file by entering:

root@ip-10-233-85-247:~# ls -al /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf  
ls: cannot access /etc/my.cnf: No such file or directory 
ls: cannot access /usr/etc/my.cnf: No such file or directory 
ls: cannot access /root/.my.cnf: No such file or directory 
-rw-r--r-- 1 root root 3571 Sep 1 2016 /etc/mysql/my.cnf 

Order of Precedence for Using the my.cnf File

As per http://dev.mysql.com/doc/refman/5.1/en/option-files.html:

On Unix, Linux, and OS X, MySQL programs read startup options from the following files in the specified order (top files are read first, and later files take precedence).

File Name

Purpose

/etc/my.cnf

Global options

/etc/mysql/my.cnf

Global options (as of MySQL 5.1.15)

/my.cnf

Global options

$MYSQL_HOME/my.cnf

Server-specific options

defaults-extra-file

The file specified with --defaults-extra-file=file_name, if any

~/.my.cnf

User-specific options

In the table above, ~ represents the current user’s home directory (the value of $HOME).

represents the directory specified with the --sysconfdir option to configure when MySQL was built. By default, this is the /etc directory located under the compiled-in installation directory. This location is used as of MySQL 5.1.10. (From 5.1.10 to 5.1.22, it was read last, after ~/.my.cnf).

$MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If $MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set $MYSQL_HOME as follows:

  • Let BASEDIR and DATADIR represent the path names of the MySQL base directory and data directory, respectively.
  • If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.
  • Otherwise, if $MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.
  • On Windows, MySQL programs read startup options from the following files in the specified order (top files are read first, later files take precedence).
  • This probably indicates that my.cnf is in BASEDIR (meaning the /usr folder).

File Name

Purpose

%PROGRAMDATA%\MySQL\MySQL Server 5.1\my.ini,%PROGRAMDATA%\MySQL\MySQL Server 5.1\my.cnf

Global options

%WINDIR%\my.ini, %WINDIR%\my.cnf

Global options

C:\my.ini, C:\my.cnf

Global options

INSTALLDIR\my.ini, INSTALLDIR\my.cnf

Global options

defaults-extra-file

The file specified with --defaults-extra-file=file_name, if any

In the table above, %PROGRAMDATA% represents the file system directory that contains application data for all users on the host. This path defaults to C:\ProgramData on Microsoft Windows Vista and greater and C:\Documents and Settings\All Users\Application Data on older versions of Microsoft Windows.

%WINDIR% represents the location of your Windows directory. This is commonly C:\WINDOWS. You can determine its exact location from the value of the %WINDIR% environment variable by using the following command:

C:\> echo %WINDIR%

INSTALLDIR represents the MySQL installation directory. This is typically C:\<PROGRAMDIR>\MySQL\MySQL 5.1 Server where <PROGRAMDIR> represents the programs directory (usually Program Files on English-language versions of Windows) when MySQL 5.1 was installed using the installation and configuration wizards.

In addition to the my.cnf file, if you need the MySQL show variables output, enter:

mysql --host=ora-db-test2 --port=3306 --user=root --password=password --execute "show variables; select '-----' ;"> /tmp/show_variables.txt

Note: You must use the correct --host= --port= --user= --password= values.

Then you can examine the /tmp/show_variables.txt file that you just created for the output.

References

http://dba.stackexchange.com/questions/6054/where-does-my-cnf-get-stored-in-linux

http://stackoverflow.com/questions/2482234/how-to-know-mysql-my-cnf-location

http://dev.mysql.com/doc/refman/5.5/en/option-files.html

 

Troubleshooting Your MySQL Configuration

  1. Determine all of your MySQL current parameters by entering:

    mysql --host=localhost --port=3306 --user=root --password=commander --execute "SHOW variables;" > /tmp/show_ variables.txt
  2. Verify that the MySQL default character-set is set to UTF8 as mentioned in How_to_change_Mysql_character-set_to_UTF8.docx and change it as necessary.
  3. Resolving org.hibernate.exception.LockTimeoutException during transaction retry (i.e " | TransactionRetryAspectImpl | Retry") in the commander logs (based on NMB-13898, https://confluence.atlassian.com/display/BAMKB/MySQL+-+Lock+wait+timeout+exceeded+-+try+restarting+transaction):

    Determine all the MySQL timeout parameters currently being used by using the following MySQL command:
    show variables like '%timeout%';
    An output similar to the following appears:
    connect_timeout 10
    delayed_insert_timeout 300
    innodb_flush_log_at_timeout 1
    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
    rpl_stop_slave_timeout 31536000
    slave_net_timeout 3600
    wait_timeout 28800

    Then identify which timeout is low, for example: innodb_lock_wait_timeout. Check if increasing innodb_lock_wait_timeout makes the timeout last longer to avoid org.hibernate.exception.LockTimeoutException errors in the ElectricFlow log files. The default value is 50 seconds, and given the size of these transactions, you should use a much higher value, perhaps 300 seconds or more.
  4. In NMB-22687, we found that in an 80 GB MySQL database, the index creation during an ElectricFlow upgrade was taking longer than 18 minutes, so the upgrade fails because the database connection is lost. Below is the ElectricFlow log snippet:

    2017-04-20T11:38:16.622 | WARN | bootstrap | | | schemaMaintenance | UpgradeDriverImpl | Create foreign key indexes failed: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet
    ..
    org.hibernate.exception.JDBCConnectionException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
    at org.hibernate.loader.Loader.doList(Loader.java:2554)
    at org.hibernate.loader.Loader.doList(Loader.java:2540)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
    at org.hibernate.loader.Loader.list(Loader.java:2365)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
    at com.electriccloud.upgrade.UpgradeDriverImpl.createForeignKeyIndexes(UpgradeDriverImpl.java:143)
    at com.electriccloud.upgrade.UpgradeDriverImpl.upgradeSchema(UpgradeDriverImpl.java:424)
    at com.electriccloud.upgrade.UpgradeManagerImpl.doSchemaMaintenance(UpgradeManagerImpl.java:980)

Google Search for "How to avoid com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException Communications link failure during commit()" and found lots of links like the following:

a. MySQL would kill connection when open transaction is idle for too long.

As per http://forums.pentaho.com/showthread.php?140518-MySql-Communications-link-failure-SOLVED and http://wiki.pentaho.com/display/EAI/MySQL:
"net_write_timeout" defaults to 60 seconds. Increasing to a large value like 1800 helped to resolve com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException Communications link failure during commit().
Another reported solution was: Set the tcpKeepAlive setting in the MySQL connector (since MySQL Connector/J 5.0.7, please see about JDBC driver versions).

b. The link
https://serverfault.com/questions/89955/unable-to-connect-to-mysql-through-jdbc-connector-through-tomcat-or-externally discusses more timeout settings to check MySQL parameters such as:
wait_timeout = number
interactive_timeout = number
connect_timeout = number

c. Make sure you're not doing something really silly like starting MySQL with --skip-networking (https://dev.mysql.com/doc/refman/5.7/en/gone-away.html)

d. As per https://jira.terracotta.org/jira/browse/QTZ-478, check if there is a disk space issue on the MySQL server.

=======================

5. Check the MySQL transaction isolation (https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html ) by using:

SHOW VARIABLES like "%isolation%";.

The recommended output is:

'tx_isolation', 'REPEATABLE-READ'

Check if the isolation level is 'REPEATABLE-READ'. It has to be REPEATABLE-READ and not READ-COMMITTED, READ-UNCOMMITTED, and so on. This was an issue in NMB-21602.

Note: The tx_isolation system variable is deprecated as of MySQL version 8.0.3.

6. Make sure that you are using a MySQL version that Electric Cloud supports (and not using Percona, etc.) by entering:

SHOW VARIABLES like "%version%";

The output will be similar to the following:
'innodb_version', '5.6.22'
'protocol_version', '10'
'slave_type_conversions', ''
'version', '5.6.22-log'
'version_comment', 'MySQL Community Server (GPL)'
'version_compile_machine', 'x86_64'
'version_compile_os', 'Win64'

7. Verify that the collation is case insensitive (CEV-15511).

The ElectricFlow server expects the MySQL database, tables, and columns to have case-insensitive collation (utf8_unicode_ci). You must check for case-insensitive collation before you connect the ElectricFlow server to the database for the first time (meaning when the database schema is empty). So first verify the collation as described in https://stackoverflow.com/questions/3936967/mysql-case-insensitive-select.

Comparisons are case insensitive when the column uses a collation that ends with _ci (such as latin1_general_ci collation, which is the default) and case sensitive when the column uses a collation that ends with _cs or _bin (such as the utf8_unicode_cs and utf8_bin collations).

To check your server, database, and connection collations, enter:

mysql> show variables like '%collation%';

+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

To check the collation (meaning case sensitive or insensitive) and character_set details of all the table columns, enter:

Note: You must replace perftest1 with the name of your database. Also, you must use the correct values for --host= --port= --user= --password=

OUTPUT in text format:

{code}

mysql --host=ora-db-test2 --port=3306 --user=root --password=password --execute "select '-----' ;SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA WHERE schema_name = 'perftest1';select '-----'; SELECT T.table_name, T.table_collation, CCSA.character_set_name FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = 'perftest1'; select '-----'; SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.COLUMNS WHERE character_set_name != 'NULL' AND table_schema = 'perftest1'; select '-----' ;" > /tmp/schema_character_set_details.txt

{code}

Or OUTPUT in HTML format:

{code}

mysql --host=ora-db-test2 --port=3306 --user=root --password=password --html --execute "select '-----' ;SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA WHERE schema_name = 'perftest1';select '-----'; SELECT T.table_name, T.table_collation, CCSA.character_set_name FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = 'perftest1'; select '-----'; SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.COLUMNS WHERE character_set_name != 'NULL' AND table_schema = 'perftest1'; select '-----' ;" > /tmp/schema_character_set_details.html

{code}

Check the /tmp/schema_character_set_details.txt or /tmp/schema_character_set_details.html file.

Note: The attached Flow_8.0.1_schema_character_set_details.html shows the character set and collation of tables and columns (all of which end with _ci, which indicates that the collation is case insensitive) in an ElectricFlow 8.0.1 schema.

In this ElectricFlow 8.0.1 schema, running the following query:

select hex(id), name from ec_report_object_type where name = 'pipelineRun'
union all
select hex(id), name from ec_report_object_type where name = 'pipelinerun' ;

returns two rows that show that the where clause filter is case insensitive.

References

http://wiki/display/ec/KBEC-00087+-+Upgrading+MySQL+for+bug+fixes+or+to+the+64-bit+Enterprise+Edition

https://electriccloud.zendesk.com/hc/en-us/articles/202827143-KBEC-00038-Improving-ElectricCommander-server-performance-by-tuning-memory-allocation

max_long_data_size was removed in MySQL 5.6 and replaced by max_allowed_packet (see https://bugs.mysql.com/bug.php?id=77781). Also review https://ask.electric-cloud.com/questions/3104/mysql-ec-how-do-i-address-ectool-import-errors-for.html where Electric Cloud suggests increasing max_allowed_packet for setting large values for properties using: ectool --debug 1 --timeout 300 setProperty /projects/TEST/procedures/TestProc/BigData --valueFile /home/build/BigData.txt

https://www.askapache.com/mysql/init_connect-speed-up/

Have more questions?

0 Comments

Article is closed for comments.