Tweaking MySQL Server (1 viewing)
Favoured: 0
|
|
|
TOPIC: Tweaking MySQL Server
|
bzzik (User)
Senior Boarder
Posts: 16
|
|
Tweaking MySQL Server 2007/10/26 10:22
|
Karma: 0
|
This thread discusses the Content article: Tweaking MySQL ServerGreat article! It helped me a lot, but I still have some questions. I hope you can answer on it  1. I have Qcache_free_memory about 10-11M everytime, but still Qcache_lowmem_prunes is too big about 5 000 - 6 000. 2. tmp_table_size is more than 32M, but still I have 10 - 17 k Created_tmp_disk_tables 3. I have about 900-1000 Opened_tables and 400-500 Open_tables - is it normal? And the last - Table_locks_waited is too big. It is always about 2 000 - 3 000  Thank you  |
|
|
|
The administrator has disabled public write access. |
|
|
|
Re:Tweaking MySQL Server 2007/10/26 10:33
|
Karma: 14
|
|
Well,
1. Try bumping it up by 20 megs or so... It could be you have some large queries...
2. Try bumping max_heap_table_size up a little...
3. That is about normal... 2-1 ratio is fine.
As for table_locks_waited, there is no tuning that you can do to change that...
The biggest obstruction to innovation is not ignorance, but the delusion of knowledge...
JFGI + RTFM will answer 98% of all questions.... |
|
|
|
The administrator has disabled public write access. |
bzzik (User)
Senior Boarder
Posts: 16
|
|
Re:Tweaking MySQL Server 2007/10/27 07:15
|
Karma: 0
|
|
Ok, what I have now: We have 106 guests and 52 users online
# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
skip-locking
max_connections=1000
interactive_timeout=100
wait_timeout=100
connect_timeout=10
key_buffer=256M
max_allowed_packet=16M
table_cache=1024
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
myisam_sort_buffer_size=64M
thread_cache_size=128
query_cache_size=48M
query_cache_limit=1M
query_cache_type=1
tmp_table_size=64M
max_heap_table_size=64M
join_buffer_size=1M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
Qcache_free_blocks
Qcache_free_memory 47 M
Qcache_hits 177 k
Qcache_inserts 17 k
Qcache_lowmem_prunes 0
Qcache_not_cached 8,005
Qcache_queries_in_cache 1,645
Qcache_total_blocks 4,147
Threads_cached 62
Threads_connected 21
Threads_created 83
Created_tmp_disk_tables 3,697
Created_tmp_tables 7,253
Key_blocks_unused 230 k
Key_blocks_used 2,047
Key_read_requests 26 M
Key_reads 2,047
Key_write_requests 7,878
Key_writes 5,532
Open_tables 370
Opened_tables 376
Should I change anything?
Post edited by: bzzik, at: 2007/10/27 07:26
|
|
|
|
The administrator has disabled public write access. |
bzzik (User)
Senior Boarder
Posts: 16
|
|
Re:Tweaking MySQL Server 2007/10/28 05:25
|
Karma: 0
|
|
Ok, more user more porblems - again
Qcache_lowmem_prunes 5,602
|
|
|
|
The administrator has disabled public write access. |
|
|
|
Re:Tweaking MySQL Server 2007/10/29 16:30
|
Karma: 14
|
|
What's the entire qcache section?
The biggest obstruction to innovation is not ignorance, but the delusion of knowledge...
JFGI + RTFM will answer 98% of all questions.... |
|
|
|
The administrator has disabled public write access. |
bzzik (User)
Senior Boarder
Posts: 16
|
|
Re:Tweaking MySQL Server 2007/10/30 14:44
|
Karma: 0
|
|
We have 209 guests and 69 users online - important values:
Slow_queries 2 (what this? how to avoid it?)
Created_tmp_disk_tables 350 k
Created_tmp_tables 620 k
Sort_merge_passes 45
Open_tables 560
Opened_tables 2,518
Where to find entire qcache section?
|
|
|
|
The administrator has disabled public write access. |
|
|
|
Re:Tweaking MySQL Server 2007/10/30 22:15
|
Karma: 14
|
|
Well, your opened_tables is right where it should be... good... tmp tables are decent, not too bad... Slow Queries are great (you are good)...
As for qcache section, I mean :
Qcache_free_blocks
Qcache_free_memory 47 M
Qcache_hits 177 k
Qcache_inserts 17 k
Qcache_lowmem_prunes 0
Qcache_not_cached 8,005
Qcache_queries_in_cache 1,645
Qcache_total_blocks 4,147
The biggest obstruction to innovation is not ignorance, but the delusion of knowledge...
JFGI + RTFM will answer 98% of all questions.... |
|
|
|
The administrator has disabled public write access. |
bzzik (User)
Senior Boarder
Posts: 16
|
|
Re:Tweaking MySQL Server 2007/10/31 11:29
|
Karma: 0
|
|
We have 134 guests and 49 users online
Qcache_free_blocks 2,316
Qcache_free_memory 38 M
Qcache_hits 3,259 k
Qcache_inserts 364 k
Qcache_lowmem_prunes 4,449
Qcache_not_cached 169 k
Qcache_queries_in_cache 5,199
Qcache_total_blocks 13 k
Do not like that Qcache_lowmem_prunes still big...
Binlog_cache_disk_use 7 - what is this? It is normal?
ort_merge_passes 45 - should I increase sort_buffer_size?
|
|
|
|
The administrator has disabled public write access. |
|
|
|
Re:Tweaking MySQL Server 2007/10/31 11:37
|
Karma: 14
|
|
bzzik wrote:
Do not like that Qcache_lowmem_prunes still big...
Qcache_lowmem_prunes
The number of queries that were deleted from the query cache because of low memory.
Binlog_cache_disk_use 7 - what is this? It is normal?
Binlog_cache_disk_use
The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
Sort_merge_passes 45 - should I increase sort_buffer_size?
Sort_merge_passes
The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Watch the qcache_lowmem_prunes, and see if it grows... If it doesn't, I woudlnt' worry about it...
The biggest obstruction to innovation is not ignorance, but the delusion of knowledge...
JFGI + RTFM will answer 98% of all questions.... |
|
|
|
The administrator has disabled public write access. |
bzzik (User)
Senior Boarder
Posts: 16
|
|
Re:Tweaking MySQL Server 2007/10/31 16:03
|
Karma: 0
|
|
binlog_cache_size what value should be?
|
|
|
|
The administrator has disabled public write access. |
|
|
|
Re:Tweaking MySQL Server 2007/10/31 16:22
|
Karma: 14
|
|
You got me on that one... Check out MySQL's documentation for the binlog...
The biggest obstruction to innovation is not ignorance, but the delusion of knowledge...
JFGI + RTFM will answer 98% of all questions.... |
|
|
|
The administrator has disabled public write access. |
bzzik (User)
Senior Boarder
Posts: 16
|
|
Re:Tweaking MySQL Server 2007/11/01 07:03
|
Karma: 0
|
I will do so, thanks anyway for your answers and help!
|
|
|
|
The administrator has disabled public write access. |
nick (User)
Junior Boarder
Posts: 9
|
|
Re:Tweaking MySQL Server 2007/11/27 21:04
|
Karma: 0
|
hello, thank you for the information first I follow your instructions but i have very big load when i have 300+ users. i have a system Core 2 Duo with 4gb ram | Code: |
my config is:
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
open_files_limit=8192
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
key_buffer = 1024M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 128
max_connections = 750
table_cache = 1280M
thread_concurrency = 4
join_buffer = 4M
sort_buffer = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 40M
tmp_table_size = 656M
query_cache_limit = 2M
query_cache_size = 512M
query_cache_type = 1
expire_logs_days = 10
max_binlog_size = 100M
skip-bdb
skip-innodb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[isamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
|
and the extended-status is
| Code: |
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 2 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 1 |
| Bytes_received | 1265541709 |
| Bytes_sent | 1590434364 |
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_table | 69 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 266081 |
| Com_change_master | 0 |
| Com_check | 919 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 32 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 101042 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 10 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 35941 |
| Com_insert_select | 21 |
| 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 | 1966 |
| 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 | 2105817 |
| Com_set_option | 254059 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 11 |
| Com_show_charsets | 34 |
| Com_show_collations | 34 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 1 |
| Com_show_databases | 9 |
| Com_show_errors | 0 |
| Com_show_fields | 16 |
| Com_show_grants | 13 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 14 |
| 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 | 131 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 145 |
| Com_show_triggers | 0 |
| Com_show_variables | 76 |
| 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 | 279625 |
| Com_update_multi | 4074 |
| 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 | 113436 |
| Created_tmp_disk_tables | 89667 |
| Created_tmp_files | 32 |
| Created_tmp_tables | 736155 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 1 |
| Handler_delete | 10920 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 62460 |
| Handler_read_key | 115070306 |
| Handler_read_next | 1380998355 |
| Handler_read_prev | 1336418 |
| Handler_read_rnd | 21320496 |
| Handler_read_rnd_next | 2617079808 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback
|
| | | |
