Results 1 to 3 of 3

Thread: mysql issues on Plesk server - too many connections

  1. #1

    Default mysql issues on Plesk server - too many connections

    My config
    Plesk 9.5.4
    OS Linux CentOS 5 32 bits 2.6.33.5-1ikl
    CPU GenuineIntel, Intel(R) Xeon(R)CPU X3430 @ 2.40GHz (4 cores, 2.4 GHz)
    4x2 Go DDR3

    I have had big instability trouble for 4 days now on this server wich is oversized for my use (about 40 domains running, but the top domain has 1500 user/day in the best case...)
    i also have smaller servers and never had these issues.
    My server hangs and i got "too many connection" error, on domains, Plesk...
    I never changed default mysql parameters sofar. These are linux/ Plesk.

    So I did :
    mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` variables
    => wait_timeout 28800

    mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow ` variables | grep 'max.*connections'
    => max_connections 100
    => max_user_connections 0

    mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` extended-status | grep Max_used_connections
    Max_used_connections 91

    BUT
    mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` processlist
    i got 99% of these connections in "sleep" : no request = pas RAM used but connection blocked for users.

    I can add that in my php scripts i think all my mysql REQUEST are closed by mysql_close();

    How can i tweak my.cnf to prevent these problems ?
    raise max_connection ? up to ?
    decrease wait_timeout ? down to ?
    increasse/decrease other values ?

    Thanks for any tips, hints, help



  2. #2

    Default

    A mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` variables gives me
    |auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | automatic_sp_privileges | ON |
    | back_log | 50 |
    | bdb_cache_size | 8384512 |
    | bdb_log_buffer_size | 262144 |
    | bdb_logdir | |
    | bdb_max_lock | 10000 |
    | bdb_shared_data | OFF |
    | binlog_cache_size | 32768 |
    | bulk_insert_buffer_size | 8388608 |
    | completion_type | 0 |
    | concurrent_insert | 1 |
    | connect_timeout | 10 |
    | default_week_format | 0 |
    | delay_key_write | ON |
    | delayed_insert_limit | 100 |
    | delayed_insert_timeout | 300 |
    | delayed_queue_size | 1000 |
    | div_precision_increment | 4 |
    | keep_files_on_create | OFF |
    | engine_condition_pushdown | OFF |
    | expire_logs_days | 0 |
    | flush | OFF |
    | flush_time | 0 |
    | ft_boolean_syntax | + -><()~*:""&| |
    | ft_max_word_len | 84 |
    | ft_min_word_len | 4 |
    | ft_query_expansion_limit | 20 |
    | ft_stopword_file | (built-in) |
    | group_concat_max_len | 1024 |
    | have_archive | NO |
    | have_bdb | YES |
    | have_blackhole_engine | NO |
    | have_compress | YES |
    | have_crypt | YES |
    | have_csv | NO |
    | have_dynamic_loading | YES |
    | have_example_engine | NO |
    | have_federated_engine | NO |
    | have_geometry | YES |
    | have_innodb | YES |
    | have_isam | NO |
    | have_merge_engine | YES |
    | have_ndbcluster | NO |
    | have_openssl | DISABLED |
    | have_ssl | DISABLED |
    | have_query_cache | YES |
    | have_raid | NO |
    | have_rtree_keys | YES |
    | have_symlink | YES |
    | init_connect | |
    | init_file | |
    | init_slave | |
    | innodb_additional_mem_pool_size | 1048576 |
    | innodb_autoextend_increment | 8 |
    | innodb_buffer_pool_awe_mem_mb | 0 |
    | innodb_buffer_pool_size | 8388608 |
    | innodb_checksums | ON |
    | innodb_commit_concurrency | 0 |
    | innodb_concurrency_tickets | 500 |
    | innodb_data_file_path | ibdata1:10M:autoextend |
    | innodb_data_home_dir | |
    | innodb_adaptive_hash_index | ON |
    | innodb_doublewrite | ON |
    | innodb_fast_shutdown | 1 |
    | innodb_file_io_threads | 4 |
    | innodb_file_per_table | OFF |
    | innodb_flush_log_at_trx_commit | 1 |
    | innodb_flush_method | |
    | innodb_force_recovery | 0 |
    | innodb_lock_wait_timeout | 50 |
    | innodb_locks_unsafe_for_binlog | OFF |
    | innodb_log_arch_dir | |
    | innodb_log_archive | OFF |
    | innodb_log_buffer_size | 1048576 |
    | innodb_log_file_size | 5242880 |
    | innodb_log_files_in_group | 2 |
    | innodb_log_group_home_dir | ./ |
    | innodb_max_dirty_pages_pct | 90 |
    | innodb_max_purge_lag | 0 |
    | innodb_mirrored_log_groups | 1 |
    | innodb_open_files | 300 |
    | innodb_rollback_on_timeout | OFF |
    | innodb_support_xa | ON |
    | innodb_sync_spin_loops | 20 |
    | innodb_table_locks | ON |
    | innodb_thread_concurrency | 8 |
    | innodb_thread_sleep_delay | 10000 |
    | interactive_timeout | 28800 |
    | join_buffer_size | 131072 |
    | key_buffer_size | 8384512 |
    | key_cache_age_threshold | 300 |
    | key_cache_block_size | 1024 |
    | key_cache_division_limit | 100 |
    | large_files_support | ON |
    | large_page_size | 0 |
    | large_pages | OFF |
    | lc_time_names | en_US |
    | license | GPL |
    | local_infile | OFF |
    | locked_in_memory | OFF |
    | log | OFF |
    | log_bin | OFF |
    | log_bin_trust_function_creators | OFF |
    | log_error | |
    | log_queries_not_using_indexes | OFF |
    | log_slave_updates | OFF |
    | log_slow_queries | OFF |
    | log_warnings | 1 |
    | long_query_time | 10 |
    | low_priority_updates | OFF |
    | lower_case_file_system | OFF |
    | lower_case_table_names | 0 |
    | max_allowed_packet | 1048576 |
    | max_binlog_cache_size | 4294963200 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 10 |
    | max_connections | 100 |
    | max_delayed_threads | 20 |
    | max_error_count | 64 |
    | max_heap_table_size | 16777216 |
    | max_insert_delayed_threads | 20 |
    | max_join_size | 4294967295 |
    | max_length_for_sort_data | 1024 |
    | max_prepared_stmt_count | 16382 |
    | max_relay_log_size | 0 |
    | max_seeks_for_key | 4294967295 |
    | max_sort_length | 1024 |
    | max_sp_recursion_depth | 0 |
    | max_tmp_tables | 32 |
    | max_user_connections | 0 |
    | max_write_lock_count | 4294967295 |
    | multi_range_count | 256 |
    | myisam_data_pointer_size | 6 |
    | myisam_max_sort_file_size | 2146435072 |
    | myisam_recover_options | OFF |
    | myisam_repair_threads | 1 |
    | myisam_sort_buffer_size | 8388608 |
    | myisam_stats_method | nulls_unequal |
    | net_buffer_length | 16384 |
    | net_read_timeout | 30 |
    | net_retry_count | 10 |
    | net_write_timeout | 60 |
    | new | OFF |
    | old_passwords | ON |
    | open_files_limit | 1024 |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | port | 3306 |
    | preload_buffer_size | 32768 |
    | profiling | OFF |
    | profiling_history_size | 15 |
    | protocol_version | 10 |
    | query_alloc_block_size | 8192 |
    | query_cache_limit | 1048576 |
    | query_cache_min_res_unit | 4096 |
    | query_cache_size | 0 |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 8192 |
    | range_alloc_block_size | 4096 |
    | read_buffer_size | 131072 |
    | read_only | OFF |
    | read_rnd_buffer_size | 262144 |
    | relay_log | |
    | relay_log_index | |
    | relay_log_info_file | relay-log.info |
    | relay_log_purge | ON |
    | relay_log_space_limit | 0 |
    | rpl_recovery_rank | 0 |
    | secure_auth | OFF |
    | secure_file_priv | |
    | server_id | 0 |
    | skip_external_locking | ON |
    | skip_networking | OFF |
    | skip_show_database | OFF |
    | slave_compressed_protocol | OFF |
    | slave_load_tmpdir | /tmp/ |
    | slave_net_timeout | 3600 |
    | slave_skip_errors | OFF |
    | slave_transaction_retries | 10 |
    | slow_launch_time | 2 |
    | sort_buffer_size | 2097144 |
    | sql_big_selects | ON |
    | sql_mode | |
    | sql_notes | ON |
    | sql_warnings | OFF |
    | ssl_ca | |
    | ssl_capath | |
    | ssl_cert | |
    | ssl_cipher | |
    | ssl_key | |
    | storage_engine | MyISAM |
    | sync_binlog | 0 |
    | sync_frm | ON |
    | system_time_zone | CET |
    | table_cache | 64 |
    | table_lock_wait_timeout | 50 |
    | table_type | MyISAM |
    | thread_cache_size | 0 |
    | thread_stack | 196608 |
    | time_format | %H:%i:%s |
    | time_zone | SYSTEM |
    | timed_mutexes | OFF |
    | tmp_table_size | 33554432 |
    | tmpdir | /tmp/ |
    | transaction_alloc_block_size | 8192 |
    | transaction_prealloc_size | 4096 |
    | tx_isolation | REPEATABLE-READ |
    | updatable_views_with_limit | YES |
    | version | 5.0.77 |
    | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) |
    | version_comment | Source distribution |
    | version_compile_machine | i686 |
    | version_compile_os | redhat-linux-gnu |
    | wait_timeout | 28800 |



  3. #3
    Product Expert
    Join Date
    Nov 2002
    Location
    Washington, DC
    Posts
    3,753

    Default

    check out mysqltuner, you can grab it from the atomic repo (yum install mysqltuner). It will analyse your mysql stats and provide recommendations on tuning your configuration.
    Atomicorp
    www.atomicorp.com
    Secure your server now: http://www.atomicorp.com/products.html Atomic Secured Linux



Posting Permissions

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