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: 19
|
|
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: 19
|
|
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: 19
|
|
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: 19
|
|
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: 19
|
|
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 | 0 |
| Handler_update | 4805410 |
| Handler_write | 44833612 |
| 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 | 918691 |
| Key_blocks_used | 9261 |
| Key_read_requests | 366034478 |
| Key_reads | 43438 |
| Key_write_requests | 2602759 |
| Key_writes | 151058 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 67 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 2383 |
| Open_streams | 0 |
| Open_tables | 1463 |
| Opened_tables | 1609 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 30536 |
| Qcache_free_memory | 446472240 |
| Qcache_hits | 4172172 |
| Qcache_inserts | 2014680 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 91497 |
| Qcache_queries_in_cache | 66616 |
| Qcache_total_blocks | 164070 |
| Questions | 7336575 |
| Rpl_status | NULL |
| Select_full_join | 1586 |
| Select_full_range_join | 0 |
| Select_range | 39996 |
| Select_range_check | 0 |
| Select_scan | 346698 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 9 |
| Sort_merge_passes | 16 |
| Sort_range | 54553 |
| Sort_rows | 62196926 |
| Sort_scan | 776805 |
| 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 | 6006155 |
| Table_locks_waited | 107773 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 65 |
| Threads_connected | 2 |
| Threads_created | 67 |
| Threads_running | 2 |
| Uptime | 36940 |
+-----------------------------------+------------+
|
any help it will be thankful Post edited by: nick, at: 2007/11/28 02:28 |
|
|
|
The administrator has disabled public write access. |
nick (User)
Junior Boarder
Posts: 9
|
|
Re:Tweaking MySQL Server 2007/11/28 01:43
|
Karma: 0
|
|
i have in red colour
| Handler_read_rnd | 21320496 |
| Handler_read_rnd_next | 2617079808 |
| Created_tmp_disk_tables| 3,697 |
| Select_full_join | 1586 |
| Opened_tables | 1609 |
| Table_locks_waited | 107773 |
| Slow_queries | 9 |
also the problem is on cpu load not ram load
sorry for the double post!
|
|
|
|
The administrator has disabled public write access. |
|
|
|
Re:Tweaking MySQL Server 2007/11/28 13:59
|
Karma: 19
|
|
It doesn't look too bad. I wouldn't worry too much about those numbers. I'm going to talk to one of the Joomla devs about the query modification I posted on the forums for 1.0, and I'll get back to you.
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. |
|
|
|
|
|
|
Copyright 2007 Anthony Ferrara, All Rights Reserved
|
|
