Hello MySQL Gurus, I'm using MySQL 3.23.49-log on Debian Linux 2.4.18-bf2.4. I am using replication over ssh tunnels, and one of the slaves is apparently in need of performance tuning. After reviewing the status and variable settings (detailed below) and the MySQL online manual, I decided that I needed to up the key_buffer_size. Unfortunately, I've not been successful in doing this. I tried setting it at the command line: mysql> set key_buffer_size=32M; ERROR 1064: You have an error in your SQL syntax near 'key_buffer_size=32M' at line 1 I tried setting it in /etc/mysql/my.cnf (detailed below), restarted mysqld, and did a show variables, and it was still 16M. I thought maybe this is variable that is dependent upon the master db. So I changed the master /etc/mysql/my.cnf to set the key_buffer_size to 32M, and restarted it. I did a show variables on the master, and indeed it now has a key_buffer_size of 32M. I went back to the slave and restarted it, but it *STILL* has a key_buffer_size of 16M (and it still has the 32M setting in the my.cnf). So, does anyone have any suggestions? Of course, this is predicated on my possibly naive idea that changing the key_buffer_size will help. If anyone else has suggestions on other tuning, those would be much appreciated, too. Has anyone done much performance tuning on replicated databases? I imagine that there might be some differences between that and non-replicated db tuning. thanks very much! Liz ----------------------slave's variables----------------------- | back_log | 50 | basedir | /usr/ | bdb_cache_size | 8388600 | bdb_log_buffer_size | 32768 | bdb_home | /var/lib/mysql/ | bdb_max_lock | 10000 | bdb_logdir | | bdb_shared_data | OFF | bdb_tmpdir | /tmp/ | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (February 14, 2002) | binlog_cache_size | 32768 | character_set | latin1 | character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru k oi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 | | concurrent_insert | ON| | connect_timeout | 5| | datadir | /var/lib/mysql/ | | delay_key_write | ON | | delayed_insert_limit | 100| | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | flush | OFF| | flush_time | 0| | have_bdb | YES| | have_gemini | NO | have_innodb | DISABLED | have_isam | YES| | have_raid | YES| | have_openssl | NO| | init_file || | innodb_additional_mem_pool_size | 1048576| | innodb_buffer_pool_size | 8388608| | innodb_data_file_path || | innodb_data_home_dir || | innodb_file_io_threads | 4| | innodb_force_recovery | 0| | innodb_thread_concurrency | 8| | innodb_flush_log_at_trx_commit | OFF| | innodb_fast_shutdown | OFF| | innodb_flush_method | | | innodb_lock_wait_timeout | 50 | | 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_mirrored_log_groups | 1| | interactive_timeout | 28800 | | join_buffer_size | 131072| | key_buffer_size | 16773120 | | language | /usr/share/mysql/english/| | large_files_support | ON | | locked_in_memory | OFF | | log | ON| | log_update | OFF| | log_bin | OFF | log_slave_updates | OFF| | log_long_queries | OFF| | long_query_time | 10| | low_priority_updates | OFF| | lower_case_table_names | 0| | max_allowed_packet | 1047552| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824| | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20| | max_heap_table_size | 16777216| | max_join_size | 4294967295| | max_sort_length | 1024| | max_user_connections | 0| | max_tmp_tables | 32 | | max_write_lock_count | 4294967295| | myisam_max_extra_sort_file_size | 256| | miasma_max_sort_file_size | 2047 | | myisam_recover_options | 0| | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384| | net_read_timeout | 30| | net_retry_count | 10| | net_write_timeout | 60| | open_files_limit | 0 | | pid_file | /var/run/mysqld/mysqld.pid| | port | 0| | protocol_version | 10| | record_buffer | 131072| | record_rnd_buffer | 131072 | | query_buffer_size | 0 | | safe_show_database | OFF| | server_id | 2| | slave_net_timeout | 3600 | | skip_locking | ON | | skip_networking | ON | | skip_show_database | OFF | | slow_launch_time | 2 | | socket | /var/run/mysqld/mysqld.sock| | sort_buffer | 2097144 | | sql_mode | 0| | table_cache | 64 | | table_type | MYISAM| | thread_cache_size | 0 | | thread_stack | 131072 | transaction_isolation | READ-COMMITTED | timezone | PST | tmp_table_size | 33554432 | tmpdir | /tmp/ | version | 3.23.49-log | wait_timeout | 28800 ----------------------slave's status----------------------- +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | Aborted_clients | 0 | | Aborted_connects | 1 | | Bytes_received | 13169753 | | Bytes_sent | 88367276 | | Com_admin_commands | 2 | | 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_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_insert | 1432 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_purge | 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_rollback | 0 | | Com_select | 41130 | | Com_set_option | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 0 | | Com_show_fields | 0 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_stat | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 0 | | Com_show_slave_stat | 1 | | Com_show_status | 2 | | Com_show_tables | 0 | | Com_show_variables | 4 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 0 | | Connections | 352 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 40439 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 0 | | Handler_read_first | 1 | | Handler_read_key | 81421 | | Handler_read_next | 13230926 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 392635232 | | Handler_update | 0 | | Handler_write | 2859153 | | Key_blocks_used | 7646 | | Key_read_requests | 1374160 | | Key_reads | 7600 | | Key_write_requests | 5703 | | Key_writes | 5703 | | Max_used_connections | 11 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 24 | | Open_files | 30 | | Open_streams | 0 | | Opened_tables | 30 | | Questions | 42918 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 314 | | Slave_running | ON | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 104 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | Table_locks_immediate | 43217 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 350 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 4749 |
----------------------slave's etc/mysql/my.cnf----------------------- # This will be passed to all mysql clients [client] #password = my_password port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 server-id =2 master-host=127.0.0.1 master-user=repl master-connect-retry=60 master-port = 3307 replicate-do-db=accessio_handprints set-variable = key_buffer_size=32M # # You can also put it into /var/log/mysql/mysql.log but I leave it in /var/log # for backward compatibility. Both location gets rotated by the cronjob. #log = /var/log/mysql/mysql.log log = /var/log/mysql.log basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-locking # # The skip-networkin option will no longer be set via debconf menu. # You have to manually change it if you want networking i.a. the server # listening on port 3306. The default is "disable" - for security reasons. skip-networking set-variable = key_buffer=32M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K # # Here you can see queries with especially long duration #log-slow-queries = /var/log/mysql/mysql-slow.log # # The following can be used as easy to replay backup logs or for replication #server-id = 1 #log-bin = /var/log/mysql/mysql-bin.log #binlog-do-db = include_database_name #binlog-ignore-db = include_database_name # # Read the manual if you want to enable InnoDB! skip-innodb [mysqldump] quick set-variable = max_allowed_packet=1M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] set-variable = key_buffer=32M --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
