List:General Discussion« Previous MessageNext Message »
From:Rafal Kedziorski Date:April 6 2005 2:04pm
Subject:Re: Strange behavior
View as plain text  
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

Thread
Strange behaviorRafal Kedziorski1 Apr
  • Re: Strange behaviorGleb Paharenko4 Apr
    • Re: Strange behaviorRafal Kedziorski6 Apr
      • Re: Strange behaviorGleb Paharenko7 Apr
        • Re: Strange behaviorRafal Kedziorski7 Apr
          • Re: Strange behaviorGleb Paharenko8 Apr
            • Re: Strange behaviorRafal Kedziorski8 Apr
            • Re: Strange behaviorRafal Kedziorski8 Apr
        • Re: Strange behaviorRafal Kedziorski7 Apr