List:General Discussion« Previous MessageNext Message »
From:madunix Date:January 14 2010 5:53pm
Subject:Re: optimize mysql
View as plain text  
its a linux server fedora with 2GB ram, insert mostly used through the
update, no load or on  cpu during that time

mysql> show status;

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| Aborted_clients            | 16         |

| Aborted_connects           | 7          |

| Binlog_cache_disk_use      | 1          |

| Binlog_cache_use           | 1          |

| Bytes_received             | 327786682  |

| Bytes_sent                 | 2078650970 |

| Com_admin_commands         | 9725       |

| Com_alter_db               | 0          |

| Com_alter_table            | 0          |

| Com_analyze                | 0          |

| Com_backup_table           | 0          |

| Com_begin                  | 0          |

| Com_change_db              | 65467      |

| Com_change_master          | 0          |

| Com_check                  | 0          |

| Com_checksum               | 0          |

| Com_commit                 | 1          |

| Com_create_db              | 0          |

| Com_create_function        | 0          |

| Com_create_index           | 0          |

| Com_create_table           | 0          |

| Com_dealloc_sql            | 0          |

| Com_delete                 | 41271      |

| Com_delete_multi           | 0          |

| Com_do                     | 0          |

| Com_drop_db                | 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                 | 42096      |

| Com_insert_select          | 36         |

| Com_kill                   | 0          |

| Com_load                   | 0          |

| Com_load_master_data       | 0          |

| Com_load_master_table      | 0          |

| Com_lock_tables            | 6          |

| 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                 | 2597515    |

| Com_set_option             | 625        |

| Com_show_binlog_events     | 0          |

| Com_show_binlogs           | 0          |

| Com_show_charsets          | 1          |

| Com_show_collations        | 0          |

| Com_show_column_types      | 0          |

| Com_show_create_db         | 9          |

| Com_show_create_table      | 818        |

| Com_show_databases         | 3          |

| Com_show_errors            | 0          |

| Com_show_fields            | 482        |

| Com_show_grants            | 0          |

| Com_show_innodb_status     | 8386       |

| Com_show_keys              | 0          |

| Com_show_logs              | 0          |

| Com_show_master_status     | 3          |

| Com_show_new_master        | 0          |

| Com_show_open_tables       | 0          |

| Com_show_privileges        | 0          |

| Com_show_processlist       | 2          |

| Com_show_slave_hosts       | 3          |

| Com_show_slave_status      | 0          |

| Com_show_status            | 8385       |

| Com_show_storage_engines   | 0          |

| Com_show_tables            | 493        |

| Com_show_variables         | 19         |

| Com_show_warnings          | 0          |

| Com_slave_start            | 0          |

| Com_slave_stop             | 0          |

| Com_stmt_prepare           | 0          |

| Com_stmt_execute           | 0          |

| Com_stmt_send_long_data    | 0          |

| Com_stmt_reset             | 0          |

| Com_stmt_close             | 0          |

| Com_truncate               | 0          |

| Com_unlock_tables          | 6          |

| Com_update                 | 30723      |

| Com_update_multi           | 0          |

| Connections                | 65423      |

| Created_tmp_disk_tables    | 2027       |

| Created_tmp_files          | 431        |

| Created_tmp_tables         | 647863     |

| Delayed_errors             | 0          |

| Delayed_insert_threads     | 0          |

| Delayed_writes             | 0          |

| Flush_commands             | 1          |

| Handler_commit             | 4          |

| Handler_delete             | 12585      |

| Handler_discover           | 0          |

| Handler_read_first         | 6257964    |

| Handler_read_key           | 36366165   |

| Handler_read_next          | 574201726  |

| Handler_read_prev          | 47151      |

| Handler_read_rnd           | 5037081    |

| Handler_read_rnd_next      | 2842816956 |

| Handler_rollback           | 53758      |

| Handler_update             | 309674     |

| Handler_write              | 311112976  |

| Key_blocks_not_flushed     | 0          |

| Key_blocks_unused          | 10532      |

| Key_blocks_used            | 4170       |

| Key_read_requests          | 5615280    |

| Key_reads                  | 6087       |

| Key_write_requests         | 68041      |

| Key_writes                 | 47094      |

| Max_used_connections       | 25         |

| Not_flushed_delayed_rows   | 0          |

| Open_files                 | 685        |

| Open_streams               | 0          |

| Open_tables                | 512        |

| Opened_tables              | 762        |

| Qcache_free_blocks         | 0          |

| Qcache_free_memory         | 0          |

| Qcache_hits                | 0          |

| Qcache_inserts             | 0          |

| Qcache_lowmem_prunes       | 0          |

| Qcache_not_cached          | 0          |

| Qcache_queries_in_cache    | 0          |

| Qcache_total_blocks        | 0          |

| Questions                  | 2864857    |

| Rpl_status                 | NULL       |

| Select_full_join           | 4241       |

| Select_full_range_join     | 0          |

| Select_range               | 3206       |

| Select_range_check         | 0          |

| Select_scan                | 1331873    |

| Slave_open_temp_tables     | 0          |

| Slave_running              | OFF        |

| Slave_retried_transactions | 0          |

| Slow_launch_threads        | 0          |

| Slow_queries               | 51         |

| Sort_merge_passes          | 0          |

| Sort_range                 | 130473     |

| Sort_rows                  | 5904253    |

| Sort_scan                  | 788547     |

| Table_locks_immediate      | 2946519    |

| Table_locks_waited         | 236        |

| Threads_cached             | 0          |

| Threads_connected          | 1          |

| Threads_created            | 65422      |

| Threads_running            | 1          |

| Uptime                     | 84823      |

+----------------------------+------------+

162 rows in set (0.00 sec)



mysql>





mysql> show variables;

+---------------------------------+------------------------------------------+

| Variable_name                   | Value                                    |

+---------------------------------+------------------------------------------+

| 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_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                        |

| concurrent_insert               | ON                                       |

| connect_timeout                 | 5                                        |

| datadir                         | /var/lib/mysql/                          |

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

| 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                    | NO                                       |

| have_bdb                        | NO                                       |

| have_blackhole_engine           | NO                                       |

| have_compress                   | YES                                      |

| have_crypt                      | YES                                      |

| have_csv                        | NO                                       |

| have_example_engine             | NO                                       |

| have_geometry                   | YES                                      |

| have_innodb                     | YES                                      |

| have_isam                       | NO                                       |

| have_ndbcluster                 | NO                                       |

| have_openssl                    | NO                                       |

| 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_data_file_path           | ibdata1:10M:autoextend                   |

| innodb_data_home_dir            |                                          |

| innodb_fast_shutdown            | ON                                       |

| 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_table_locks              | ON                                       |

| innodb_thread_concurrency       | 8                                        |

| interactive_timeout             | 28800                                    |

| join_buffer_size                | 131072                                   |

| key_buffer_size                 | 16777216                                 |

| key_cache_age_threshold         | 300                                      |

| key_cache_block_size            | 1024                                     |

| key_cache_division_limit        | 100                                      |

| language                        | /usr/share/mysql/english/                |

| large_files_support             | ON                                       |

| license                         | GPL                                      |

| local_infile                    | ON                                       |

| locked_in_memory                | OFF                                      |

| log                             | OFF                                      |

| log_bin                         | ON                                       |

| log_error                       |                                          |

| log_slave_updates               | OFF                                      |

| log_slow_queries                | ON                                       |

| log_update                      | OFF                                      |

| log_warnings                    | 1                                        |

| long_query_time                 | 4                                        |

| low_priority_updates            | OFF                                      |

| lower_case_file_system          | OFF                                      |

| lower_case_table_names          | 0                                        |

| max_allowed_packet              | 1047552                                  |

| max_binlog_cache_size           | 4294967295                               |

| max_binlog_size                 | 1073741824                               |

| max_connect_errors              | 10                                       |

| max_connections                 | 100                                      |

| max_delayed_threads             | 20                                       |

| max_error_count                 | 64                                       |

| max_heap_table_size             | 16777216                                 |

| max_insert_delayed_threads      | 20                                       |

| max_join_size                   | 4294967295                               |

| max_length_for_sort_data        | 1024                                     |

| max_relay_log_size              | 0                                        |

| max_seeks_for_key               | 4294967295                               |

| max_sort_length                 | 1024                                     |

| max_tmp_tables                  | 32                                       |

| max_user_connections            | 0                                        |

| max_write_lock_count            | 4294967295                               |

| myisam_data_pointer_size        | 4                                        |

| myisam_max_extra_sort_file_size | 2147483648                               |

| myisam_max_sort_file_size       | 2147483647                               |

| myisam_recover_options          | OFF                                      |

| myisam_repair_threads           | 1                                        |

| myisam_sort_buffer_size         | 8388608                                  |

| net_buffer_length               | 8192                                     |

| net_read_timeout                | 30                                       |

| net_retry_count                 | 10                                       |

| net_write_timeout               | 60                                       |

| new                             | OFF                                      |

| old_passwords                   | OFF                                      |

| open_files_limit                | 1134                                     |

| pid_file                        | /var/lib/mysql/com.pid            |

| port                            | 3306                                     |

| preload_buffer_size             | 32768                                    |

| protocol_version                | 10                                       |

| query_alloc_block_size          | 8192                                     |

| query_cache_limit               | 1048576                                  |

| query_cache_min_res_unit        | 4096                                     |

| query_cache_size                | 0                                        |

| query_cache_type                | ON                                       |

| query_cache_wlock_invalidate    | OFF                                      |

| query_prealloc_size             | 8192                                     |

| range_alloc_block_size          | 2048                                     |

| read_buffer_size                | 258048                                   |

| read_only                       | OFF                                      |

| read_rnd_buffer_size            | 520192                                   |

| 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_net_timeout               | 3600                                     |

| slave_transaction_retries       | 0                                        |

| slow_launch_time                | 2                                        |

| socket                          | /var/lib/mysql/mysql.sock                |

| sort_buffer_size                | 524280                                   |

| sql_mode                        |                                          |

| storage_engine                  | MyISAM                                   |

| sql_notes                       | OFF                                      |

| sql_warnings                    | OFF                                      |

| sync_binlog                     | 0                                        |

| sync_replication                | 0                                        |

| sync_replication_slave_id       | 0                                        |

| sync_replication_timeout        | 0                                        |

| sync_frm                        | ON                                       |

| system_time_zone                | EET                                      |

| table_cache                     | 512                                      |

| table_type                      | MyISAM                                   |

| thread_cache_size               | 0                                        |

| thread_stack                    | 126976                                   |

| time_format                     | %H:%i:%s                                 |

| time_zone                       | SYSTEM                                   |

| tmp_table_size                  | 33554432                                 |

| tmpdir                          |                                          |

| transaction_alloc_block_size    | 8192                                     |

| transaction_prealloc_size       | 4096                                     |

| tx_isolation                    | REPEATABLE-READ                          |

| version                         | 4.1.14-standard-log                      |

| version_comment                 | MySQL Community Edition - Standard (GPL) |

| version_compile_machine         | i686                                     |

| version_compile_os              | pc-linux-gnu                             |

| wait_timeout                    | 28800                                    |

+---------------------------------+------------------------------------------+

184 rows in set (0.00 sec)



mysql>


[root@/]# free -m

             total       used       free     shared    buffers     cached

Mem:          2026       1796        229          0        126       1362

-/+ buffers/cache:        307       1718

Swap:         4000          0       4000

[root /]#






# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# 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          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# 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=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> 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     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <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 = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1: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 = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#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 = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

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

[mysqlhotcopy]
interactive-timeout
Thread
optimize mysqlmadunix12 Jan
  • Re: optimize mysqlmos12 Jan
    • Re: optimize mysqlmadunix14 Jan