Mysql 5 wer kann bei der optimierung helfen...

MySQL, PostgreSQL, SQLite
greenrover
Posts: 203
Joined: 2004-12-17 19:34

Mysql 5 wer kann bei der optimierung helfen...

Post by greenrover » 2006-04-26 18:32

Code: Select all

mysql> SHOW STATUS;
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 4        |
| Aborted_connects                  | 4        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 91       |
| Bytes_sent                        | 76       |
| Com_admin_commands                | 0        |
| Com_alter_db                      | 0        |
| Com_alter_table                   | 0        |
| Com_analyze                       | 0        |
| Com_backup_table                  | 0        |
| Com_begin                         | 0        |
| Com_change_db                     | 0        |
| 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                        | 0        |
| 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                        | 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                        | 0        |
| 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_table             | 0        |
| Com_show_databases                | 0        |
| Com_show_errors                   | 0        |
| Com_show_fields                   | 0        |
| Com_show_grants                   | 0        |
| Com_show_innodb_status            | 0        |
| Com_show_keys                     | 0        |
| 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                   | 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                       | 313824   |
| Created_tmp_disk_tables           | 0        |
| Created_tmp_files                 | 0        |
| Created_tmp_tables                | 1        |
| Delayed_errors                    | 3        |
| Delayed_insert_threads            | 0        |
| Delayed_writes                    | 25       |
| 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                     | 130      |
| Innodb_buffer_pool_pages_data     | 0        |
| Innodb_buffer_pool_pages_dirty    | 0        |
| Innodb_buffer_pool_pages_flushed  | 0        |
| Innodb_buffer_pool_pages_free     | 0        |
| Innodb_buffer_pool_pages_latched  | 0        |
| Innodb_buffer_pool_pages_misc     | 0        |
| Innodb_buffer_pool_pages_total    | 0        |
| Innodb_buffer_pool_read_ahead_rnd | 0        |
| Innodb_buffer_pool_read_ahead_seq | 0        |
| Innodb_buffer_pool_read_requests  | 0        |
| Innodb_buffer_pool_reads          | 0        |
| Innodb_buffer_pool_wait_free      | 0        |
| Innodb_buffer_pool_write_requests | 0        |
| Innodb_data_fsyncs                | 0        |
| Innodb_data_pending_fsyncs        | 0        |
| Innodb_data_pending_reads         | 0        |
| Innodb_data_pending_writes        | 0        |
| Innodb_data_read                  | 0        |
| Innodb_data_reads                 | 0        |
| Innodb_data_writes                | 0        |
| Innodb_data_written               | 0        |
| Innodb_dblwr_pages_written        | 0        |
| Innodb_dblwr_writes               | 0        |
| Innodb_log_waits                  | 0        |
| Innodb_log_write_requests         | 0        |
| Innodb_log_writes                 | 0        |
| Innodb_os_log_fsyncs              | 0        |
| Innodb_os_log_pending_fsyncs      | 0        |
| Innodb_os_log_pending_writes      | 0        |
| Innodb_os_log_written             | 0        |
| Innodb_page_size                  | 0        |
| Innodb_pages_created              | 0        |
| Innodb_pages_read                 | 0        |
| Innodb_pages_written              | 0        |
| 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               | 0        |
| Innodb_rows_inserted              | 0        |
| Innodb_rows_read                  | 0        |
| Innodb_rows_updated               | 0        |
| Key_blocks_not_flushed            | 0        |
| Key_blocks_unused                 | 450833   |
| Key_blocks_used                   | 2124     |
| Key_read_requests                 | 4805240  |
| Key_reads                         | 2124     |
| Key_write_requests                | 9168     |
| Key_writes                        | 8704     |
| Last_query_cost                   | 0.000000 |
| Max_used_connections              | 73       |
| Not_flushed_delayed_rows          | 0        |
| Open_files                        | 936      |
| Open_streams                      | 0        |
| Open_tables                       | 669      |
| Opened_tables                     | 0        |
| Qcache_free_blocks                | 1979     |
| Qcache_free_memory                | 6398032  |
| Qcache_hits                       | 1108224  |
| Qcache_inserts                    | 161644   |
| Qcache_lowmem_prunes              | 17893    |
| Qcache_not_cached                 | 229324   |
| Qcache_queries_in_cache           | 4936     |
| Qcache_total_blocks               | 12037    |
| Questions                         | 2391641  |
| Rpl_status                        | NULL     |
| Select_full_join                  | 0        |
| Select_full_range_join            | 0        |
| Select_range                      | 0        |
| Select_range_check                | 0        |
| Select_scan                       | 1        |
| Slave_open_temp_tables            | 0        |
| Slave_retried_transactions        | 0        |
| Slave_running                     | OFF      |
| Slow_launch_threads               | 4        |
| Slow_queries                      | 0        |
| Sort_merge_passes                 | 0        |
| Sort_range                        | 0        |
| Sort_rows                         | 0        |
| Sort_scan                         | 0        |
| 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             | 594667   |
| Table_locks_waited                | 21199    |
| Tc_log_max_pages_used             | 0        |
| Tc_log_page_size                  | 0        |
| Tc_log_page_waits                 | 0        |
| Threads_cached                    | 4        |
| Threads_connected                 | 5        |
| Threads_created                   | 9226     |
| Threads_running                   | 1        |
| Uptime                            | 9597     |
+-----------------------------------+----------+
245 rows in set (0.02 sec)

Code: Select all

mysql> SHOW VARIABLES;
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| auto_increment_increment        | 1                                |
| auto_increment_offset           | 1                                |
| automatic_sp_privileges         | ON                               |
| back_log                        | 50                               |
| basedir                         | /opt/lampp/                      |
| 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              | /opt/lampp/share/mysql/charsets/ |
| collation_connection            | latin1_swedish_ci                |
| collation_database              | latin1_swedish_ci                |
| collation_server                | latin1_swedish_ci                |
| completion_type                 | 0                                |
| concurrent_insert               | 1                                |
| connect_timeout                 | 5                                |
| datadir                         | /opt/lampp/var/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                             |
| div_precision_increment         | 4                                |
| engine_condition_pushdown       | OFF                              |
| 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_federated_engine           | YES                              |
| have_geometry                   | YES                              |
| have_innodb                     | DISABLED                         |
| have_isam                       | NO                               |
| have_ndbcluster                 | NO                               |
| have_openssl                    | DISABLED                         |
| 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 | 2097152                          |
| innodb_autoextend_increment     | 8                                |
| innodb_buffer_pool_awe_mem_mb   | 0                                |
| innodb_buffer_pool_size         | 16777216                         |
| innodb_checksums                | ON                               |
| innodb_commit_concurrency       | 0                                |
| innodb_concurrency_tickets      | 500                              |
| innodb_data_file_path           | ibdata1:10M:autoextend           |
| innodb_data_home_dir            | /opt/lampp/var/mysql/            |
| innodb_doublewrite              | ON                               |
| innodb_fast_shutdown            | 1                                |
| 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             | /opt/lampp/var/mysql/            |
| innodb_log_archive              | OFF                              |
| innodb_log_buffer_size          | 8388608                          |
| innodb_log_file_size            | 5242880                          |
| innodb_log_files_in_group       | 2                                |
| innodb_log_group_home_dir       | /opt/lampp/var/mysql/            |
| innodb_max_dirty_pages_pct      | 90                               |
| innodb_max_purge_lag            | 0                                |
| innodb_mirrored_log_groups      | 1                                |
| innodb_open_files               | 300                              |
| innodb_support_xa               | ON                               |
| innodb_sync_spin_loops          | 20                               |
| innodb_table_locks              | ON                               |
| innodb_thread_concurrency       | 20                               |
| innodb_thread_sleep_delay       | 10000                            |
| interactive_timeout             | 28800                            |
| join_buffer_size                | 131072                           |
| key_buffer_size                 | 524288000                        |
| key_cache_age_threshold         | 300                              |
| key_cache_block_size            | 1024                             |
| key_cache_division_limit        | 100                              |
| language                        | /opt/lampp/share/mysql/english/  |
| large_files_support             | ON                               |
| large_page_size                 | 0                                |
| large_pages                     | OFF                              |
| license                         | GPL                              |
| local_infile                    | OFF                              |
| locked_in_memory                | OFF                              |
| log                             | OFF                              |
| log_bin                         | ON                               |
| log_bin_trust_function_creators | OFF                              |
| log_error                       |                                  |
| log_slave_updates               | OFF                              |
| log_slow_queries                | ON                               |
| log_warnings                    | 1                                |
| long_query_time                 | 1                                |
| low_priority_updates            | OFF                              |
| lower_case_file_system          | OFF                              |
| lower_case_table_names          | 0                                |
| max_allowed_packet              | 16776192                         |
| max_binlog_cache_size           | 4294967295                       |
| max_binlog_size                 | 1073741824                       |
| max_connect_errors              | 10                               |
| max_connections                 | 2500                             |
| 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               | 1000                             |
| max_sort_length                 | 1024                             |
| max_sp_recursion_depth          | 0                                |
| max_tmp_tables                  | 32                               |
| max_user_connections            | 0                                |
| max_write_lock_count            | 4294967295                       |
| multi_range_count               | 256                              |
| myisam_data_pointer_size        | 6                                |
| myisam_max_sort_file_size       | 2147483647                       |
| myisam_recover_options          | OFF                              |
| myisam_repair_threads           | 1                                |
| myisam_sort_buffer_size         | 8388608                          |
| myisam_stats_method             | nulls_unequal                    |
| net_buffer_length               | 8192                             |
| net_read_timeout                | 30                               |
| net_retry_count                 | 10                               |
| net_write_timeout               | 60                               |
| new                             | OFF                              |
| old_passwords                   | OFF                              |
| open_files_limit                | 65535                            |
| optimizer_prune_level           | 1                                |
| optimizer_search_depth          | 62                               |
| pid_file                        | /opt/lampp/var/mysql/pluto14.pid |
| port                            | 0                                |
| 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                | 16777216                         |
| query_cache_type                | ON                               |
| query_cache_wlock_invalidate    | OFF                              |
| query_prealloc_size             | 8192                             |
| range_alloc_block_size          | 2048                             |
| read_buffer_size                | 1044480                          |
| read_only                       | OFF                              |
| read_rnd_buffer_size            | 6287360                          |
| 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                 | ON                               |
| skip_show_database              | OFF                              |
| slave_compressed_protocol       | OFF                              |
| slave_load_tmpdir               | /tmp/ramtmp/                     |
| slave_net_timeout               | 3600                             |
| slave_skip_errors               | OFF                              |
| slave_transaction_retries       | 10                               |
| slow_launch_time                | 2                                |
| socket                          | /opt/lampp/var/mysql/mysql.sock  |
| sort_buffer_size                | 15728632                         |
| sql_mode                        |                                  |
| sql_notes                       | OFF                              |
| sql_warnings                    | OFF                              |
| storage_engine                  | MyISAM                           |
| sync_binlog                     | 0                                |
| sync_frm                        | ON                               |
| sync_replication                | 0                                |
| sync_replication_slave_id       | 0                                |
| sync_replication_timeout        | 10                               |
| system_time_zone                | CEST                             |
| table_cache                     | 31512                            |
| table_lock_wait_timeout         | 50                               |
| table_type                      | MyISAM                           |
| thread_cache_size               | 8                                |
| thread_stack                    | 131072                           |
| time_format                     | %H:%i:%s                         |
| time_zone                       | SYSTEM                           |
| timed_mutexes                   | OFF                              |
| tmp_table_size                  | 67108864                         |
| tmpdir                          | /tmp/ramtmp                      |
| transaction_alloc_block_size    | 8192                             |
| transaction_prealloc_size       | 4096                             |
| tx_isolation                    | REPEATABLE-READ                  |
| updatable_views_with_limit      | YES                              |
| version                         | 5.0.18-log                       |
| version_comment                 | Source distribution              |
| version_compile_machine         | i686                             |
| version_compile_os              | pc-linux-gnu                     |
| wait_timeout                    | 28800                            |
+---------------------------------+----------------------------------+
210 rows in set (0.00 sec)

Code: Select all

cat my.cnf
# 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 /opt/lampp/var/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          = /opt/lampp/var/mysql/mysql.sock


# Here follows entries for some specific programs

# The MySQL server
[mysqld]
# commented out by lampp security
#port           = 3306
port = 0
socket          = /opt/lampp/var/mysql/mysql.sock
log-update      = /opt/lampp/logs/mysql-update.log
skip-locking
skip-external-locking
skip-networking
local-infile = 0

key_buffer = 500M
key_buffer_size = 500M

sort_buffer_size = 15M
read_buffer_size = 1M
net_buffer_length = 8K
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 8M
max_allowed_packet = 16M

net_buffer_length = 8K
thread_stack = 128K
thread_cache = 8
table_cache = 10M
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 1M
thread_concurrency = 2

max-seeks-for-key       = 1000

# Zusatz Henning
set-variable            = max_connections=2500
long_query_time         = 1
log-slow-queries        = /opt/lampp/logs/slow-query.log


tmp_table_size          = 64M



server-id       = 1


# 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=mysql-bin


# Point the following paths to different dedicated disks
tmpdir          = /tmp/ramtmp
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Comment the following if you are using InnoDB tables
skip-innodb
innodb_data_home_dir = /opt/lampp/var/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/lampp/var/mysql/
innodb_log_arch_dir = /opt/lampp/var/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 = 64M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

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

[mysqlhotcopy]
interactive-timeout

Code: Select all

tail -n 200 slow-query.log
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 1089
use usr_web204_1;
SELECT * FROM news_images WHERE news_id='20740' ORDER BY id LIMIT 1;
# Time: 060426 17:08:27
# User@Host: web204[web204] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 4033
SELECT * FROM ticker_COMMENTS WHERE races_id='1332' LIMIT 1;
# Time: 060426 17:08:29
# User@Host: root[root] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 4560  Rows_examined: 4560
use usr_web6006_2;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `phpbb_search_wordlist`;
# Time: 060426 17:10:45
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use usr_web204-06_1;
UPDATE ticker_VISITORS SET lastRequest = "1146064243",msgToMovie = "" WHERE vid = "AZW3bpJcqxg1NSSDjCmHCl1146052890";
# Time: 060426 17:11:55
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
SELECT `poster`, `post_date`, `post_ref`, `post_id` FROM `forum_posts` WHERE `post_theme`="Starkes Team ?" ORDER BY `post_date` DESC LIMIT 1;
# Time: 060426 17:13:36
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
SELECT `poster`, `post_date`, `post_ref`, `post_id` FROM `forum_posts` WHERE `post_theme`="Krieteriumtraining" ORDER BY `post_date` DESC LIMIT 1;
# Time: 060426 17:14:51
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
SELECT `poster`, `post_date`, `post_ref`, `post_id` FROM `forum_posts` WHERE `post_theme`="LiVE-CUP Regelwerk" ORDER BY `post_date` DESC LIMIT 1;
# Time: 060426 17:15:55
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5238
SELECT COUNT(`time`) FROM `besucherzaehler` WHERE `time` BETWEEN "1146002400" AND "1146088800";
# Time: 060426 17:24:08
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
SELECT post_date, poster FROM forum_posts WHERE post_ref='643' OR post_id='643' ORDER BY post_date DESC LIMIT 1;
# Time: 060426 17:24:09
# User@Host: web204[web204] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 5833  Rows_examined: 11666
use usr_web204_1;
SELECT * FROM forum_posts ORDER BY post_date DESC;
# Time: 060426 17:24:11
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
use usr_web204-06_1;
SELECT post_date, poster FROM forum_posts WHERE post_ref='1459' OR post_id='1459' ORDER BY post_date DESC LIMIT 1;
# Time: 060426 17:27:17
# User@Host: web19[web19] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 8  Rows_examined: 3649
use usr_web19_1;
SELECT `title`, `url`, `back_clicks` FROM `LINKER_LINK` GROUP BY `url` ORDER BY `last_back_click` DESC LIMIT 0 , 8;
# Time: 060426 17:27:43
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 8  Rows_examined: 30
use usr_web204-06_1;
SELECT DISTINCT `post_theme` FROM `forum_posts`  WHERE 1  ORDER BY `post_id` DESC LIMIT 8;
# Time: 060426 17:27:45
# User@Host: web204[web204] @ localhost []
# Query_time: 3  Lock_time: 1  Rows_sent: 0  Rows_examined: 0
use usr_web204_1;
SELECT `id`, `parent`, `title`, `url`, `target` FROM `06_kategorien` WHERE `parent`="242" AND `hide`='0' ORDER BY `orders` ASC;
# Time: 060426 17:27:48
# User@Host: web204[web204] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
DELETE FROM news_news WHERE ablaufdatum<'1146065265' AND ablaufdatum!='-1';
# Time: 060426 17:28:08
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
use usr_web204-06_1;
SELECT `poster`, `post_date`, `post_ref`, `post_id` FROM `forum_posts` WHERE `post_theme`="Starkes Team ?" ORDER BY `post_date` DESC LIMIT 1;
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5399
SELECT COUNT(`time`) FROM `besucherzaehler` WHERE `time` BETWEEN "1146002400" AND "1146088800";
# Time: 060426 17:28:15
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
SELECT post_date, poster FROM forum_posts WHERE post_ref='4741' OR post_id='4741' ORDER BY post_date DESC LIMIT 1;
# Time: 060426 17:28:20
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
SELECT `poster`, `post_date`, `post_ref`, `post_id` FROM `forum_posts` WHERE `post_theme`="Krieteriumtraining" ORDER BY `post_date` DESC LIMIT 1;
# Time: 060426 17:42:58
# User@Host: web204[web204] @ localhost []
# Query_time: 14  Lock_time: 0  Rows_sent: 0  Rows_examined: 2276
use usr_web204_1;
SELECT * FROM news_news WHERE (title REGEXP 'Startzeiten|Startzeiten' OR main_text REGEXP 'Startzeiten|Startzeiten' OR input_1 REGEXP 'Startzeiten|Startzeiten' OR input_2 REGEXP 'Startzeiten|Startzeiten' OR input_3 REGEXP 'Startzeiten|Startzeiten' OR input_4 REGEXP 'Startzeiten|Startzeiten') AND kat_id='72' ORDER BY date DESC;
# Time: 060426 17:43:01
# User@Host: web204[web204] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 15528
SELECT t.* FROM news_ticker t, news_news n WHERE t.text REGEXP 'Startzeiten|Startzeiten' AND t.news_id=n.id AND n.kat_id='72' ORDER BY t.date DESC;
# Time: 060426 17:45:49
# User@Host: web204[web204] @ localhost []
# Query_time: 12  Lock_time: 0  Rows_sent: 145  Rows_examined: 2421
SELECT * FROM news_news WHERE (title REGEXP 'Startliste|Startliste' OR main_text REGEXP 'Startliste|Startliste' OR input_1 REGEXP 'Startliste|Startliste' OR input_2 REGEXP 'Startliste|Startliste' OR input_3 REGEXP 'Startliste|Startliste' OR input_4 REGEXP 'Startliste|Startliste') ORDER BY date DESC;
# Time: 060426 17:46:25
# User@Host: web204[web204] @ localhost []
# Query_time: 6  Lock_time: 0  Rows_sent: 10  Rows_examined: 2286
SELECT * FROM news_news WHERE (title REGEXP '2006|2006' OR main_text REGEXP '2006|2006' OR input_1 REGEXP '2006|2006' OR input_2 REGEXP '2006|2006' OR input_3 REGEXP '2006|2006' OR input_4 REGEXP '2006|2006') AND kat_id='67' ORDER BY date DESC;
# Time: 060426 17:46:29
# User@Host: web204[web204] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 15526
SELECT t.* FROM news_ticker t, news_news n WHERE t.text REGEXP '2006|2006' AND t.news_id=n.id AND n.kat_id='67' ORDER BY t.date DESC;
# Time: 060426 17:46:35
# User@Host: web204[web204] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 10  Rows_examined: 15534
SELECT * FROM news_ticker WHERE text REGEXP 'Startliste|Startliste' ORDER BY date DESC;
# Time: 060426 17:47:16
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use usr_web204-06_1;
UPDATE ticker_VISITORS SET lastRequest = "1146066434",seq = "237" WHERE vid = "QzDYWG4ruhi5UignPLKKFV1146056369";
# Time: 060426 17:48:19
# User@Host: livebox[livebox] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use livebox;
SELECT messageid, boxuserid, username, homepage, message, messagedate, ip FROM shoutbox_message WHERE shoutboxid='2' ORDER BY messageid DESC LIMIT 30;
# Time: 060426 17:48:43
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 8  Lock_time: 0  Rows_sent: 59  Rows_examined: 810
use usr_web204-06_1;
SELECT * FROM news_news WHERE (title REGEXP 'cannondale bad boy|cannondale|bad|boy' OR main_text REGEXP 'cannondale bad boy|cannondale|bad|boy' OR input_1 REGEXP 'cannondale bad boy|cannondale|bad|boy' OR input_2 REGEXP 'cannondale bad boy|cannondale|bad|boy' OR input_3 REGEXP 'cannondale bad boy|cannondale|bad|boy' OR input_4 REGEXP 'cannondale bad boy|cannondale|bad|boy') ORDER BY date DESC;
# Time: 060426 17:48:45
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 5  Rows_examined: 3860
SELECT * FROM news_ticker WHERE text REGEXP 'cannondale bad boy|cannondale|bad|boy' ORDER BY date DESC;
# Time: 060426 17:49:46
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
SELECT COUNT(`time`) FROM `besucherzaehler`;
# Time: 060426 17:50:19
# User@Host: web204[web204] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use usr_web204_1;
SELECT `id`, `parent`, `title`, `url`, `target` FROM `06_kategorien` WHERE `parent`="183" AND `hide`='0' ORDER BY `orders` ASC;
# Time: 060426 17:53:15
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use usr_web204-06_1;
UPDATE ticker_VISITORS SET lastRequest = "1146066793" WHERE vid = "3W5g6ISQpcYcW3UiPKCaT01146052451";
# Time: 060426 17:53:53
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
UPDATE ticker_VISITORS SET lastRequest = "1146066831",seq = "250" WHERE vid = "MvbYsi2S4LYrrZIMJ2mmab1146060215";
# Time: 060426 17:54:33
# User@Host: web204[web204] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 2277
use usr_web204_1;
SELECT id, title FROM news_news WHERE kat_id='93' AND date>'1123443300' ORDER BY date LIMIT 1;
# Time: 060426 17:56:10
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 5834
use usr_web204-06_1;
SELECT `poster`, `post_date`, `post_ref`, `post_id` FROM `forum_posts` WHERE `post_theme`="Krieteriumtraining" ORDER BY `post_date` DESC LIMIT 1;
# Time: 060426 17:56:22
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
UPDATE ticker_VISITORS SET lastRequest = "1146066979",seq = "252" WHERE vid = "MOjHhT1fssmu5kxRwoZAag1146062057";
# Time: 060426 17:59:52
# User@Host: web204[web204] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 2277
use usr_web204_1;
SELECT id, title FROM news_news WHERE kat_id='99' AND date>'1110839040' ORDER BY date LIMIT 1;
# Time: 060426 18:01:10
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use usr_web204-06_1;
UPDATE ticker_VISITORS SET lastRequest = "1146067268",seq = "254" WHERE vid = "adLfKiA7z91IlvnSpdAtMX1146065951";
# Time: 060426 18:07:22
# User@Host: root[root] @ localhost []
# Query_time: 6  Lock_time: 0  Rows_sent: 2276  Rows_examined: 2276
use usr_web204_1;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `news_news`;
# Time: 060426 18:07:37
# User@Host: root[root] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1954  Rows_examined: 1954
use usr_web6006_1;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `news_news`;
# Time: 060426 18:15:56
# User@Host: web333[web333] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use usr_web333_1;
SET timestamp=1146068156;
UPDATE  phpads_adstats SET views = views + 1 WHERE day = NOW()
                                AND hour = HOUR(NOW()) AND bannerid = '13' AND zoneid = '1'
                                AND source = '';
# User@Host: web204-06[web204-06] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use usr_web204-06_1;
UPDATE ticker_VISITORS SET lastRequest = "1146068154",seq = "257" WHERE vid = "uzQ3xAnyk4nU9icGGg3nP81146064235";
# Time: 060426 18:15:57
# User@Host: root[root] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use mysql;
SHOW BINLOG EVENTS;
# Time: 060426 18:25:17
# User@Host: web204[web204] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 61  Rows_examined: 2337
use usr_web204_1;
SELECT * FROM news_news WHERE (title REGEXP 'Romandie|Romandie' OR main_text REGEXP 'Romandie|Romandie' OR input_1 REGEXP 'Romandie|Romandie' OR input_2 REGEXP 'Romandie|Romandie' OR input_3
Ich haffe mal das waren jetzt nicht zu viele Daten und das jemand eine Ahnung hat wiso der Server in:
2.46 Stunden Laufzeit auf:
Slow_queries 114
Handler_read_rnd_next 326 M
Created_tmp_disk_tables 2 k
Delayed_errors 3
Key_writes 9 k
kommt

Da System ist ein 2GHZ mit 2GB RAM und trägt den SQL server un einen Apache2 mit php5 ca 10 000 Besucher pro Tag und teilweise ein Load von 3-4 wobei das meiste der SQL Server ausmacht.

bin-log steht zur Verfügung ... wenn es weiter hilft.

greenrover
Posts: 203
Joined: 2004-12-17 19:34

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by greenrover » 2006-04-27 17:00

bzw giebt es einen möglichkeit, mysql anzuweisen das er 2 tabellen komplett in dne ram läd und nur dort verarbeitet.

speichern auf der hdd bein shutdown des swl servers versteht sich vonselbst oder ?!

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by flo » 2006-04-27 18:02

eventuell hilft Dir die engine "memory" da weiter - außerdem sind isotopps und Joe Users Beiträge empfehlenswert :-)

flo.

greenrover
Posts: 203
Joined: 2004-12-17 19:34

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by greenrover » 2006-04-27 18:08

Danke für den Tipp...
Das ganze soll für einen besucherzähler herhalten.. da ist das optimal.

Und das die beiden ober guren in irgent wie allem sind habe ich schon mibekommen.. aber noch nicht ganz den passenden thread gefunden.

Und ich kann mir einfach nichtvorstellen das die Maschine schon bei 250 gleichzeiten request (apache) und sql den ca 2500 gleichezitigen query`s am Ende sein soll.

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by flo » 2006-04-27 18:37

Die Slow-Queries sind besorgniserregend - angenommen, die "id" der Tabelle ist der Primärschlüssel, dürfte das keine zwei Sekunden dauern, einen Datensatz da rauszuholen.

Hast Du pconnects an?

flo.

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by Joe User » 2006-04-27 18:58

GreenRover wrote:Das ganze soll für einen besucherzähler herhalten.. da ist das optimal.
Dir ist aber bewusst, dass bei jedem shutdown des MySQLd die Daten futsch sind?
GreenRover wrote:Und das die beiden ober guren in irgent wie allem sind habe ich schon mibekommen..
Für Kris mag das zutreffen, für mich definitiv nicht.
GreenRover wrote:aber noch nicht ganz den passenden thread gefunden.
Lies die im DB-Forum als "Wichtig" markierten Threads...
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

greenrover
Posts: 203
Joined: 2004-12-17 19:34

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by greenrover » 2006-04-27 21:04

die low query`s komemn von eienr kaum genutzen suchfunktion (start veraltet und over killed)

und vom besucher zähler...

Das mit dem shutdown ist mit den neusten änderungen nicht so schlimmt, da diese tabelle nur dazu diehnt das ein besucher nicht bei jedem click gezählt wird (ip sperre)


slow_query == >1s

greenrover
Posts: 203
Joined: 2004-12-17 19:34

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by greenrover » 2006-04-28 11:26

So ich habe nochmals eine frage...

Ich habe jetzt das bin log abgeschaltet um performance zu spaaren...

für wie sinvoll haltet ihr soetwas ?!
(das normale backup mit einem stündlichen mysqldump läuft weiterhin)

lord_pinhead
Posts: 774
Joined: 2004-04-26 15:57

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by lord_pinhead » 2006-04-28 15:52

Mysqldump ist absoluter Overhead. Die Binlogs sind genial um alle Anfragen die gestellt wurden zu Protokollieren (siehe im Datenbank Forum den Thread "Niemand will Backup, alle wollen Restore.") und bei einem Datenbank Crash wieder einzuspielen. Mysqldump nutze ich eigentlich doch relativ selten, je nachdem wie wichtig die Daten sind und dann auch nicht auf die selbe Kiste. Also ich würde sie anlassen und einfach nur einen aktuellen Dump halten und von da an alles per Binlog machen. Wenn du ein Backupserver hast, kannst du die Binlog immer dorthin schieben. Wer faul ist macht das per FTP und kopiert es immer zum FTP hoch (Proftpd kann ja auch DEL Anweisungen blocken). Allerdings wäre es immer umgedreht sinnvoll, also das der Backup Prozess auf dein Server zugreift. Damit würdest du dir dein Stündliches Dumpen sparen, das entlastet schonmal.

Ein "OPTIMIZE TABLE" vielleicht mal durchlaufen lassen und dann zusehen was langsam fragmentiert, das dauert ne weile bis du das weißt. Danach die Tabellen gezielt verändern und das sollte dann ok sein. Oder zuhause die DB einspielen und dann einfach sinnlos Daten einfügen, das sollte es recht gut simulieren. Rest einfach nochmal Tipps und Tricks die hier im Forum rumgeistern suchen und probieren.

greenrover
Posts: 203
Joined: 2004-12-17 19:34

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by greenrover » 2006-04-28 15:56

Aber das hauptaugenmerk unseres backub besteht darin möglichst junge backups von einzelnden datenbaken zurück spielen zu können.

das geht mit dme bin log ja leider nicht.

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by flo » 2006-04-28 21:37

Wenn die Kiste im Betrieb eh schon auf dem letzten Loch pfeift, würde ich nicht auhc noch mir binlogs die Situation verschlimmern - mir reicht derzeit ein MySQL-Dump, den ich vom Slave ziehe, aber ohne Slave geht das auch, wenn Du Dir halt bewußt bist, daß im schlimmsten Fall Daten eines kompletten Backupzyklus weg sind.

flo.

greenrover
Posts: 203
Joined: 2004-12-17 19:34

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by greenrover » 2006-04-28 22:23

Ja das währe im schimmsten fall halt eine stunde..

da sag ich einfach mal schip happens... und vertrau auf das raid 1

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by flo » 2006-04-28 22:28

GreenRover wrote:da sag ich einfach mal schip happens... und vertrau auf das raid 1
Jein ... die mesiten Backups werden IMHO nötig, weil einer der UnSichERheitsfaktoren Mist baut - das Raid 1 hilft dann nicht wirklich, per Binlog könntest Du (mit viel Aufwand) noch einen Rollback fahren.

flo.

ren
Posts: 18
Joined: 2006-01-04 11:58
Location: Karlsruhe

Re: Mysql 5 wer kann bei der optimierung helfen...

Post by ren » 2006-05-02 18:19

GreenRover wrote:bzw giebt es einen möglichkeit, mysql anzuweisen das er 2 tabellen komplett in dne ram läd und nur dort verarbeitet.
Klar. InnoDB als Tabletype und dann

Code: Select all

innodb_buffer_pool_size=100M
Den Wert sollte grob der Grösse der Tabellen entsprechen.

Evtl. deinen Thread Cache Size auch mal etwas anheben, 8 kommt mir wenig vor.

Ausserdem sehr lesenswert:
http://jeremy.zawodny.com/blog/archives/000173.html