Announcement

Announcement Module
Collapse
No announcement yet.

Help need some optimization advice

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help need some optimization advice

    Hello first of here is my SHOW STATUS
    server specs
    8GB memory
    OS Fedora Core 4
    Dual Intel(R) Xeon(TM) CPU 3.40GHz

    +-----------------------------------+-----------+
    | Variable_name | Value |
    +-----------------------------------+-----------+
    | Aborted_clients | 5490 |
    | Aborted_connects | 1 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 279 |
    | Bytes_sent | 1594 |
    | Com_admin_commands | 0 |
    | Com_alter_db | 0 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_change_db | 0 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_checksum | 0 |
    | Com_commit | 0 |
    | Com_create_db | 0 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_table | 0 |
    | Com_dealloc_sql | 0 |
    | Com_delete | 0 |
    | Com_delete_multi | 0 |
    | Com_do | 0 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 0 |
    | Com_drop_user | 0 |
    | Com_execute_sql | 0 |
    | Com_flush | 0 |
    | Com_grant | 0 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_help | 0 |
    | Com_insert | 0 |
    | Com_insert_select | 0 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 0 |
    | Com_optimize | 0 |
    | Com_preload_keys | 0 |
    | Com_prepare_sql | 0 |
    | Com_purge | 0 |
    | Com_purge_before_date | 0 |
    | Com_rename_table | 0 |
    | Com_repair | 0 |
    | Com_replace | 0 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_revoke_all | 0 |
    | Com_rollback | 0 |
    | Com_savepoint | 0 |
    | Com_select | 0 |
    | Com_set_option | 0 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 0 |
    | Com_show_charsets | 0 |
    | Com_show_collations | 0 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 0 |
    | Com_show_create_table | 0 |
    | Com_show_databases | 0 |
    | Com_show_errors | 0 |
    | Com_show_fields | 0 |
    | Com_show_grants | 0 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 0 |
    | Com_show_logs | 0 |
    | Com_show_master_status | 0 |
    | Com_show_ndb_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_privileges | 0 |
    | Com_show_processlist | 0 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 1 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 0 |
    | Com_show_triggers | 0 |
    | Com_show_variables | 7 |
    | Com_show_warnings | 0 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_stmt_close | 0 |
    | Com_stmt_execute | 0 |
    | Com_stmt_fetch | 0 |
    | Com_stmt_prepare | 0 |
    | Com_stmt_reset | 0 |
    | Com_stmt_send_long_data | 0 |
    | Com_truncate | 0 |
    | Com_unlock_tables | 0 |
    | Com_update | 0 |
    | Com_update_multi | 0 |
    | Com_xa_commit | 0 |
    | Com_xa_end | 0 |
    | Com_xa_prepare | 0 |
    | Com_xa_recover | 0 |
    | Com_xa_rollback | 0 |
    | Com_xa_start | 0 |
    | Compression | OFF |
    | Connections | 2851 |
    | Created_tmp_disk_tables | 0 |
    | Created_tmp_files | 52 |
    | Created_tmp_tables | 8 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 0 |
    | Handler_discover | 0 |
    | Handler_prepare | 0 |
    | Handler_read_first | 0 |
    | Handler_read_key | 0 |
    | Handler_read_next | 0 |
    | Handler_read_prev | 0 |
    | Handler_read_rnd | 0 |
    | Handler_read_rnd_next | 27 |
    | Handler_rollback | 0 |
    | Handler_savepoint | 0 |
    | Handler_savepoint_rollback | 0 |
    | Handler_update | 0 |
    | Handler_write | 150 |
    | Innodb_buffer_pool_pages_data | 19 |
    | Innodb_buffer_pool_pages_dirty | 0 |
    | Innodb_buffer_pool_pages_flushed | 0 |
    | Innodb_buffer_pool_pages_free | 493 |
    | Innodb_buffer_pool_pages_latched | 0 |
    | Innodb_buffer_pool_pages_misc | 0 |
    | Innodb_buffer_pool_pages_total | 512 |
    | Innodb_buffer_pool_read_ahead_rnd | 1 |
    | Innodb_buffer_pool_read_ahead_seq | 0 |
    | Innodb_buffer_pool_read_requests | 77 |
    | Innodb_buffer_pool_reads | 12 |
    | Innodb_buffer_pool_wait_free | 0 |
    | Innodb_buffer_pool_write_requests | 0 |
    | Innodb_data_fsyncs | 3 |
    | Innodb_data_pending_fsyncs | 0 |
    | Innodb_data_pending_reads | 0 |
    | Innodb_data_pending_writes | 0 |
    | Innodb_data_read | 2494464 |
    | Innodb_data_reads | 25 |
    | Innodb_data_writes | 3 |
    | Innodb_data_written | 1536 |
    | Innodb_dblwr_pages_written | 0 |
    | Innodb_dblwr_writes | 0 |
    | Innodb_log_waits | 0 |
    | Innodb_log_write_requests | 0 |
    | Innodb_log_writes | 1 |
    | Innodb_os_log_fsyncs | 3 |
    | Innodb_os_log_pending_fsyncs | 0 |
    | Innodb_os_log_pending_writes | 0 |
    | Innodb_os_log_written | 512 |
    | Innodb_page_size | 16384 |
    | Innodb_pages_created | 0 |
    | Innodb_pages_read | 19 |
    | Innodb_pages_written | 0 |
    | Innodb_row_lock_current_waits | 0 |
    | Innodb_row_lock_time | 0 |
    | Innodb_row_lock_time_avg | 0 |
    | Innodb_row_lock_time_max | 0 |
    | Innodb_row_lock_waits | 0 |
    | Innodb_rows_deleted | 0 |
    | Innodb_rows_inserted | 0 |
    | Innodb_rows_read | 0 |
    | Innodb_rows_updated | 0 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 0 |
    | Key_blocks_used | 14497 |
    | Key_read_requests | 94036894 |
    | Key_reads | 4019574 |
    | Key_write_requests | 15695044 |
    | Key_writes | 975770 |
    | Last_query_cost | 10.499000 |
    | Max_used_connections | 29 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 136 |
    | Open_streams | 0 |
    | Open_tables | 89 |
    | Opened_tables | 0 |
    | Qcache_free_blocks | 0 |
    | Qcache_free_memory | 0 |
    | Qcache_hits | 0 |
    | Qcache_inserts | 0 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 0 |
    | Qcache_queries_in_cache | 0 |
    | Qcache_total_blocks | 0 |
    | Questions | 363495 |
    | Rpl_status | NULL |
    | Select_full_join | 0 |
    | Select_full_range_join | 0 |
    | Select_range | 0 |
    | Select_range_check | 0 |
    | Select_scan | 8 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 0 |
    | Sort_merge_passes | 0 |
    | Sort_range | 0 |
    | Sort_rows | 0 |
    | Sort_scan | 0 |
    | Ssl_accept_renegotiates | 0 |
    | Ssl_accepts | 0 |
    | Ssl_callback_cache_hits | 0 |
    | Ssl_cipher | |
    | Ssl_cipher_list | |
    | Ssl_client_connects | 0 |
    | Ssl_connect_renegotiates | 0 |
    | Ssl_ctx_verify_depth | 0 |
    | Ssl_ctx_verify_mode | 0 |
    | Ssl_default_timeout | 0 |
    | Ssl_finished_accepts | 0 |
    | Ssl_finished_connects | 0 |
    | Ssl_session_cache_hits | 0 |
    | Ssl_session_cache_misses | 0 |
    | Ssl_session_cache_mode | NONE |
    | Ssl_session_cache_overflows | 0 |
    | Ssl_session_cache_size | 0 |
    | Ssl_session_cache_timeouts | 0 |
    | Ssl_sessions_reused | 0 |
    | Ssl_used_session_cache_entries | 0 |
    | Ssl_verify_depth | 0 |
    | Ssl_verify_mode | 0 |
    | Ssl_version | |
    | Table_locks_immediate | 285831 |
    | Table_locks_waited | 276 |
    | Tc_log_max_pages_used | 0 |
    | Tc_log_page_size | 0 |
    | Tc_log_page_waits | 0 |
    | Threads_cached | 0 |
    | Threads_connected | 12 |
    | Threads_created | 2850 |
    | Threads_running | 1 |
    | Uptime | 5080 |
    +-----------------------------------+-----------+


    The problem we are having and I am new to MySQL is that we use mysql as a backend to our alerts kinda like syslog repository. Everytime there is an alert the data gets inserted into the DB. Alerts are being generated from about 40 servers. Nightly we run a script that goes through and removes (DELETE) deletes data that is older than 7 days(which =~100k-200k alerts per day). So at any giving day the last event should be <7 days from current date.

    During the DELETE procedure that occurs nightly we get large number of dropped alerts because (not sure) the tables that it needs to write to are locked and also being accessed by the script.

    What would be the best way to optimize our SQL server for fast INSERTS, DELETES, UPDATES which is mostly what we do. It appears as though INSERTS during this DELETING period are somehow dropped not allowed if we sum up the total that should have been INSERTED with the actual number of successful INSERTS its about about 2 our of 10.

    Any help ideas suggestions? As I am all out of ideas.
    thanks for the help in advanced and please let me know if you have questions.

  • #2
    If you're using MyISAM I'd use one table per week and use merge table for querying. This way you can simply drop old weekly table instead of running expensive delete.

    Comment


    • #3
      The problem is tha we have about 40 tables and each server corresponds to a server ID which gets created depending on which one reports first. In other words that wouldn't even be an option due to the large amount of configuration changes that would have to be done on the server that are reporting to the MySQL server. Are there any changes that can be done to improve INSERT, DELETE, UPDATE that are being done?

      Thanks for the quick reply!!

      Comment


      • #4
        Well,

        You can use Innodb which does not have the problem with concurrency.

        You can use concurrent_insert=2 (I'm not 100% sure if it will help with long running delete though)

        You can use INSERT DELAYED and raise insert delayed queue to be large enough to accommodate all inserts.

        You can do inserts in temporary table and merge them into main table every so often.

        Comment


        • #5
          Please post output of SHOW GLOBAL VARIABLES, not just SHOW VARIABLES (it's behaviour was changed in version 5, SHOW VARIABLES omits some data).

          //edited
          SHOW GLOBAL STATUS too instead of SHOW STATUS.

          Comment


          • #6
            Quote:


            | Key_blocks_unused | 0 || Key_blocks_used | 14497 || Key_read_requests | 94036894 || Key_reads | 4019574 |


            Uh oh, do you have my.cnf file at all?

            Comment


            • #7
              Humm I am sure I do, is there something that I might be doing wrong? here is a copy of it, it's located in /etc/my.cnf

              When I do a ps -ef |grep mysql here is the output

              **root 1555 1 0 Feb23 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log

              **mysql 1592 1555 7 Feb23 ? 03:33:35 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock

              MY.cnf

              [mysqld]
              datadir=/var/lib/mysql
              socket=/var/lib/mysql/mysql.sock
              # Default to using old password format for compatibility with mysql 3.x
              # clients (those using the mysqlclient10 compatibility package).
              old_passwords=1

              [mysql.server]
              user=mysql
              basedir=/var/lib
              log-error=/var/log/mysqld.log
              pid-file=/var/run/mysqld/mysqld.pid

              # The MySQL server
              # Added 02.27.07
              [mysqld]
              set-variable=max_allowed_packet=1M
              set-variable=max_user_connections=350
              set-variable=max_connections=350
              set-variable=table_cache=1200
              key_buffer_size=32M
              connect_timeout=15
              wait_timeout=15
              set-variable=max_connect_errors=999999
              set-variable=log-slow-queries=/var/log/mysql-slow.log

              #[mysqld_safe]
              log-error=/var/log/mysqld.log
              pid-file=/var/run/mysqld/mysqld.pid
              set-variable=max_connect_errors=999999
              set-variable=log-slow-queries=/var/log/mysql-slow.log




              HERE IS SHOW GLOBAL VARIABLES

              mysql> show global variables
              -> ;
              +---------------------------------+------------------------- -------------------------------+
              | Variable_name | Value |
              +---------------------------------+------------------------- -------------------------------+
              | auto_increment_increment | 1 |
              | auto_increment_offset | 1 |
              | automatic_sp_privileges | ON |
              | back_log | 50 |
              | basedir | /usr/ |
              | bdb_cache_size | 8388600 |
              | bdb_home | /var/lib/mysql/ |
              | bdb_log_buffer_size | 614400 |
              | bdb_logdir | |
              | bdb_max_lock | 10000 |
              | bdb_shared_data | OFF |
              | bdb_tmpdir | /tmp/ |
              | binlog_cache_size | 32768 |
              | bulk_insert_buffer_size | 8388608 |
              | character_set_client | latin1 |
              | character_set_connection | latin1 |
              | character_set_database | latin1 |
              | character_set_filesystem | binary |
              | character_set_results | latin1 |
              | character_set_server | latin1 |
              | character_set_system | utf8 |
              | character_sets_dir | /usr/share/mysql/charsets/ |
              | collation_connection | latin1_swedish_ci |
              | collation_database | latin1_swedish_ci |
              | collation_server | latin1_swedish_ci |
              | completion_type | 0 |
              | concurrent_insert | 1 |
              | connect_timeout | 15 |
              | datadir | /var/lib/mysql/ |
              | date_format | %Y-%m-%d |
              | datetime_format | %Y-%m-%d %H:%i:%s |
              | default_week_format | 0 |
              | delay_key_write | ON |
              | delayed_insert_limit | 100 |
              | delayed_insert_timeout | 300 |
              | delayed_queue_size | 1000 |
              | div_precision_increment | 4 |
              | 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_example_engine | NO |
              | have_federated_engine | NO |
              | have_geometry | YES |
              | have_innodb | YES |
              | have_isam | NO |
              | have_ndbcluster | NO |
              | have_openssl | 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_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_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 | 16777216 |
              | key_cache_age_threshold | 300 |
              | key_cache_block_size | 1024 |
              | key_cache_division_limit | 100 |
              | language | /usr/share/mysql/english/ |
              | large_files_support | ON |
              | large_page_size | 0 |
              | large_pages | OFF |
              | license | GPL |
              | local_infile | ON |
              | locked_in_memory | OFF |
              | log | OFF |
              | log_bin | OFF |
              | log_bin_trust_function_creators | OFF |
              | log_error | /var/log/mysqld.log |
              | log_slave_updates | OFF |
              | log_slow_queries | ON |
              | log_warnings | 1 |
              | long_query_time | 10 |
              | low_priority_updates | OFF |
              | lower_case_file_system | OFF |
              | lower_case_table_names | 0 |
              | max_allowed_packet | 1047552 |
              | max_binlog_cache_size | 4294967295 |
              | max_binlog_size | 1073741824 |
              | max_connect_errors | 999999 |
              | max_connections | 350 |
              | 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 | 350 |
              | max_write_lock_count | 4294967295 |
              | multi_range_count | 256 |
              | myisam_data_pointer_size | 6 |
              | myisam_max_sort_file_size | 2147483647 |
              | 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 | 2760 |
              | optimizer_prune_level | 1 |
              | optimizer_search_depth | 62 |
              | pid_file | /var/run/mysqld/mysqld.pid |
              | prepared_stmt_count | 0 |
              | port | 3306 |
              | preload_buffer_size | 32768 |
              | 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 | 2048 |
              | read_buffer_size | 131072 |
              | read_only | OFF |
              | read_rnd_buffer_size | 262144 |
              | relay_log_purge | ON |
              | relay_log_space_limit | 0 |
              | rpl_recovery_rank | 0 |
              | secure_auth | OFF |
              | 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 |
              | socket | /var/lib/mysql/mysql.sock |
              | sort_buffer_size | 2097144 |
              | sql_mode | |
              | sql_notes | ON |
              | sql_warnings | ON |
              | storage_engine | MyISAM |
              | sync_binlog | 0 |
              | sync_frm | ON |
              | system_time_zone | EST |
              | table_cache | 1200 |
              | 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 | |
              | transaction_alloc_block_size | 8192 |
              | transaction_prealloc_size | 4096 |
              | tx_isolation | REPEATABLE-READ |
              | updatable_views_with_limit | YES |
              | version | 5.0.22-log |
              | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 25, 2006) |
              | version_comment | Source distribution |
              | version_compile_machine | i686 |
              | version_compile_os | redhat-linux-gnu |
              | wait_timeout | 15 |
              +---------------------------------+------------------------- -------------------------------+
              218 rows in set (0.00 sec)

              Comment


              • #8
                Do you really have it named MY.cnf ?

                Unix/Linux are case sensitive so it should be /etc/my.cnf not not MY.cnf

                It looks like for one or another reason your config file is not used as you specify key_buffer of 32M while 16MB seems to be used.

                Comment


                • #9
                  mysqluser81 wrote on Fri, 23 February 2007 13:51



                  Any help ideas suggestions? As I am all out of ideas.


                  After you sort out your configuration problems (32MB key buffer for a 8GB machine is too small, I'd start with at least 512MB) you can use "limit" trick. You don't have to delete all rows with a single query, you can do something like (pseudocode):

                  do {
                  DELETE FROM table WHERE date < (....) LIMIT 1000; # takes less than 1 second
                  sleep(5); # lets server process updates & inserts
                  }
                  until ($affected_rows == 0); # until no more rows left to delete

                  Comment


                  • #10
                    The name of the file is my.cnf. It's stored in /etc directory. Sorry for using CAPS when stating MY.cnf. Any ideas as to why only 16MB is being used when I have stated 32?

                    I will make the change to 512mb.

                    As to delete code. We are running DELETE FROM table WHERE date for the automatic removal of stale records.

                    I'll make the changes suggested and see how much of a difference it wil make.

                    THANKS FOR ALL THE HELP!!

                    Comment


                    • #11
                      Well,

                      Something is wrong in your setup. Check by restarting MySQL if it really does not pick it up. Also check for my.cnf in other places such as data directory which may override it as well as permissions on my.cnf

                      Checking mysql error logs also may be good idea even though config file parsing does not normally generate a lot of error messages.

                      Comment


                      • #12
                        Not sure what I have wrong or what I am doing wrong but I can figure this out. Help...

                        I did a search for both my.cnf and .cnf on my system the only file found was my.cnf which is store in /etc directory

                        Another thing that I found that was not taking is the "wait_timeout setting that I am configuring in my.cnf. But the weird thing is that the connect_timeout is taking/changing correctly so are other settings like max_connections.

                        Any ideas as to why the settings might not be taking place. I do restart the mysqld server after every change.

                        Again here is a copy of ps -ef |grep mysql command and also a copy of my.cnf. One did I have a question is do I have the variables defined correctly under mysqld and mysqld_safe? I have the mysqld_safe commented out as you can tell below, do I need to include the same variables under there also?

                        thanks for the help I really appreciate it!


                        #ps -ef command output
                        root 1555 1 0 Feb27 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log

                        mysql 1592 1555 10 Feb27 ? 09:42:45 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock

                        #my.cnf file located in /etc directory
                        [mysqld]
                        datadir=/var/lib/mysql
                        socket=/var/lib/mysql/mysql.sock
                        # Default to using old password format for compatibility with mysql 3.x
                        # clients (those using the mysqlclient10 compatibility package).
                        old_passwords=1

                        [mysql.server]
                        user=mysql
                        basedir=/var/lib
                        log-error=/var/log/mysqld.log
                        pid-file=/var/run/mysqld/mysqld.pid

                        # The MySQL server
                        # Added 02.27.07
                        [mysqld]
                        set-variable=max_allowed_packet=1M
                        set-variable=max_user_connections=350
                        set-variable=max_connections=350
                        set-variable=table_cache=1200
                        key_buffer_size=32M
                        connect_timeout=15
                        wait_timeout=15
                        set-variable=max_connect_errors=999999
                        set-variable=log-slow-queries=/var/log/mysql-slow.log

                        #[mysqld_safe]
                        log-error=/var/log/mysqld.log
                        pid-file=/var/run/mysqld/mysqld.pid
                        set-variable=max_connect_errors=999999
                        set-variable=log-slow-queries=/var/log/mysql-slow.log

                        Comment


                        • #13
                          Did you check permissions ?

                          Comment


                          • #14
                            Checked permissions and it appears that mysql user did not have permissions I added the mysql group to the permissions and now it appears to have taken the settings. Getting closer thanks to you guys!!!!!!!!!!


                            The problem that I do however contine to see is that the server are losing connection to the mysql database when trying to write to it. We have noticed that this problem mostly happens on heavy alerting/load servers. Are there any other changes/config edits that I can make to help improve or maybe even as a start troubleshoot this mysql problem. Are there any debug/commands that I can run to see what is causing mysql to drop/not being able to handle multiple various INSERTS during heavy traffic times?

                            Thanks again for all the help.

                            Comment


                            • #15
                              How exactly it happens ? What command ? How long after command started and what is error code ?

                              Normally even with table locks once command is sent MySQL will wait forever fore reply. Firewalls however may drop connections.

                              Comment

                              Working...
                              X