MySQL i jego wydajnosc

Pawel Koska pawel w artfan.net
Wto, 16 Maj 2006, 15:14:38 CEST


Witam,

Tak więc ciągle kombinuję z MySQL i nie potrafie dojść z nim do porozumienia.


Mam serwer z 2x Xeon 2.8Ghz z 2GB RAM na którym chodzi też serwer WWW i mały 
pocztowy. Oczywiście chodzi na nim też MySQL.
Do tego jest jeszcze drugi serwer WWW który korzysta z MySQL zainstalowanym na 
pierwszym serwerze.

No i problem jest z tym drugim serwerem. Ma on jeden serwis WWW który bardzo z 
MySQL korzysta. Aktualnie jest tak że często jest komunikat o braku 
połączenia do serwera MySQL. Jest to trochę dziwne, bo jest to problem tylko 
z tego serwisu, inne które działają na tym serwerze nie mają takich błędów.

Czemu tak się dzieje?

Co mogę jeszcze zrobić? Co można zmienić w konfiguracji by to działało 
prawidłowo.

Poniżej przedstawiam to co potrafię wyciągnąć z MySQL i w załączniku mój plik 
konfiguracyjny.

Dzięki za pomoc :-)

Paweł


mysql> show status;
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| Aborted_clients                | 8          |
| Aborted_connects               | 37         |
| Binlog_cache_disk_use          | 0          |
| Binlog_cache_use               | 0          |
| Bytes_received                 | 3106728668 |
| Bytes_sent                     | 1966274624 |
| Com_admin_commands             | 132542     |
| Com_alter_db                   | 0          |
| Com_alter_table                | 0          |
| Com_analyze                    | 0          |
| Com_backup_table               | 0          |
| Com_begin                      | 0          |
| Com_change_db                  | 5544689    |
| Com_change_master              | 0          |
| Com_check                      | 0          |
| Com_checksum                   | 0          |
| Com_commit                     | 0          |
| Com_create_db                  | 0          |
| Com_create_function            | 0          |
| Com_create_index               | 0          |
| Com_create_table               | 0          |
| Com_dealloc_sql                | 0          |
| Com_delete                     | 4807564    |
| Com_delete_multi               | 132588     |
| 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                     | 4767553    |
| 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                    | 18117      |
| 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                     | 7673568    |
| Com_set_option                 | 4921251    |
| Com_show_binlog_events         | 0          |
| Com_show_binlogs               | 7          |
| Com_show_charsets              | 35         |
| Com_show_collations            | 35         |
| Com_show_column_types          | 0          |
| Com_show_create_db             | 0          |
| Com_show_create_table          | 4          |
| Com_show_databases             | 17         |
| Com_show_errors                | 0          |
| Com_show_fields                | 8          |
| Com_show_grants                | 7          |
| Com_show_innodb_status         | 0          |
| Com_show_keys                  | 12         |
| 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           | 0          |
| Com_show_slave_hosts           | 0          |
| Com_show_slave_status          | 0          |
| Com_show_status                | 3          |
| Com_show_storage_engines       | 0          |
| Com_show_tables                | 116        |
| Com_show_variables             | 84         |
| Com_show_warnings              | 0          |
| Com_slave_start                | 0          |
| Com_slave_stop                 | 0          |
| Com_stmt_close                 | 0          |
| Com_stmt_execute               | 0          |
| Com_stmt_prepare               | 0          |
| Com_stmt_reset                 | 0          |
| Com_stmt_send_long_data        | 0          |
| Com_truncate                   | 0          |
| Com_unlock_tables              | 0          |
| Com_update                     | 2601920    |
| Com_update_multi               | 47         |
| Connections                    | 5412806    |
| Created_tmp_disk_tables        | 5          |
| Created_tmp_files              | 681        |
| Created_tmp_tables             | 402646     |
| Delayed_errors                 | 0          |
| Delayed_insert_threads         | 0          |
| Delayed_writes                 | 0          |
| Flush_commands                 | 1          |
| Handler_commit                 | 0          |
| Handler_delete                 | 4774270    |
| Handler_discover               | 0          |
| Handler_read_first             | 3532       |
| Handler_read_key               | 686107679  |
| Handler_read_next              | 1959637720 |
| Handler_read_prev              | 2064598777 |
| Handler_read_rnd               | 77731191   |
| Handler_read_rnd_next          | 2143855045 |
| Handler_rollback               | 0          |
| Handler_update                 | 491919612  |
| Handler_write                  | 120817695  |
| Key_blocks_not_flushed         | 0          |
| Key_blocks_unused              | 0          |
| Key_blocks_used                | 7248       |
| Key_read_requests              | 563529008  |
| Key_reads                      | 155142     |
| Key_write_requests             | 567039     |
| Key_writes                     | 375290     |
| Max_used_connections           | 111        |
| Not_flushed_delayed_rows       | 0          |
| Open_files                     | 385        |
| Open_streams                   | 0          |
| Open_tables                    | 344        |
| Opened_tables                  | 350        |
| Qcache_free_blocks             | 5106       |
| Qcache_free_memory             | 22827424   |
| Qcache_hits                    | 9005184    |
| Qcache_inserts                 | 6710572    |
| Qcache_lowmem_prunes           | 0          |
| Qcache_not_cached              | 963569     |
| Qcache_queries_in_cache        | 8067       |
| Qcache_total_blocks            | 21276      |
| Questions                      | 44889659   |
| Rpl_status                     | NULL       |
| Select_full_join               | 240        |
| Select_full_range_join         | 0          |
| Select_range                   | 95316      |
| Select_range_check             | 0          |
| Select_scan                    | 1728088    |
| Slave_open_temp_tables         | 0          |
| Slave_retried_transactions     | 0          |
| Slave_running                  | OFF        |
| Slow_launch_threads            | 6          |
| Slow_queries                   | 1          |
| Sort_merge_passes              | 374        |
| Sort_range                     | 442595     |
| Sort_rows                      | 493243433  |
| Sort_scan                      | 275230     |
| 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          | 18321961   |
| Table_locks_waited             | 3353550    |
| Threads_cached                 | 0          |
| Threads_connected              | 40         |
| Threads_created                | 5412805    |
| Threads_running                | 15         |
| Uptime                         | 91375      |
+--------------------------------+------------+
186 rows in set (0.03 sec)

mysql> show variables;
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| back_log                        | 50                                |
| basedir                         | /usr/                             |
| binlog_cache_size               | 32768                             |
| bulk_insert_buffer_size         | 8388608                           |
| character_set_client            | latin2                            |
| character_set_connection        | latin2                            |
| character_set_database          | latin1                            |
| character_set_results           | latin2                            |
| character_set_server            | latin1                            |
| character_set_system            | utf8                              |
| character_sets_dir              | /usr/share/mysql/charsets/        |
| collation_connection            | latin2_general_ci                 |
| collation_database              | latin1_swedish_ci                 |
| collation_server                | latin1_swedish_ci                 |
| concurrent_insert               | ON                                |
| connect_timeout                 | 5                                 |
| datadir                         | /var/lib/mysql/mysqldb/db/        |
| 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                    | YES                               |
| 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                       | YES                               |
| have_ndbcluster                 | DISABLED                          |
| have_openssl                    | YES                               |
| have_query_cache                | YES                               |
| have_raid                       | YES                               |
| 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                 | 8388600                           |
| 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                         | OFF                               |
| log_error                       |                                   |
| log_slave_updates               | OFF                               |
| log_slow_queries                | OFF                               |
| log_update                      | 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              | 1048576                           |
| max_binlog_cache_size           | 4294967295                        |
| max_binlog_size                 | 1073741824                        |
| max_connect_errors              | 10                                |
| max_connections                 | 16384                             |
| 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            | 20000                             |
| 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          | DEFAULT                           |
| myisam_repair_threads           | 1                                 |
| myisam_sort_buffer_size         | 8388608                           |
| myisam_stats_method             | nulls_unequal                     |
| ndb_autoincrement_prefetch_sz   | 32                                |
| ndb_force_send                  | ON                                |
| ndb_use_exact_count             | ON                                |
| ndb_use_transactions            | ON                                |
| net_buffer_length               | 16384                             |
| net_read_timeout                | 30                                |
| net_retry_count                 | 10                                |
| net_write_timeout               | 60                                |
| new                             | OFF                               |
| old_passwords                   | OFF                               |
| open_files_limit                | 65535                             |
| pid_file                        | /var/lib/mysql/mysqldb/mysql.pid  |
| port                            | 3306                              |
| preload_buffer_size             | 32768                             |
| protocol_version                | 10                                |
| query_alloc_block_size          | 8192                              |
| query_cache_limit               | 4194304                           |
| query_cache_min_res_unit        | 4096                              |
| query_cache_size                | 33554432                          |
| query_cache_type                | ON                                |
| query_cache_wlock_invalidate    | OFF                               |
| query_prealloc_size             | 8192                              |
| range_alloc_block_size          | 2048                              |
| read_buffer_size                | 131072                            |
| read_only                       | OFF                               |
| read_rnd_buffer_size            | 262144                            |
| relay_log_purge                 | ON                                |
| relay_log_space_limit           | 0                                 |
| rpl_recovery_rank               | 0                                 |
| secure_auth                     | OFF                               |
| server_id                       | 0                                 |
| 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/mysqldb/mysql.sock |
| sort_buffer_size                | 2097144                           |
| sql_mode                        |                                   |
| sql_notes                       | ON                                |
| sql_warnings                    | ON                                |
| storage_engine                  | MyISAM                            |
| sync_binlog                     | 0                                 |
| sync_frm                        | ON                                |
| sync_replication                | 0                                 |
| sync_replication_slave_id       | 0                                 |
| sync_replication_timeout        | 0                                 |
| system_time_zone                | CEST                              |
| table_cache                     | 24570                             |
| table_type                      | MyISAM                            |
| thread_cache_size               | 0                                 |
| thread_stack                    | 196608                            |
| 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.16                            |
| version_comment                 | PLD Linux Distribution MySQL RPM  |
| version_compile_machine         | i686                              |
| version_compile_os              | pld-linux-gnu                     |
| wait_timeout                    | 28800                             |
+---------------------------------+-----------------------------------+
189 rows in set (0.00 sec)


-------------- następna część ---------
; The MySQL server configuration file.
; One can use all long options that the mysqld supports.
; Run mysqld with --help to get a whole list of available options
; $Id: mysqld.conf,v 1.21 2005/11/07 17:30:44 glen Exp $

; This section must be the first!
[mysqld]
datadir	= /var/lib/mysql/mysqldb/db
pid-file = /var/lib/mysql/mysqldb/mysql.pid
port        = 3306
socket = /var/lib/mysql/mysqldb/mysql.sock
user        = mysql

;character-set-server=latin2
;collation-server=latin2_general_ci
;skip-character-set-client-handshake

; set default charset
;default-character-set = latin1

; Use passwords compatible with old 4.0 mysqld
;old-passwords

; Don't use system locking
;skip-locking

; Don't allow connections over the network by default
;skip-networking

; IP address to bind to
bind-address=10.22.2.5

; no dns
;skip-name-resolve

; Emergency option. Use only if you really need this.
;skip-grant-tables

; replication options
;server-id=2

; skip startup of slave
;skip-slave-start

; slave setup
;relay-log=slave-relay-bin
;master-host=master
;master-user=replica
;master-port=3306
;master-password=replica

; master setup
;log-bin

; databases to be included in binlog
;binlog-do-db=db1
; or excluded
;binlog-ignore-db=mysql
;binlog-ignore-db=test

; Don't flush key buffers between writes for any MyISAM
; set this if you have dedicated slave server where writes aren't important
;delay-key-write-for-all-tables

; Log connections and queries. It slows down MySQL so it's disabled by default
;log        = /var/log/mysql/log

; Log all updates.
;log-update = /var/log/mysql/update
; Log some extra information to update log
;log-long-format

; Log all isam changes
;log-isam    = /var/log/mysql/isamlog

; Language
;language   = polish

;
; Internal server variables
;

;set-variable = back_log=5
set-variable = connect_timeout=5
;set-variable = delayed_insert_timeout=300
;set-variable = delayed_insert_limit=100
;set-variable = flush_time=0
;set-variable = join_buffer_size=131072
;set-variable = key_buffer_size=8M
;set-variable = long_query_time=10
;set-variable = max_allowed_packet=1M
set-variable = max_connections=20000
set-variable = max_user_connections=20000
;set-variable = max_connect_errors=10
;set-variable = max_delayed_insert_threads=20
;set-variable = delayed_queue_size=1000
;set-variable = max_join_size=4294967295
;set-variable = max_sort_length=1024
;set-variable = max_write_lock_count=4294967295
;set-variable = net_buffer_length=16384
;set-variable = record_buffer=131072
;set-variable = sort_buffer_size=2M
;set-variable = table_cache=64
;set-variable = tmp_table_size=1M
;set-variable = max_heap_table_size=32M
;set-variable = thread_cache_size=16
;set-variable = thread_stack=65536
; Try number of CPU's*2
set-variable = thread_concurrency=8
set-variable = wait_timeout=7200
set-variable = query_cache_size=32M
set-variable = query_cache_limit=4M

; replication local log
;set-variable = max_relay_log_size=256M
; max size of master binlog files
;set-variable = max_binlog_size=256M


;
; MyISAM tables options
;

; Don't flush key buffers between writes for any MyISAM
;delay-key-write-for-all-tables

; Repair automatically on open if the table wasn't closed properly.
myisam-recover
;
;set-variable = myisam_sort_buffer_size=4M



;
; InnoDB tables options (see MySQL manual)
;
;skip-innodb

;innodb_data_home_dir = /var/lib/mysql/innodb/data
;innodb_data_file_path = 00:10M;01:20M
;set-variable = innodb_mirrored_log_groups=1
;innodb_log_group_home_dir = /var/lib/mysql/innodb/log
;set-variable = innodb_log_files_in_group=3
;set-variable = innodb_log_file_size=30M
;set-variable = innodb_log_buffer_size=8M

;innodb_flush_log_at_trx_commit=1
;innodb_log_arch_dir = /var/lib/mysql/innodb/log
;innodb_log_archive=0
;set-variable = innodb_buffer_pool_size=80M
;set-variable = innodb_additional_mem_pool_size=10M
;set-variable = innodb_file_io_threads=4
;set-variable = innodb_lock_wait_timeout=50


; used for logs rotation
[mysqladmin]
user        = mysql_logrotate
password    =

; vim:syn=dosini




Więcej informacji o liście dyskusyjnej pld-users-pl