Hello.
I don't have any ideas at least now. But additional information could be helpful. Do you connect from JBoss to the slave or master server? Please use
We are conecting to the active mysql (normaly master).
SHOW PROCESSLIST to find in what state the server threads waste their time. If you find something interesting send it. Include also the output of SHOW STATUS and SHOW VARIABLES.
SHOW STATUS:
mysql> show status; +--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+ | Aborted_clients | 342 | | Aborted_connects | 0 | | Bytes_received | 2114765083 | | Bytes_sent | 3521573247 | | Com_admin_commands | 3992 | | Com_alter_table | 2 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 119962 | | Com_change_master | 0 | | Com_check | 0 | | Com_commit | 106880 | | Com_create_db | 1 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 162153 | | Com_delete_multi | 0 | | Com_drop_db | 1 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 147742 | | 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_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 | 0 | | Com_rollback | 3691 | | Com_savepoint | 0 | | Com_select | 9075484 | | Com_set_option | 32097 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 5 | | Com_show_create | 0 | | Com_show_databases | 44 | | Com_show_fields | 1284 | | Com_show_grants | 0 | | Com_show_keys | 1219 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 69 | | Com_show_slave_hosts | 4 | | Com_show_slave_status | 0 | | Com_show_status | 22195 | | Com_show_innodb_status | 13030 | | Com_show_tables | 1483 | | Com_show_variables | 56755 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 39024 | | Connections | 45560 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 56631 | | Created_tmp_files | 2133 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 106883 | | Handler_delete | 1268 | | Handler_read_first | 13902 | | Handler_read_key | 3619254984 | | Handler_read_next | 3479415584 | | Handler_read_prev | 0 | | Handler_read_rnd | 7278832 | | Handler_read_rnd_next | 756152091 | | Handler_rollback | 7624 | | Handler_update | 88733 | | Handler_write | 218257589 | | Key_blocks_used | 125 | | Key_read_requests | 16111 | | Key_reads | 107 | | Key_write_requests | 16822 | | Key_writes | 7750 | | Max_used_connections | 93 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 414 | | Open_files | 43 | | Open_streams | 0 | | Opened_tables | 482 | | Questions | 9869197 | | Qcache_queries_in_cache | 970 | | Qcache_inserts | 13534 | | Qcache_hits | 40792 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 8890003 | | Qcache_free_memory | 30470064 | | Qcache_free_blocks | 2 | | Qcache_total_blocks | 2000 | | Rpl_status | NULL | | Select_full_join | 2 | | Select_full_range_join | 0 | | Select_range | 51465 | | Select_range_check | 0 | | Select_scan | 17467 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 45130 | | Sort_merge_passes | 0 | | Sort_range | 25 | | Sort_rows | 7280323 | | Sort_scan | 28708 | | Ssl_accepts | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_accept_renegotiates | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_client_connects | 0 | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_mode | NONE | | Ssl_sessions_reused | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_verify_depth | 0 | | Ssl_version | | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_default_timeout | 0 | | Table_locks_immediate | 9656621 | | Table_locks_waited | 1 | | Threads_cached | 9 | | Threads_created | 94 | | Threads_connected | 85 | | Threads_running | 2 | | Uptime | 545691 | +--------------------------------+------------+ 155 rows in set (0.03 sec)
SHOW VARIABLES:
mysql> show variables;
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /drbd/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | YES |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 20971520 |
| innodb_buffer_pool_size | 1287651328 |
| innodb_data_file_path | ibdata1:2048M;ibdata2:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | ./ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 104857600 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_table_locks | ON |
| interactive_timeout | 28800 |
| join_buffer_size | 8388608 |
| key_buffer_size | 134217728 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | ON |
| 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 | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 300 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 1510 |
| pid_file | /drbd/mysql/tocldb1.rz.bln.de.mgx.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 4190208 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 20 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 8388600 |
| sql_mode | 0 |
| table_cache | 512 |
| table_type | MYISAM |
| thread_cache_size | 400 |
| thread_stack | 196608 |
| tx_isolation | REPEATABLE-READ |
| timezone | CEST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.23a-log |
| version_comment | Source distribution |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
133 rows in set (0.08 sec)
I tryied an older version of our JBoss application and a newer JBoss. But without any changes.
Regards, Rafal
Rafal Kedziorski <[EMAIL PROTECTED]> wrote:
> Hi,
>
> after extending our MySQL 4.0.23a installation to master-slave
> configuration two specific queries sended from our JBoss are 25-30 times
> slower.
>
> In our J2EE application which runs under JBoss 3.2.2 we are generating own
> queries by using a connection from JBoss connection pool. This are prepared
> statements:
>
> 1.
>
> select count(distinct m.media_id) from category_tree c_tree,
> media_2_category m2c, media m, magix_product mp, media_type_2_magix_product
> mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ?
> and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id =
> m.media_id and mp.magix_product_id = ? and mp.magix_product_id =
> mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and
> mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null)
> and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or
> c_tree.parent_id = ? or c_tree.path like ?)
>
> 2.
>
> select distinct m.media_id from category_tree c_tree, media_2_category m2c,
> media m, media_2_partner m2p, magix_product mp, media_type_2_magix_product
> mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ?
> and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id =
> m.media_id and m2p.media_id = m.media_id and m2p.partner_id = ? and
> mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id
> and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and
> (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id
> = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path
> like ?) order by m2p.priority desc limit ?, ?
>
> Times:
>
> 1.
> - needed from JBoss 450-500 millis
> - nedded from normal Java application 15-25 millis
>
> 2.
> needed from JBoss 500-800 millis
> - nedded from normal Java application 19 millis
> - nedded from normal Java application 20-30 millis
>
> All other sql statements generated by JBoss for entity beans are fast like
> bevore switching to master-slave configuration. Thru this queries the speed
> of our service is 2-3 times slower.
>
> After spend some hours checking our system, I have no more idea where is
> the problem.
>
> This is our my.cnf:
>
> [mysqld]
> datadir=/drbd/mysql
>
> log-bin
> server-id=20
>
> set-variable = key_buffer=128M
> set-variable = table_cache=512
> set-variable = sort_buffer=8M
> set-variable = join_buffer_size=8M
> set-variable = query_cache_size=32M
> set-variable = record_buffer=4M
> set-variable = thread_cache_size=400
> set-variable = max_connections=300
> set-variable = long_query_time=10
> log_long_format
> log_slow_queries
> innodb_data_file_path = ibdata1:2048M;ibdata2:10M:autoextend
> #innodb_buffer_pool_size = 384M
> innodb_buffer_pool_size = 1228M
> innodb_additional_mem_pool_size = 20M
> innodb_log_file_size = 100M
> innodb_log_buffer_size = 8M
> innodb_flush_log_at_trx_commit = 1
>
>
> Regards,
> Rafal
>
>
-- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
