| Advice pls my.cnf settings for my system [message #2005] |
Sat, 06 October 2007 04:10  |
 saddy
Messages: 3
Registered: October 2007
Location: Russia |
Junior Member |
|
|
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
|
With best regars,
Saddy
|
|
|
|
|
| Re: Advice pls my.cnf settings for my system [message #2014 is a reply to message #2009 ] |
Sat, 06 October 2007 23:37  |
|
| 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.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
|
Members
Search
Help
Register
Login
Home
