| 
 
Joomla Performance
Welcome, Guest
Please Login Using Control Panel At Top Of Page or Register.
Lost Password?
Tweaking MySQL Server (1 viewing)
_GEN_GOTOBOTTOM Post Reply Favoured: 0
TOPIC: Tweaking MySQL Server
#621
bzzik (User)
Senior Boarder
Posts: 16
graphgraph
User Offline Click here to see the profile of this user
Tweaking MySQL Server 2007/10/26 10:22 Karma: 0  
This thread discusses the Content article: Tweaking MySQL Server

Great 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.
#622
ircmaxell (Admin)
Admin
Posts: 961
graph
User Offline Click here to see the profile of this user
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.
#637
bzzik (User)
Senior Boarder
Posts: 16
graphgraph
User Offline Click here to see the profile of this user
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.
#638
bzzik (User)
Senior Boarder
Posts: 16
graphgraph
User Offline Click here to see the profile of this user
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.
#641
ircmaxell (Admin)
Admin
Posts: 961
graph
User Offline Click here to see the profile of this user
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.
#644
bzzik (User)
Senior Boarder
Posts: 16
graphgraph
User Offline Click here to see the profile of this user
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.
#645
ircmaxell (Admin)
Admin
Posts: 961
graph
User Offline Click here to see the profile of this user
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.
#652
bzzik (User)
Senior Boarder
Posts: 16
graphgraph
User Offline Click here to see the profile of this user
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.
#653
ircmaxell (Admin)
Admin
Posts: 961
graph
User Offline Click here to see the profile of this user
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.
#654
bzzik (User)
Senior Boarder
Posts: 16
graphgraph
User Offline Click here to see the profile of this user
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.
#656
ircmaxell (Admin)
Admin
Posts: 961
graph
User Offline Click here to see the profile of this user
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.
#666
bzzik (User)
Senior Boarder
Posts: 16
graphgraph
User Offline Click here to see the profile of this user
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.
#867
nick (User)
Junior Boarder
Posts: 9
graphgraph
User Offline Click here to see the profile of this user
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.
#868
nick (User)
Junior Boarder
Posts: 9
graphgraph
User Offline Click here to see the profile of this user
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.
#880
ircmaxell (Admin)
Admin
Posts: 961
graph
User Offline Click here to see the profile of this user
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.
_GEN_GOTOTOP Post Reply
get the latest posts directly to your desktop
feed image
Copyright 2007 Anthony Ferrara, All Rights Reserved