MySQL Forums
Forum List  »  Performance

Benchmarking issues
Posted by: Derek Burket
Date: October 24, 2005 12:05PM

Greetings,

I'm trying to optimize my database to get maximum performance. The data in this database will be read-only and will probably be reloaded each night. I've got lots of indexes on the tables and my explain plans look good. These queries all use correlated subqueries, which I realize don't perform as well as regular joins. However, these queries are generated by a web app that only knows how to generate SQL using subqueries, so I can't change that at this point.

I've tried to disable all MySQL caching in order to determine where the bottlenecks in my queries are and because I need to know how exactly how long the user is going to wait to get a count back. My hopes are that preloading the indexes will increase performance. I set query_cache_size=0 and key_buffer_size=8 and flushed the tables before I run my benchmarks. However, I've noticed that the first time I execute a query, it takes minutes to run. If I run it repeatedly it comes back faster, usually in less than 5 seconds. I've read that MySQL doesn't cache data. So if it's not caching the data, the query or the keys, then why does the query come back faster if executed repeatedly? Is this from the OS cache, or am I failing to disable all MySQL caching?

Below is my server status, vars, tb creates, and one of the queries I'm using with an explain plan.
The server is a quad Xeon 64-bit 3.4 GHz.running Red Hat Enterprise Linux ES release 3. This box is a dedicated MySQL server.

mysql> show status;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 8 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 52462 |
| Connections | 50 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 63 |
| Created_tmp_tables | 15 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 6 |
| Handler_commit | 10 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_read_first | 55 |
| Handler_read_key | 20534073 |
| Handler_read_next | 106329869 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 118785933 |
| Handler_rollback | 3 |
| Handler_update | 0 |
| Handler_write | 15617462 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 307222 |
| Key_blocks_used | 140770 |
| Key_read_requests | 9087840 |
| Key_reads | 9087840 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 3 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 54 |
| Open_streams | 0 |
| Open_tables | 47 |
| Opened_tables | 131 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 754 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 15 |
| Select_range_check | 0 |
| Select_scan | 66 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 21 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 272 |
| Table_locks_waited | 0 |
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 1 |
| Uptime | 6603 |
+--------------------------+------------+
156 rows in set (0.00 sec)


mysql> show variables;
+---------------------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /apps/mysql/db/ |
| 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 |
| 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_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_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 | 402653184 |
| innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend |
| innodb_data_home_dir | /apps/mysql/db/ |
| innodb_fast_shutdown | ON |
| 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 | /apps/mysql/db/ |
| 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 | /apps/mysql/db/ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | ON |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | 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 | 1047552 |
| 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 | 4194302976 |
| 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 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1134 |
| pid_file | /apps/mysql/db/largo.techtarget.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 | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 8384512 |
| relay_log_purge | ON |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| socket | /apps/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| sync_frm | ON |
| system_time_zone | EDT |
| table_cache | 512 |
| table_type | MyISAM |
| thread_cache_size | 8 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | /apps/mysql/tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.10a-pro-gpl-log |
| version_comment | MySQL Pro Certified Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------------------+
179 rows in set (0.00 sec)

SELECT COUNT(DISTINCT ue.user_id)
FROM USER_OPTOUT_CMP ue where list_id IN (571078, 911115, 897640, 571071, 897638, 571072, 897658, 571068, 897598, 897686, 897687, 571076, 571070, 571080, 897636)
AND
(
(EXISTS (SELECT 1
FROM
USER_MASTER_CMP uc1
WHERE (ue.user_id = uc1.user_id AND
country IN ('US') )) )
AND
(EXISTS (SELECT 1
FROM
USER_MASTER_CMP uc2
WHERE (ue.user_id = uc2.user_id AND
industry IN (11) )) )
AND
(NOT EXISTS (SELECT 1
FROM
USER_SITE_CMP us2
WHERE (ue.user_id = us2.user_id AND
site_id IN (11, 13, 14, 15, 16, 17) )) )
)

mysql> EXPLAIN SELECT COUNT(DISTINCT ue.user_id) FROM USER_OPTOUT_CMP ue where list_id IN (571078, 911115, 897640, 571071, 897638, 571072, 897658, 571068, 897598, 897686, 897687, 571076, 571070, 571080, 897636) AND ( (EXISTS (SELECT 1 FROM USER_MASTER_CMP uc1 WHERE (ue.user_id = uc1.user_id AND country IN ('US') )) ) AND (EXISTS (SELECT 1 FROM USER_MASTER_CMP uc2 WHERE (ue.user_id = uc2.user_id AND industry IN (11) )) ) AND (NOT EXISTS (SELECT 1 FROM USER_SITE_CMP us2 WHERE (ue.user_id = us2.user_id AND site_id IN (11, 13, 14, 15, 16, 17) )) ) );
+----+--------------------+-------+--------+-------------------------+---------+---------+-----------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------+---------+---------+-----------------------+--------+--------------------------+
| 1 | PRIMARY | ue | range | list_id | list_id | 3 | NULL | 341233 | Using where |
| 4 | DEPENDENT SUBQUERY | us2 | ref | PRIMARY,user_id,site_id | PRIMARY | 3 | listpuller.ue.user_id | 3 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | uc2 | eq_ref | PRIMARY,industry | PRIMARY | 3 | listpuller.ue.user_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | uc1 | eq_ref | PRIMARY,country | PRIMARY | 3 | listpuller.ue.user_id | 1 | Using where |
+----+--------------------+-------+--------+-------------------------+---------+---------+-----------------------+--------+--------------------------+
4 rows in set (0.01 sec)


/* 27647220 rows */
CREATE TABLE `USER_OPTOUT_CMP` (
`user_id` mediumint(8) unsigned NOT NULL default '0',
`list_id` mediumint(8) unsigned NOT NULL default '0',
`list_type_cd` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`user_id`,`list_id`),
KEY `list_id` (`list_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1

/* 2630902 rows */
CREATE TABLE `USER_MASTER_CMP` (
`user_id` mediumint(8) unsigned NOT NULL default '0',
`title` char(255) NOT NULL default '',
`state` char(64) NOT NULL default '',
`zip` char(16) NOT NULL default '',
`country` char(64) NOT NULL default '',
`global_region` char(255) NOT NULL default '',
`scf_code` char(3) NOT NULL default '',
`scrubbed` char(1) NOT NULL default '',
`employee_count` smallint(5) unsigned NOT NULL default '0',
`industry` smallint(5) unsigned NOT NULL default '0',
`budget` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`user_id`),
KEY `country` (`country`(30)),
KEY `industry` (`industry`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1

/* 7808731 rows */
CREATE TABLE `USER_SITE_CMP` (
`user_id` mediumint(8) unsigned NOT NULL default '0',
`site_id` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`user_id`,`site_id`),
KEY `user_id` (`user_id`),
KEY `site_id` (`site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1

Options: ReplyQuote


Subject
Views
Written By
Posted
Benchmarking issues
1815
October 24, 2005 12:05PM


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.