Magento Expert Forum - Improve your Magento experience

Results 1 to 3 of 3

Set MySQL Connection TimeOut

  1. #1
    Administrator david's Avatar
    Join Date
    Nov 2012
    Posts
    261
    Thanks
    22
    Thanked 42 Times in 34 Posts

    Post Set MySQL Connection TimeOut

    Sometimes you find a condition where your database connection (MySQL) is time out because of your queries are spending too much time. This is a hint on how you increase your MySQL time out configuration.

    Login to your root acccount first

    Code:
    mysql -uroot -p
    Enter your root password

    First you query your default connection timeout.

    Code:
    SHOW VARIABLES LIKE 'connect_timeout';
    As you can see below, i have approximately 10 seconds before my mysql connection time out.


    Next is updated it to 60 seconds.

    Code:
    SET GLOBAL connect_timeout=60;
    Now for example you query all of variables relate to InnoDB you can do like this

    Code:
    show variables like 'innodb_%';
    It will show the result like this

    Code:
    +---------------------------------+------------------------+
    | Variable_name                   | Value                  |
    +---------------------------------+------------------------+
    | innodb_adaptive_flushing        | ON                     |
    | innodb_adaptive_hash_index      | ON                     |
    | innodb_additional_mem_pool_size | 8388608                |
    | innodb_autoextend_increment     | 8                      |
    | innodb_autoinc_lock_mode        | 1                      |
    | innodb_buffer_pool_instances    | 1                      |
    | innodb_buffer_pool_size         | 134217728              |
    | innodb_change_buffering         | all                    |
    | innodb_checksums                | ON                     |
    | innodb_commit_concurrency       | 0                      |
    | innodb_concurrency_tickets      | 500                    |
    | innodb_data_file_path           | ibdata1:10M:autoextend |
    | innodb_data_home_dir            |                        |
    | innodb_doublewrite              | ON                     |
    | innodb_fast_shutdown            | 1                      |
    | innodb_file_format              | Antelope               |
    | innodb_file_format_check        | ON                     |
    | innodb_file_format_max          | Antelope               |
    | innodb_file_per_table           | OFF                    |
    | innodb_flush_log_at_trx_commit  | 1                      |
    | innodb_flush_method             |                        |
    | innodb_force_load_corrupted     | OFF                    |
    | innodb_force_recovery           | 0                      |
    | innodb_io_capacity              | 200                    |
    | innodb_large_prefix             | OFF                    |
    | innodb_lock_wait_timeout        | 50                     |
    | innodb_locks_unsafe_for_binlog  | OFF                    |
    | innodb_log_buffer_size          | 8388608                |
    | innodb_log_file_size            | 5242880                |
    | innodb_log_files_in_group       | 2                      |
    | innodb_log_group_home_dir       | ./                     |
    | innodb_max_dirty_pages_pct      | 75                     |
    | innodb_max_purge_lag            | 0                      |
    | innodb_mirrored_log_groups      | 1                      |
    | innodb_old_blocks_pct           | 37                     |
    | innodb_old_blocks_time          | 0                      |
    | innodb_open_files               | 300                    |
    | innodb_print_all_deadlocks      | OFF                    |
    | innodb_purge_batch_size         | 20                     |
    | innodb_purge_threads            | 0                      |
    | innodb_random_read_ahead        | OFF                    |
    | innodb_read_ahead_threshold     | 56                     |
    | innodb_read_io_threads          | 4                      |
    | innodb_replication_delay        | 0                      |
    | innodb_rollback_on_timeout      | OFF                    |
    | innodb_rollback_segments        | 128                    |
    | innodb_spin_wait_delay          | 6                      |
    | innodb_stats_method             | nulls_equal            |
    | innodb_stats_on_metadata        | ON                     |
    | innodb_stats_sample_pages       | 8                      |
    | innodb_strict_mode              | OFF                    |
    | innodb_support_xa               | ON                     |
    | innodb_sync_spin_loops          | 30                     |
    | innodb_table_locks              | ON                     |
    | innodb_thread_concurrency       | 0                      |
    | innodb_thread_sleep_delay       | 10000                  |
    | innodb_use_native_aio           | ON                     |
    | innodb_use_sys_malloc           | ON                     |
    | innodb_version                  | 5.5.31                 |
    | innodb_write_io_threads         | 4                      |
    +---------------------------------+------------------------+
    60 rows in set (0.00 sec)

    View more threads in the same category:


  2. #2
    Junior Member
    Join Date
    Sep 2018
    Location
    Oman, Muscat
    Posts
    2,084
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Default

    Log in to your server using SSH.
    Edit my.cnf (the MySQL configuration file). sudo vi /etc/my.cnf.
    Locate the timeout configuration and adjust it to fit your server.
    Save the changes and exit the editor.
    Restart MySQL to apply the changes as follows: sudo /etc/init.d/mysql restart.

Similar Threads

  1. Connect Netbean to MySQL
    By david in forum PHP programming
    Replies: 1
    Last Post: 14-03-2019, 07:46 AM
  2. Resetting MySQL Root Password: Red Hat and CentOS
    By david in forum Programming & Development
    Replies: 0
    Last Post: 03-04-2013, 08:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •