•  

Announcement

Announcement Module
Collapse
No announcement yet.

Advice pls my.cnf settings for my system

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

  • Advice pls my.cnf settings for my system

    #1
    I have Core2Duo E6420 (dual core) + 4Gb RAM server. Mainly it used for two process - game server any mysql DB for game server. I don't have much expirience in mysql so my mysql settings calls lags in game server. I tried several configs but cant understand what specifically influents on it. (but I saw difference in game with different settings).

    Size of my DB in mysqldump's file is about 260Mb. Half of tables is in INNODB, others in MyISAM. Game proccess always have 3 connections to mysql and several (not important) (2-20) from outside.

    I'd like to mysql not have more than 300-500M RAM and not have 50% CPU..

    Help please to tune config!

    show status
    Quote:

    +-----------------------------------+------------+
    | Variable_name | Value |
    +-----------------------------------+------------+
    | Aborted_clients | 0 |
    | Aborted_connects | 4 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 115 |
    | Bytes_sent | 180 |
    | Com_admin_commands | 0 |
    | Com_alter_db | 0 |
    | Com_alter_event | 0 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_call_procedure | 0 |
    | Com_change_db | 0 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_checksum | 0 |
    | Com_commit | 0 |
    | Com_create_db | 0 |
    | Com_create_event | 0 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_table | 0 |
    | Com_create_user | 0 |
    | Com_dealloc_sql | 0 |
    | Com_delete | 0 |
    | Com_delete_multi | 0 |
    | Com_do | 0 |
    | Com_drop_db | 0 |
    | Com_drop_event | 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 | 1 |
    | 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_event | 0 |
    | Com_show_create_table | 0 |
    | Com_show_databases | 0 |
    | Com_show_engine_logs | 0 |
    | Com_show_engine_mutex | 0 |
    | Com_show_engine_status | 0 |
    | Com_show_errors | 0 |
    | Com_show_events | 0 |
    | Com_show_fields | 0 |
    | Com_show_grants | 0 |
    | Com_show_keys | 0 |
    | Com_show_master_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_plugins | 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 | 0 |
    | 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 | 126 |
    | Created_tmp_disk_tables | 0 |
    | Created_tmp_files | 5 |
    | Created_tmp_tables | 0 |
    | 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 | 0 |
    | Handler_rollback | 0 |
    | Handler_savepoint | 0 |
    | Handler_savepoint_rollback | 0 |
    | Handler_update | 0 |
    | Handler_write | 0 |
    | Innodb_buffer_pool_pages_data | 12903 |
    | Innodb_buffer_pool_pages_dirty | 88 |
    | Innodb_buffer_pool_pages_flushed | 110123 |
    | Innodb_buffer_pool_pages_free | 11395 |
    | Innodb_buffer_pool_pages_latched | 0 |
    | Innodb_buffer_pool_pages_misc | 278 |
    | Innodb_buffer_pool_pages_total | 24576 |
    | Innodb_buffer_pool_read_ahead_rnd | 1 |
    | Innodb_buffer_pool_read_ahead_seq | 157 |
    | Innodb_buffer_pool_read_requests | 66909947 |
    | Innodb_buffer_pool_reads | 6447 |
    | Innodb_buffer_pool_wait_free | 0 |
    | Innodb_buffer_pool_write_requests | 1288893 |
    | Innodb_data_fsyncs | 26630 |
    | Innodb_data_pending_fsyncs | 0 |
    | Innodb_data_pending_reads | 0 |
    | Innodb_data_pending_writes | 0 |
    | Innodb_data_read | 210063360 |
    | Innodb_data_reads | 7486 |
    | Innodb_data_writes | 95299 |
    | Innodb_data_written | 3742815744 |
    | Innodb_dblwr_pages_written | 110123 |
    | Innodb_dblwr_writes | 2362 |
    | Innodb_log_waits | 0 |
    | Innodb_log_write_requests | 285121 |
    | Innodb_log_writes | 6218 |
    | Innodb_os_log_fsyncs | 7599 |
    | Innodb_os_log_pending_fsyncs | 0 |
    | Innodb_os_log_pending_writes | 0 |
    | Innodb_os_log_written | 133597696 |
    | Innodb_page_size | 16384 |
    | Innodb_pages_created | 215 |
    | Innodb_pages_read | 12688 |
    | Innodb_pages_written | 110123 |
    | 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 | 180898 |
    | Innodb_rows_inserted | 179196 |
    | Innodb_rows_read | 299218310 |
    | Innodb_rows_updated | 38186 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 213133 |
    | Key_blocks_used | 1209 |
    | Key_read_requests | 350256 |
    | Key_reads | 1209 |
    | Key_write_requests | 17726 |
    | Key_writes | 15910 |
    | Last_query_cost | 0.000000 |
    | Max_used_connections | 7 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 166 |
    | Open_streams | 0 |
    | Open_table_definitions | 123 |
    | Open_tables | 118 |
    | Opened_files | 316 |
    | Opened_tables | 0 |
    | Prepared_stmt_count | 0 |
    | Qcache_free_blocks | 706 |
    | Qcache_free_memory | 5104056 |
    | Qcache_hits | 34134 |
    | Qcache_inserts | 360250 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 77776 |
    | Qcache_queries_in_cache | 3682 |
    | Qcache_total_blocks | 8158 |
    | Questions | 797430 |
    | Rpl_status | NULL |
    | Select_full_join | 0 |
    | Select_full_range_join | 0 |
    | Select_range | 0 |
    | Select_range_check | 0 |
    | Select_scan | 0 |
    | 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 |
    | Table_locks_immediate | 744811 |
    | Table_locks_waited | 626 |
    | Tc_log_max_pages_used | 0 |
    | Tc_log_page_size | 0 |
    | Tc_log_page_waits | 0 |
    | Threads_cached | 1 |
    | Threads_connected | 6 |
    | Threads_created | 7 |
    | Threads_running | 1 |
    | Uptime | 11994 |
    +-----------------------------------+------------+

    show variables
    Quote:


    +---------------------------------+------------------------- ----------+
    | Variable_name | Value |
    +---------------------------------+------------------------- ----------+
    | auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | autocommit | ON |
    | automatic_sp_privileges | ON |
    | back_log | 50 |
    | basedir | /usr/local/ |
    | big_tables | OFF |
    | binlog_cache_size | 32768 |
    | binlog_format | MIXED |
    | bulk_insert_buffer_size | 8388608 |
    | character_set_client | cp1251 |
    | character_set_connection | cp1251 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | cp1251 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/local/share/mysql/charsets/ |
    | collation_connection | cp1251_bin |
    | collation_database | utf8_general_ci |
    | collation_server | utf8_general_ci |
    | completion_type | 0 |
    | concurrent_insert | 1 |
    | connect_timeout | 5 |
    | datadir | /var/db/mysql/ |
    | date_format | %Y-%m-%d |
    | datetime_format | %Y-%m-%d %H:%i:%s |
    | default_week_format | 0 |
    | delay_key_write | OFF |
    | delayed_insert_limit | 100 |
    | delayed_insert_timeout | 300 |
    | delayed_queue_size | 1000 |
    | div_precision_increment | 4 |
    | engine_condition_pushdown | ON |
    | error_count | 0 |
    | event_scheduler | OFF |
    | expire_logs_days | 0 |
    | flush | OFF |
    | flush_time | 0 |
    | foreign_key_checks | ON |
    | ft_boolean_syntax | + -><()~*:""&| |
    | ft_max_word_len | 84 |
    | ft_min_word_len | 4 |
    | ft_query_expansion_limit | 20 |
    | ft_stopword_file | (built-in) |
    | general_log | OFF |
    | general_log_file | /var/db/mysql/myhost.log |
    | group_concat_max_len | 1024 |
    | have_compress | YES |
    | have_crypt | YES |
    | have_csv | YES |
    | have_dynamic_loading | NO |
    | have_geometry | YES |
    | have_innodb | YES |
    | have_ndbcluster | NO |
    | have_openssl | NO |
    | have_partitioning | YES |
    | have_query_cache | YES |
    | have_rtree_keys | YES |
    | have_ssl | NO |
    | have_symlink | YES |
    | hostname | myhost |
    | identity | 0 |
    | init_connect | |
    | init_file | |
    | init_slave | |
    | innodb_additional_mem_pool_size | 20971520 |
    | innodb_autoextend_increment | 8 |
    | innodb_buffer_pool_size | 402653184 |
    | innodb_checksums | ON |
    | innodb_commit_concurrency | 0 |
    | innodb_concurrency_tickets | 500 |
    | innodb_data_file_path | ibdata1:400M:autoextend |
    | innodb_data_home_dir | /var/db/mysql_innodb/ |
    | innodb_doublewrite | ON |
    | innodb_fast_shutdown | 1 |
    | innodb_file_io_threads | 4 |
    | innodb_file_per_table | ON |
    | innodb_flush_log_at_trx_commit | 0 |
    | innodb_flush_method | O_DIRECT |
    | innodb_force_recovery | 0 |
    | innodb_lock_wait_timeout | 50 |
    | innodb_locks_unsafe_for_binlog | OFF |
    | innodb_log_buffer_size | 6291456 |
    | innodb_log_file_size | 104857600 |
    | innodb_log_files_in_group | 2 |
    | innodb_log_group_home_dir | /var/db/mysql_innodb/ |
    | 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 |
    | insert_id | 0 |
    | interactive_timeout | 28800 |
    | join_buffer_size | 268431360 |
    | keep_files_on_create | OFF |
    | key_buffer_size | 268435456 |
    | key_cache_age_threshold | 300 |
    | key_cache_block_size | 1024 |
    | key_cache_division_limit | 100 |
    | language | /usr/local/share/mysql/english/ |
    | large_files_support | ON |
    | large_page_size | 0 |
    | large_pages | OFF |
    | last_insert_id | 0 |
    | lc_time_names | en_US |
    | license | GPL |
    | local_infile | ON |
    | locked_in_memory | OFF |
    | log | OFF |
    | log_bin | OFF |
    | log_bin_trust_function_creators | OFF |
    | log_bin_trust_routine_creators | OFF |
    | log_error | /var/db/mysql/mysql-err.log |
    | log_output | FILE |
    | log_queries_not_using_indexes | OFF |
    | log_slave_updates | OFF |
    | log_slow_queries | ON |
    | log_warnings | 1 |
    | long_query_time | 3.000000 |
    | low_priority_updates | OFF |
    | lower_case_file_system | OFF |
    | lower_case_table_names | 0 |
    | max_allowed_packet | 33553408 |
    | max_binlog_cache_size | 18446744073709551615 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 999999 |
    | max_connections | 40 |
    | max_delayed_threads | 20 |
    | max_error_count | 64 |
    | max_heap_table_size | 536870912 |
    | max_insert_delayed_threads | 20 |
    | max_join_size | 536870912 |
    | 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 |
    | min_examined_row_limit | 0 |
    | 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 | 8388608 |
    | myisam_stats_method | nulls_unequal |
    | myisam_use_mmap | OFF |
    | net_buffer_length | 16384 |
    | net_read_timeout | 30 |
    | net_retry_count | 1000000 |
    | net_write_timeout | 60 |
    | new | OFF |
    | old | OFF |
    | old_alter_table | OFF |
    | old_passwords | OFF |
    | open_files_limit | 11095 |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | pid_file | /var/db/mysql/myhost.pid |
    | plugin_dir | /usr/local/lib/mysql |
    | port | 3306 |
    | preload_buffer_size | 32768 |
    | protocol_version | 10 |
    | pseudo_thread_id | 124 |
    | query_alloc_block_size | 8192 |
    | query_cache_limit | 4194304 |
    | query_cache_min_res_unit | 4096 |
    | query_cache_size | 23068672 |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 8192 |
    | rand_seed1 | |
    | rand_seed2 | |
    | range_alloc_block_size | 2048 |
    | read_buffer_size | 4190208 |
    | read_only | OFF |
    | read_rnd_buffer_size | 4190208 |
    | relay_log_purge | ON |
    | relay_log_space_limit | 0 |
    | rpl_recovery_rank | 0 |
    | secure_auth | OFF |
    | secure_file_priv | |
    | server_id | 1 |
    | skip_external_locking | ON |
    | skip_networking | OFF |
    | skip_show_database | OFF |
    | slave_compressed_protocol | OFF |
    | slave_load_tmpdir | /var/tmp/ |
    | slave_net_timeout | 3600 |
    | slave_skip_errors | OFF |
    | slave_transaction_retries | 10 |
    | slow_launch_time | 2 |
    | slow_query_log | ON |
    | slow_query_log_file | /var/db/mysql/mysql-slow.log |
    | socket | /tmp/mysql.sock |
    | sort_buffer_size | 4194296 |
    | sql_auto_is_null | ON |
    | sql_big_selects | OFF |
    | sql_big_tables | OFF |
    | sql_buffer_result | OFF |
    | sql_log_bin | ON |
    | sql_log_off | OFF |
    | sql_log_update | ON |
    | sql_low_priority_updates | OFF |
    | sql_max_join_size | 536870912 |
    | sql_mode | |
    | sql_notes | ON |
    | sql_quote_show_create | ON |
    | sql_safe_updates | OFF |
    | sql_select_limit | 18446744073709551615 |
    | sql_slave_skip_counter | |
    | sql_warnings | OFF |
    | ssl_ca | |
    | ssl_capath | |
    | ssl_cert | |
    | ssl_cipher | |
    | ssl_key | |
    | storage_engine | InnoDB |
    | sync_binlog | 0 |
    | sync_frm | ON |
    | system_time_zone | MSD |
    | table_definition_cache | 128 |
    | table_lock_wait_timeout | 50 |
    | table_open_cache | 1024 |
    | table_type | InnoDB |
    | thread_cache_size | 64 |
    | thread_handling | one-thread-per-connection |
    | thread_stack | 131072 |
    | time_format | %H:%i:%s |
    | time_zone | SYSTEM |
    | timed_mutexes | OFF |
    | timestamp | 1191657738 |
    | tmp_table_size | 536870912 |
    | tmpdir | /var/tmp/ |
    | transaction_alloc_block_size | 8192 |
    | transaction_prealloc_size | 4096 |
    | tx_isolation | REPEATABLE-READ |
    | unique_checks | ON |
    | updatable_views_with_limit | YES |
    | version | 5.1.21-beta-log |
    | version_comment | FreeBSD port: mysql-server-5.1.21 |
    | version_compile_machine | amd64 |
    | version_compile_os | portbld-freebsd6.2 |
    | wait_timeout | 28800 |
    | warning_count | 0 |
    +---------------------------------+------------------------- ----------+


    mysqlreport
    Quote:

    MySQL 5.1.21-beta-log uptime 0 2:54:39 Sat Oct 6 11:28:15 2007

    __ Key __________________________________________________ __________ _____
    Buffer used 1.13M of 256.00M %Used: 0.44
    Current 47.81M %Usage: 18.68
    Write hit 11.33%
    Read hit 99.66%

    __ Questions __________________________________________________ _________
    Total 680.74k 65.0/s
    DMS 637.30k 60.8/s %Total: 93.62
    QC Hits 26.75k 2.6/s 3.93
    Com_ 16.60k 1.6/s 2.44
    COM_QUIT 92 0.0/s 0.01
    -Unknown 5 0.0/s 0.00
    Slow 0 0/s 0.00 %DMS: 0.00
    DMS 637.30k 60.8/s 93.62
    SELECT 367.03k 35.0/s 53.92 57.59
    INSERT 163.87k 15.6/s 24.07 25.71
    DELETE 74.45k 7.1/s 10.94 11.68
    UPDATE 31.95k 3.0/s 4.69 5.01
    REPLACE 5 0.0/s 0.00 0.00
    Com_ 16.60k 1.6/s 2.44
    commit 8.24k 0.8/s 1.21
    begin 8.24k 0.8/s 1.21
    change_db 86 0.0/s 0.01

    __ SELECT and Sort __________________________________________________ ___
    Scan 3.70k 0.4/s %SELECT: 1.01
    Range 6 0.0/s 0.00
    Full join 0 0/s 0.00
    Range check 0 0/s 0.00
    Full rng join 0 0/s 0.00
    Sort scan 501 0.0/s
    Sort range 1 0.0/s
    Sort mrg pass 0 0/s

    __ Query Cache __________________________________________________ _______
    Memory usage 16.73M of 22.00M %Used: 76.05
    Block Fragmnt 9.70%
    Hits 26.75k 2.6/s
    Inserts 304.52k 29.1/s
    Insrt:Prune 304.52k:1 29.1/s
    Hit:Insert 0.09:1

    __ Table Locks __________________________________________________ _______
    Waited 497 0.0/s %Total: 0.08
    Immediate 638.24k 60.9/s

    __ Tables __________________________________________________ __________ __
    Open 118 of 1024 %Cache: 11.52
    Opened 124 0.0/s

    __ Connections __________________________________________________ _______
    Max used 4 of 40 %Max: 10.00
    Total 94 0.0/s

    __ Created Temp __________________________________________________ ______
    Disk table 0 0/s
    Table 10 0.0/s
    File 5 0.0/s

    __ Threads __________________________________________________ __________ _
    Running 1 of 4
    Cached 0 of 64 %Hit: 95.74
    Created 4 0.0/s
    Slow 0 0/s

    __ Aborted __________________________________________________ __________ _
    Clients 0 0/s
    Connects 2 0.0/s

    __ Bytes __________________________________________________ __________ ___
    Sent 193.31M 18.4k/s
    Received 97.55M 9.3k/s

    __ InnoDB Buffer Pool __________________________________________________
    Usage 202.38M of 384.00M %Used: 52.70
    Read ratio 0.000
    Pages
    Free 11.62k %Total: 47.30
    Data 12.70k 51.68 %Drty: 0.39
    Misc 250 1.02
    Latched 0 0.00
    Reads 56.10M 5.4k/s
    From file 6.27k 0.6/s 0.01
    Ahead Rnd 1 0.0/s
    Ahead Sql 157 0.0/s
    Writes 1.13M 108.3/s
    Flushes 95.63k 9.1/s
    Wait Free 0 0/s

    __ InnoDB Lock __________________________________________________ _______
    Waits 0 0/s
    Current 0
    Time acquiring
    Total 0 ms
    Average 0 ms
    Max 0 ms

    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
    Reads 7.31k 0.7/s
    Writes 82.66k 7.9/s
    fsync 23.24k 2.2/s
    Pending
    Reads 0
    Writes 0
    fsync 0

    Pages
    Created 190 0.0/s
    Read 12.51k 1.2/s
    Written 95.63k 9.1/s

    Rows
    Deleted 160.50k 15.3/s
    Inserted 158.81k 15.2/s
    Read 251.32M 24.0k/s
    Updated 34.41k 3.3/s
    Tags: None

  • #2
    Quote:


    I'd like to mysql not have more than 300-500M RAM and not have 50% CPU..


    What I can see from your posting it doesn't look like MySQL is using more RAM than that. But the reason for this is that your DB is in fact so small that it doesn't have to.
    The main variables for memory usage are usually:
    innodb_buffer_pool_size
    key_buffer_pool_size

    Then you have per connection sizes:
    sort_buffer_size
    join_buffer_size
    ( which in your case set to a really strange large value which indicates on a poor design of DB)


    As for the CPU there is no solution, because the DB will consume 100% CPU in each thread if it needs to.
    BUT if the DB consumes 100% CPU it usually always indicate a poorly designed DB with lack of indexes etc.

    Optimization of DB and application is the way out instead of trying to limit the resources that MySQL needs.

    Comment


    • #3
      Let's me add some info.
      First, I can't modify DB structure, second - I moved from Win2003 to FreeBSD and got mysql troubles. Earlier, on Win2003, I have no problem with DB.

      So I need settings max optimal to work with this database..
      Can anybody advice specifically values?

      Comment


      • #4
        saddy wrote on Sat, 06 October 2007 07:41

        Let's me add some info.
        First, I can't modify DB structure, second - I moved from Win2003 to FreeBSD and got mysql troubles. Earlier, on Win2003, I have no problem with DB.

        So I need settings max optimal to work with this database..
        Can anybody advice specifically values?


        Almost all mysql settings are really specific for each case. As for db modification... can you add indexes on your tables (they only have impact on performance, not on query results, so your app would not notice these changes)?

        Regarding freebsd problems, it would be great to hear more specific information about your problems.

        Next thing is resource usage... There are some thoughts:

        You have 11395 free innodb buffer pool pages of 24576 total. I'd suggest to reduce innodb_buffer_pool_size down to 256M from your 400M now.

        Your key_buffer_size could be reduced down to 32M from your 256M considering your key buffer usage now (1209 keyblocks used and 213133 blocks free). Again, looking on your key buffer info, I'd guess that you have really small number of indexes in your db, so that is why I'd perform really close look on your queries performance and table indexes your queries use.

        Comment


        • #5
          Thanks a lot for help! You're return faith in my mind (I was crazy that mysql settings not works as it should )

          Seems I found that problem was not in MySQL ) I moved Mysql to another computer and found that game server have almost the same lags.. So problem in FreeBSD 64x and Core2Duo..

          Comment

          Working...
          X