Where is my ram?

I have a machine with 8gb of ram, but it looks like only ~2gb is used by mysql.

Also tuning-primer.sh script says that maximum ever has been 1gb, where are the other 7gb?

MEMORY USAGE
Max Memory Ever Allocated : 1 G
Configured Max Per-thread Buffers : 58 G
Configured Max Global Buffers : 1 G
Configured Max Memory Limit : 60 G
Physical Memory : 7.78 G

Max memory limit exceeds 90% of physical memory

[qgt4 home]: free -m
total used free shared buffers cached
Mem: 7972 7889 83 0 415 6432
-/+ buffers/cache: 1041 6930
Swap: 0 0 0

top - 13:00:43 up 44 days, 22:21, 1 user, load average: 0.99, 0.97, 0.91
Tasks: 60 total, 1 running, 59 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 0.0% us, 0.0% sy, 0.0% ni, 99.3% id, 0.7% wa, 0.0% hi, 0.0% si
Cpu2 : 98.0% us, 0.7% sy, 0.0% ni, 0.0% id, 1.3% wa, 0.0% hi, 0.0% si
Cpu3 : 0.3% us, 0.0% sy, 0.0% ni, 99.3% id, 0.3% wa, 0.0% hi, 0.0% si
Mem: 8164332k total, 8080884k used, 83448k free, 425556k buffers
Swap: 0k total, 0k used, 0k free, 6571100k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27534 mysql 16 0 1682m 576m 3148 S 99 7.2 125:31.53 mysqld
11152 hpsmh 21 0 379m 20m 3696 S 0 0.3 0:00.80 hpsmhd
11138 root 16 0 97796 6204 3032 S 0 0.1 0:00.07 hpsmhd
3235 root 16 0 57092 940 536 S 0 0.0 0:00.03 crond
28138 root 16 0 53996 1640 1220 S 0 0.0 0:00.02 bash
27492 root 16 0 52780 1156 952 S 0 0.0 0:00.01 mysqld_safe
28136 root 16 0 36208 2648 2056 S 0 0.0 0:00.09 sshd
9641 root 16 0 26344 9m 8084 S 0 0.1 0:00.72 vcagentd
7648 root 15 0 21920 1264 856 S 0 0.0 0:03.52 sshd
2818 root 16 0 20916 392 188 S 0 0.0 0:00.00 rpc.idmapd
11139 root 16 0 19024 1304 968 S 0 0.0 0:00.00 rotatelogs
11141 root 16 0 19024 1304 968 S 0 0.0 0:00.01 rotatelogs
3026 ntp 16 0 17520 5268 4204 S 0 0.1 0:01.21 ntpd
4776 dbus 16 0 9644 780 616 S 0 0.0 0:00.00 dbus-daemon-1
28748 root 16 0 6148 976 768 R 0 0.0 0:00.02 top
2778 root 16 0 5780 692 560 S 0 0.0 0:00.31 syslogd
1 root 16 0 4752 552 460 S 0 0.0 0:00.90 init
1464 root 6 -10 3604 452 364 S 0 0.0 0:00.01 udevd
2792 root 16 0 2548 304 204 S 0 0.0 0:00.02 irqbalance
2782 root 16 0 2536 376 300 S 0 0.0 0:00.00 klogd
4957 root 18 0 2524 404 336 S 0 0.0 0:00.00 mingetty
4959 root 18 0 2524 404 336 S 0 0.0 0:00.00 mingetty
4960 root 18 0 2524 404 336 S 0 0.0 0:00.00 mingetty
4961 root 19 0 2524 404 336 S 0 0.0 0:00.00 mingetty
4962 root 20 0 2524 404 336 S 0 0.0 0:00.00 mingetty
4963 root 21 0 2524 404 336 S 0 0.0 0:00.00 mingetty
2 root RT 0 0 0 0 S 0 0.0 0:01.21 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.04 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:01.14 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:00.03 ksoftirqd/1
6 root RT 0 0 0 0 S 0 0.0 0:01.12 migration/2

[qgt4 home]: mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14962 to server version: 5.0.26-pro-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show globals
→ ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘globals’ at line 1
mysql> show global status;
±----------------------------------±-----------+
| Variable_name | Value |
±----------------------------------±-----------+
| Aborted_clients | 10 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 360292625 |
| Bytes_sent | 4078783691 |
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_table | 69 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 88 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 15038 |
| 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 | 5 |
| 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 | 677435 |
| Com_insert_select | 107 |
| Com_kill | 1 |
| Com_load | 84 |
| 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 | 19691 |
| Com_set_option | 209 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 8 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 1 |
| 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 | 62 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 38 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 10435 |
| Com_show_triggers | 0 |
| Com_show_variables | 48 |
| 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 | 14963 |
| Created_tmp_disk_tables | 10444 |
| Created_tmp_files | 2 |
| Created_tmp_tables | 10762 |
| 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 | 7518 |
| Handler_read_key | 1196770 |
| Handler_read_next | 1664484555 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1031590 |
| Handler_read_rnd_next | 229161631 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 1490713 |
| Handler_write | 233511896 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| 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 | 1101778 |
| Key_blocks_used | 173653 |
| Key_read_requests | 543054752 |
| Key_reads | 446540 |
| Key_write_requests | 3602312 |
| Key_writes | 1135109 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 5 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 31397 |
| Open_streams | 0 |
| Open_tables | 1000 |
| Opened_tables | 73640 |
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 133794536 |
| Qcache_hits | 13 |
| Qcache_inserts | 19467 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 10753 |
| Qcache_queries_in_cache | 10 |
| Qcache_total_blocks | 29 |
| Questions | 738287 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 132 |
| Select_range_check | 0 |
| Select_scan | 17906 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 7 |
| Sort_merge_passes | 1 |
| Sort_range | 0 |
| Sort_rows | 512190 |
| Sort_scan | 21 |
| 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 | 1050576 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 1 |
| Threads_connected | 4 |
| Threads_created | 5 |
| Threads_running | 2 |
| Uptime | 73757 |
±----------------------------------±-----------+
245 rows in set (0.00 sec)

Post your my.cnf and/or the output from “SHOW VARIABLES” and tell us if you are using MyISAM or InnoDB tables.

mysql> show variables;
±--------------------------------±------------------------ ------+
| Variable_name | Value |
±--------------------------------±------------------------ ------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| 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 | 5 |
| datadir | /mysql/data/ |
| 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 | YES |
| have_bdb | NO |
| 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 | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| 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 | |
| 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 | 1451229184 |
| 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 |
| lc_time_names | en_US |
| license | Commercial |
| local_infile | ON |
| locked_in_memory | OFF |
| log | ON |
| 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 | 1047552 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 100 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 268435456 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 300 |
| net_retry_count | 10 |
| net_write_timeout | 300 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 65535 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /mysql/data/qgt4.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 20967424 |
| read_only | OFF |
| read_rnd_buffer_size | 83881984 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| 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 | /tmp/mysql.sock |
| sort_buffer_size | 20971512 |
| 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 | UTC |
| table_cache | 1000 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 16 |
| thread_stack | 262144 |
| 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.26-pro-log |
| version_comment | MySQL Pro Server (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
| wait_timeout | 28800 |
±--------------------------------±------------------------ ------+
220 rows in set (0.00 sec)

[qgt4 ~]: more /etc/my.cnf

Example MySQL config file for very large systems.

This is for a large system with memory of 1G-2G where the system runs mainly

MySQL.

You can copy this file to

/etc/my.cnf to set global options,

mysql-data-dir/my.cnf to set server-specific options (in this

installation this directory is /var/lib/mysql) or

~/.my.cnf to set user-specific options.

In this file, you can use all long options that a program supports.

If you want to know which options a program supports, run the program

with the “–help” option.

The following options will be passed to all MySQL clients

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock

Here follows entries for some specific programs

The MySQL server

[mysqld]
port = 3306
#socket = /var/lib/mysql/mysql.sock
socket = /tmp/mysql.sock
skip-locking
key_buffer = 1384M
max_allowed_packet = 1M
table_cache = 1000
open_files_limit = 65535
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 80M
myisam_sort_buffer_size = 256M
thread_cache = 16
query_cache_size = 128M

Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 8
max_connections = 500
max_connect_errors = 100
net_read_timeout = 300
net_write_timeout = 300
skip-innodb
datadir=/mysql/data
log=/mysql/data/log.log

Don’t listen on a TCP/IP port at all. This can be a security enhancement,

if all processes that need to connect to mysqld run on the same host.

All interaction with mysqld must be made via Unix sockets or named pipes.

Note that using this option without enabling named pipes on Windows

(via the “enable-named-pipe” option) will render mysqld useless!

#skip-networking

Replication Master Server (default)

binary logging is required for replication

#log-bin

required unique id between 1 and 2^32 - 1

defaults to 1 if master-host is not set

but will not function as a master if omitted

server-id = 1

Replication Slave (comment out master section to use this)

To configure this host as a replication slave, you can choose between

two methods :

1) Use the CHANGE MASTER TO command (fully described in our manual) -

the syntax is:

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,

MASTER_USER=, MASTER_PASSWORD= ;

where you replace , , by quoted strings and

by the master’s port number (3306 by default).

Example:

CHANGE MASTER TO MASTER_HOST=‘125.564.12.1’, MASTER_PORT=3306,

MASTER_USER=‘joe’, MASTER_PASSWORD=‘secret’;

OR

2) Set the variables below. However, in case you choose this method, then

start replication for the first time (even unsuccessfully, for example

if you mistyped the password in master-password and the slave fails to

connect), the slave will create a master.info file, and any later

change in this file to the variables’ values below will be ignored and

overridden by the content of the master.info file, unless you shutdown

the slave server, delete master.info and restart the slaver server.

For that reason, you may want to leave the lines below untouched

(commented) and instead use CHANGE MASTER TO (see above)

required unique id between 2 and 2^32 - 1

(and different from the master)

defaults to 2 if master-host is set

but will not function as a slave if omitted

#server-id = 2

The replication master for this slave - required

#master-host =

The username the slave will use for authentication when connecting

to the master - required

#master-user =

The password the slave will authenticate with when connecting to

the master - required

#master-password =

The port the master is listening on.

optional - defaults to 3306

#master-port =

binary logging - not required for slaves, but recommended

#log-bin

Point the following paths to different dedicated disks

#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

Uncomment the following if you are using BDB tables

#bdb_cache_size = 384M
#bdb_max_lock = 100000

Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/

You can set …_buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M

Set …_log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

Remove the next comment character if you are not familiar with SQL

#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Ok, now I understand your question.

Your MySQL is only configured to use at maximum:

[B]Quote:[/B]

| key_buffer_size | 1451229184 |

bytes for index cache. The other buffers are mainly per connection and they are only used when they need to, hence they are used pretty rarely.¨

But MyISAM table types are depeding on the OS file cache.
Since you want to avoid disk reads as much as possible.

And that is where your missing RAM has went.

In your top output below the “cached” figure is the amount of memory used for OS file cache. And as you can see about 6GB is used for this.

This is normal behavior.

top - 13:00:43 up 44 days, 22:21, 1 user, load average: 0.99, 0.97, 0.91
Tasks: 60 total, 1 running, 59 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 0.0% us, 0.0% sy, 0.0% ni, 99.3% id, 0.7% wa, 0.0% hi, 0.0% si
Cpu2 : 98.0% us, 0.7% sy, 0.0% ni, 0.0% id, 1.3% wa, 0.0% hi, 0.0% si
Cpu3 : 0.3% us, 0.0% sy, 0.0% ni, 99.3% id, 0.3% wa, 0.0% hi, 0.0% si
Mem: 8164332k total, 8080884k used, 83448k free, 425556k buffers
Swap: 0k total, 0k used, 0k free, 6571100k cached