At 18:35 01.04.2005, Gleb Paharenko wrote:
>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 <rafcio@stripped> 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
> / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
>/_/ /_/\_, /___/\___\_\___/ 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/mysql?unsub=1
