Re: Slower machine 100's of times faster??
Hi,
I had not thought I had been abandoned, just that I was working through the problem, hoping someone more knowledgable would jump in some time and say "hey try this".
I am concentrating on the 2nd of the queries I have shown in this thread, since they are on both machines. About the 1st query I mentioned, you pointed out "cw(P1)" had poor selectivity, that will change when that table has many different values in P1, up to about 100 from current 2.
I changed the my.cnf to more closely match the one on my slower machine, here:
[client]
#password = [your_password]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
default-character-set=latin1
log-bin=db1
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_size=75M
table_cache=256
tmp_table_size=200M
max_connections=100
default-storage-engine=INNODB
innodb_data_home_dir=/var/mysql_data
innodb_log_group_home=/var/mysql_log
innodb_buffer_pool_size=1500M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=400M
innodb_log_buffer_size=8M
innodb_thread_concurrency=8
[mysql.server]
user=mysql
#basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
I also ran SHOW INDEX on the two tables in the 2nd query
mysql> SHOW INDEX FROM secm_security_universe;
+------------------------+------------+----------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| secm_security_universe | 0 | PRIMARY | 1 | SECURITY_ID | A | 835385 | NULL | NULL | | BTREE | |
| secm_security_universe | 1 | FK_SECM_SECURITY_UL | 1 | UL_SECURITY_ID | A | 17 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | FK_SEC_UNI_SIMPLE_SEC_TYPE | 1 | SECURITY_TYPE | A | 17 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | FK_SEC_UNIV_BOND_CORE_INFO | 1 | ASSET_ID | A | 835385 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | FK_EQU_RIC | 1 | EQU_RIC | A | 835385 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | FK_IND_RIC | 1 | IND_RIC | A | 167077 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | FK_FND_RIC | 1 | FND_RIC | A | 17 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | FK_OPT_RIC | 1 | OPT_RIC | A | 17 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | SEC_UNIV_WNTS_XREF | 1 | WAR_RIC | A | 417692 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | IX_CUSIP | 1 | CUSIP | A | 417692 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | IX_SEDOL | 1 | SEDOL | A | 835385 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | IX_VALOREN | 1 | VALOREN | A | 27846 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | IX_ISIN | 1 | ISIN | A | 835385 | NULL | NULL | YES | BTREE | |
| secm_security_universe | 1 | IX_ISIN | 2 | EXCHANGE_CD | A | 835385 | NULL | NULL | YES | BTREE | |
+------------------------+------------+----------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
14 rows in set (0.95 sec)
mysql> SHOW INDEX FROM corax_event;
+-------------+------------+---------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+---------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| corax_event | 0 | PRIMARY | 1 | Event_Id | A | 48549 | NULL | NULL | | BTREE | |
| corax_event | 1 | Asset_Id_idx | 1 | ASSET_ID | A | 48549 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | FI_Issuer_idx | 1 | FI_ISSUER_ID | A | 2 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | EQ_Issuer_Idx | 1 | EQ_ISSUER_ID | A | 2 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | Cap_Chng_Market_idx | 1 | Cap_Chng_Market_Lvl_Id | A | 2 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | RIC_idx | 1 | RIC | A | 48549 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | Event_Type_idx | 1 | Event_Type | A | 2 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | Div_Market_Lvl_idx | 1 | Div_Market_Lvl_Id | A | 48549 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | Security_Id_idx | 1 | SECURITY_ID | A | 48549 | NULL | NULL | YES | BTREE | |
| corax_event | 1 | Security_Id_idx | 2 | Event_Time | A | 48549 | NULL | NULL | | BTREE | |
| corax_event | 1 | Event_Time_idx | 1 | Event_Time | A | 3734 | NULL | NULL | | BTREE | |
+-------------+------------+---------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.23 sec)
It is the 1st two SELECTs in the set of UNIONs that are taking the time, I ran them independently.
Finally here is the SHOW VARIABLES for the database running these queries so slowly
mysql> SHOW VARIABLES;
+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/share/mysql-standard-5.0.16/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-standard-5.0.16/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 1572864000 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | /var/mysql_data |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 419430400 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /var/mysql_log |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql-standard-5.0.16/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/lib/mysql/db1.stpenable.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 78643200 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | InnoDB |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 10 |
| system_time_zone | EST |
| table_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_type | InnoDB |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 209715200 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.16-standard-log |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+--------------------------------------------------------+
209 rows in set (0.00 sec)
Finally I tried to setup 5.0.16 on my slower machine so I could compare 4.1.14 with 5.0.16 on identical hardware, but for some unknown reason even with separate my.ini files and listening on separate ports 5.0 I cannot connect to 5.0, I get a 1130 error. But the faster machine is similar enough really, only having .5GB more RAM and faster disk system.
Thx.
David
Subject
Views
Written By
Posted
2198
December 09, 2005 11:45AM
1433
December 09, 2005 11:51AM
1502
December 09, 2005 11:53AM
1579
December 09, 2005 12:11PM
1365
December 09, 2005 12:12PM
1363
December 09, 2005 12:33PM
1392
December 10, 2005 10:50AM
1464
December 12, 2005 05:14AM
1394
December 13, 2005 03:16AM
1462
December 13, 2005 05:10AM
1421
December 13, 2005 10:53AM
1534
December 14, 2005 11:17PM
Re: Slower machine 100's of times faster??
1573
December 16, 2005 05:15AM
1483
December 16, 2005 06:11AM
1747
December 16, 2005 08:14AM
1536
December 16, 2005 11:30AM
1497
December 16, 2005 11:46AM
1419
December 16, 2005 12:06PM
1390
December 21, 2005 09:21AM
1548
December 21, 2005 10:08AM
1829
December 23, 2005 09:22AM
1423
December 26, 2005 08:32PM
1412
December 14, 2005 10:47PM
1426
December 23, 2005 12:29PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.