Ph: 33554432

Questions

The questions graph shows the number of questions/minute. It has no direct relation to a tuneable parameter, but can be used as a hint to find out during which periods a more thorough logging should be conducted.

The most relevant types of questions (SELECT, INSERT, UPDATE and DELETE) are also logged.

Hide graphs

Connections

The connection graph shows the active connections and the configured max_connections.

In a sane configuration you should allways be able to see the max_connections graph. If it is hidden by the active connections graph all connections are in use.

Hide graphs

Tables

Table Cache

The table cache graph shows the open tables and the table_cache setting.

If the table_cache graph is hidden by the open_tables graph all table cache entries are in use.

Hide graphs

Temporary Tables

The temporary tables graph shows the number of implicit temporary tables. For the best performance these tables should be created in memory.

If there are many temporary disk tables (created_tmp_disk_tables), you can increase your tmp_table_size to improve your server's performance.

Hide graphs

Indexes

Key Cache

The key cache miss rate (key_read_requests/keyreads) should be lower than 1/100 (10m, 10 mili) indicated by an horizontal ruler and is quite ok for most systems if it's lower than 1/1000 (1m) also indicated by an horizontal ruler.

One could raise the key_buffer_size parameter to improve the key cache rate.

Hide graphs

Key Buffer

The key buffer graph shows the amount of memory that's in use as key cache.

Hide graphs

Full Joins

The select_full_join value should allways be 0. If its not 0, you should check the indexes of you tables.

The full joins graph can assist you in finding out when there have been full joins.

Hide graphs

Range Checks

The select_range_check value should allways be 0. If its not 0, you should check the indexes of you tables.

The range checks graph can assist you in finding out when there have been joins without keys where the key usage got checked after each row.

Hide graphs

Reads

The reads graph shows the read requests based on a key (handler_read_key) and the read requests based on a fixed position in a datafile (handler_read_rnd).

The handler_read_key indicates that your queries use properly indexed tables, this value should be high. The handler_read_rnd indicates the opposite and should be as low as possible.

Hide graphs

Slow Queries

The slow queries graph show the queries that took longer than long_query_time seconds (for this server the long_query_time=10 seconds).

If this value is not 0, you should activate the slow query log to find out which queries take that long to complete. The slow query log for your server is ON.

Hide graphs

Server Info

Your server (dbase1) has been running MySQL version 3.23.54-log for 425 hours since 01-03-2004 17:26.

General Remarks

Since startup, dbase1 received 37950460 questions.

Question Issued
select 35580224
change_db 1460256
admin_commands 1347941
show_status 312114
update 272763
insert 215391
show_variables 86616
show_tables 10685
show_keys 3932
show_fields 2700
show_create 1670
delete 792
show_databases 110
alter_table 7
create_table 2
drop_table 2
flush 2
set_option 2
analyze 1
check 1
grant 1
revoke 1
show_processlist 1

Tuning Tips

Server tuning

Your server seems to be running for more than 7 days. It should be OK to use the tips below, but don't follow the advise blindly because this script is no replacement for common sense.

The maximum number of used connections is 257 which is 66 % of the configured maximum.

Your max_connections variable seems to be OK.

The table cache hit rate is 20 %, while 54 % of your table cache is in use.

Your table_cache variable seems to be OK.

The temporary table hit rate is 98 %.

Your tmp_table_size variable seems to be OK.

The key cache miss rate is 1/119271.

Your key_buffer_size seems to be too high, maybe you can use it's resources for something else.

Before you lower it, you should monitor the key buffer usage more closely. It may be possible that the keybuffer gets used at peak load.

Database tuning

The problems identified in this section can't be solved by tuning the server parameters. If you didn't design the database or wrote the queries yourself, you'll have to talk to the database developers to solve these.

You need to check your indexes and queries because you had 3 full joins (select_full_join). You need to look at your indexes because you had 2144 joins without keys (select_range_check). You need to look at the indexing of your databases because the number of reads without using a key is too high. Reads using a key = 32209034 (handler_read_key), reads not using a key = 50407719 (handler_read_rnd). You've got 348 slow queries. Check your slow query log to find out which query is causing trouble.

Show status

Parameter Value
Aborted_clients 105588
Aborted_connects 892
Bytes_received 3338138040
Bytes_sent 607907177
Com_admin_commands 1347941
Com_alter_table 7
Com_analyze 1
Com_backup_table 0
Com_begin 0
Com_change_db 1460256
Com_change_master 0
Com_check 1
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 2
Com_delete 792
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 2
Com_flush 2
Com_grant 1
Com_insert 215391
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 1
Com_rollback 0
Com_select 35580224
Com_set_option 2
Com_show_binlogs 0
Com_show_create 1670
Com_show_databases 110
Com_show_fields 2700
Com_show_grants 0
Com_show_keys 3932
Com_show_logs 0
Com_show_master_status 0
Com_show_open_tables 0
Com_show_processlist 1
Com_show_slave_status 0
Com_show_status 312114
Com_show_innodb_status 0
Com_show_tables 10685
Com_show_variables 86616
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 272763
Connections 109381
Created_tmp_disk_tables 9375
Created_tmp_tables 727106
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 2
Handler_delete 2905
Handler_read_first 270045
Handler_read_key 32209034
Handler_read_next 1455541937
Handler_read_prev 613908833
Handler_read_rnd 50407719
Handler_read_rnd_next 1217249120
Handler_update 276991
Handler_write 13626817
Key_blocks_used 21953
Key_read_requests 472195546
Key_reads 3959
Key_write_requests 1063668
Key_writes 602835
Max_used_connections 257
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 279
Open_files 532
Open_streams 0
Opened_tables 1375
Questions 37950460
Select_full_join 3
Select_full_range_join 0
Select_range 751479
Select_range_check 2144
Select_scan 17301643
Slave_running OFF
Slave_open_temp_tables 0
Slow_launch_threads 0
Slow_queries 348
Sort_merge_passes 0
Sort_range 48
Sort_rows 50407828
Sort_scan 10516240
Table_locks_immediate 37534155
Table_locks_waited 18155
Threads_cached 5
Threads_created 13662
Threads_connected 19
Threads_running 1
Uptime 1532232

Show variables

Variable Value
back_log 50
basedir /usr/
binlog_cache_size 32768
character_set latin1
character_sets latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620
concurrent_insert ON
connect_timeout 5
datadir /var/lib/mysql/
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
have_bdb NO
have_gemini NO
have_innodb DISABLED
have_isam YES
have_raid NO
have_openssl NO
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 0
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
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_mirrored_log_groups 1
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 268431360
language /usr/share/mysql/english/
large_files_support ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_long_queries ON
long_query_time 10
low_priority_updates OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 384
max_connect_errors 7500
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 256
myisam_max_sort_file_size 2047
myisam_recover_options 0
myisam_sort_buffer_size 67108864
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
open_files_limit 0
pid_file /var/run/mysqld/mysqld.pid
port 3306
protocol_version 10
record_buffer 2093056
record_rnd_buffer 2093056
query_buffer_size 0
safe_show_database OFF
server_id 0
slave_net_timeout 3600
skip_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer 2097144
sql_mode 0
table_cache 512
table_type MYISAM
thread_cache_size 8
thread_stack 65536
transaction_isolation READ-COMMITTED
timezone CET
tmp_table_size 33554432
tmpdir /tmp/
version 3.23.54-log
wait_timeout 28800

You are viewing a mobilized version of this site...
View original page here

Mobilized by Mowser Mowser