Advice pls my.cnf settings for my system

MySQL & MariaDB Other MySQL® Questions
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

[B]Quote:[/B]
+-----------------------------------+------------+ | 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
[B]Quote:[/B]

±--------------------------------±------------------------ ----------+
| 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

[B]Quote:[/B]
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

2
[B]Quote:[/B]

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.

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?