Percona MySQL 5.1.59-rel13.0-log Performance Issues

MySQL & MariaDB Other MySQL® Questions
1

Although I’ve been a DBA with MS-SQL for over 15 years, I can consider myself a newbie with MySQL Administration.

All tables use INNODB and are pretty large… one table have over 250 million rows while other big tables vary between 6 and 12 million rows.

I use navicat for query tasks… and linux for admin tasks.

I have a query that runs on a daily basis that eats up my memor and starts swapping to disk. This query can take over an hour to run and returns around 2.8 million records.

I’ve tried to optimize the query, all of last week, which didn’t help.

It seems that the problem lies in some MySQL setting… Am at the point where I’m just confused and nothing makes sence anymore…

Can some just tell by looking at my settings if there’s an obvious problem?
We’re on a cloud and available memory is 24GB.
HD space isn’t an issue.
I’ve tried to change a few variables, restarted MySQL, I’ve added the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
and the SQL_NO_CACHE…

Nothing worked… What I’m looking for is to lower memory consumption and speedup my query!!!
I’m pretty confortable with SQL scripts (although I don’t necessary love the EXPLAIN for analyzing - I guess it will have to do)
here are my variable settings:

auto_increment_increment 1
auto_increment_offset 1
autocommit ON
automatic_sp_privileges ON
back_log 50
basedir /usr/
big_tables OFF
binlog_cache_size 32768
binlog_direct_non_transactional_updates OFF
binlog_format STATEMENT
bulk_insert_buffer_size 8388608
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci
completion_type 0
concurrent_insert 1
connect_timeout 10
datadir /mnt/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
enable_query_response_time_stats OFF
engine_condition_pushdown ON
error_count 0
event_scheduler OFF
expand_fast_index_creation OFF
expire_logs_days 14
fast_index_creation ON
flush OFF
flush_time 0
foreign_key_checks ON
ft_boolean_syntax + -><()~*:“”&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
general_log OFF
general_log_file /mnt/mysql/int-btr-db2.log
group_concat_max_len 1024
have_community_features YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_geometry YES
have_innodb YES
have_ndbcluster NO
have_openssl DISABLED
have_partitioning YES
have_query_cache YES
have_response_time_distribution YES
have_rtree_keys YES
have_ssl DISABLED
have_symlink YES
hostname int-btr-db2.beyondtherack.com
identity 0
ignore_builtin_innodb OFF
init_connect SET NAMES utf8
init_file
init_slave
innodb_adaptive_checkpoint estimate
innodb_adaptive_flushing OFF
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 209715200
innodb_auto_lru_dump 0
innodb_autoextend_increment 32
innodb_autoinc_lock_mode 1
innodb_blocking_lru_restore OFF
innodb_buffer_pool_shm_checksum ON
innodb_buffer_pool_shm_key 0
innodb_buffer_pool_size 18014535680
innodb_change_buffering inserts
innodb_checkpoint_age_target 0
innodb_checksums ON
innodb_commit_concurrency 32
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:4G:autoextend
innodb_data_home_dir /mnt/mysql
innodb_dict_size_limit 0
innodb_doublewrite ON
innodb_doublewrite_file
innodb_enable_unsafe_group_commit 0
innodb_expand_import 0
innodb_extra_rsegments 0
innodb_extra_undoslots OFF
innodb_fake_changes OFF
innodb_fast_checksum OFF
innodb_fast_recovery OFF
innodb_fast_shutdown 1
innodb_file_format Antelope
innodb_file_format_check Barracuda
innodb_file_per_table ON
innodb_flush_log_at_trx_commit 2
innodb_flush_log_at_trx_commit_session 3
innodb_flush_method O_DIRECT
innodb_flush_neighbor_pages 1
innodb_force_recovery 0
innodb_ibuf_accel_rate 100
innodb_ibuf_active_contract 1
innodb_ibuf_max_size 9007251456
innodb_io_capacity 200
innodb_kill_idle_transaction 0
innodb_lazy_drop_table 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_block_size 512
innodb_log_buffer_size 33554432
innodb_log_file_size 1073741824
innodb_log_files_in_group 2
innodb_log_group_home_dir /mnt/mysql_logfile
innodb_max_dirty_pages_pct 75
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_old_blocks_pct 37
innodb_old_blocks_time 0
innodb_open_files 300
innodb_overwrite_relay_log_info OFF
innodb_page_size 16384
innodb_pass_corrupt_table 0
innodb_random_read_ahead OFF
innodb_read_ahead linear
innodb_read_ahead_threshold 56
innodb_read_io_threads 4
innodb_recovery_stats OFF
innodb_replication_delay 0
innodb_rollback_on_timeout OFF
innodb_show_locks_held 10
innodb_show_verbose_locks 0
innodb_spin_wait_delay 6
innodb_stats_auto_update 1
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages 8
innodb_stats_update_need_lock 1
innodb_strict_mode OFF
innodb_support_xa OFF
innodb_sync_spin_loops 30
innodb_table_locks ON
innodb_thread_concurrency 32
innodb_thread_concurrency_timer_based OFF
innodb_thread_sleep_delay 10000
innodb_use_purge_thread 1
innodb_use_sys_malloc ON
innodb_use_sys_stats_table OFF
innodb_version 1.0.17-13.0
innodb_write_io_threads 4
insert_id 0
interactive_timeout 28800
join_buffer_size 131072
keep_files_on_create OFF
key_buffer_size 1073741824
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/share/mysql/english/
large_files_support ON
large_page_size 0
large_pages OFF
last_insert_id 0
lc_time_names en_US
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin ON
log_bin_trust_function_creators OFF
log_bin_trust_routine_creators OFF
log_error /mnt/logs/mysql/mysql_error.log
log_output FILE
log_queries_not_using_indexes ON
log_slave_updates ON
log_slow_admin_statements ON
log_slow_filter
log_slow_queries ON
log_slow_rate_limit 1
log_slow_slave_statements OFF
log_slow_sp_statements ON
log_slow_timestamp_every OFF
log_slow_verbosity microtime
log_warnings 2
long_query_time 1.000000
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 16777216
max_binlog_cache_size 18446744073709547520
max_binlog_size 1073741824
max_connect_errors 10
max_connections 10000
max_delayed_threads 20
max_error_count 64
max_heap_table_size 33554432
max_insert_delayed_threads 20
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_long_data_size 16777216
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 18446744073709551615
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 1024
max_user_connections 0
max_write_lock_count 18446744073709551615
min_examined_row_limit 0
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036853727232
myisam_mmap_size 18446744073709551615
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 4194304
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old OFF
old_alter_table OFF
old_passwords OFF
open_files_limit 50000
optimizer_fix ON
optimizer_prune_level 1
optimizer_search_depth 62
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=o n,index_merge_intersection=on
pid_file /mnt/mysql/int-btr-db2.beyondtherack.com.pid
plugin_dir /usr/lib64/mysql/plugin
port 3306
preload_buffer_size 32768
profiling ON
profiling_history_size 15
profiling_server OFF
profiling_use_getrusage OFF
protocol_version 10
pseudo_thread_id 59
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 268435456
query_cache_strip_comments OFF
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
query_response_time_range_base 10
rand_seed1
rand_seed2
range_alloc_block_size 4096
read_buffer_size 262144
read_only OFF
read_rnd_buffer_size 4194304
relay_log /mnt/mysql_relaylogs/int-btr-db03_relay_bin
relay_log_index
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_space_limit 10737418240
report_host int-btr-db03
report_password
report_port 3306
report_user
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 12962
skip_external_locking ON
skip_name_resolve ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_exec_mode STRICT
slave_load_tmpdir /tmp
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
slow_query_log ON
slow_query_log_file /mnt/logs/mysql/mysql_slow.log
slow_query_log_microseconds_timestamp OFF
socket /mnt/mysql/mysql.sock
sort_buffer_size 4194304
sql_auto_is_null ON
sql_big_selects ON
sql_big_tables OFF
sql_buffer_result OFF
sql_log_bin ON
sql_log_off OFF
sql_log_update ON
sql_low_priority_updates OFF
sql_max_join_size 18446744073709551615
sql_mode
sql_notes ON
sql_quote_show_create ON
sql_safe_updates OFF
sql_select_limit 18446744073709551615
sql_slave_skip_counter
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine MyISAM
suppress_log_warning_1592 OFF
sync_binlog 0
sync_frm ON
system_time_zone EST
table_definition_cache 256
table_lock_wait_timeout 50
table_open_cache 2048
table_type MyISAM
thread_cache_size 300
thread_handling one-thread-per-connection
thread_stack 131072
thread_statistics OFF
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
timestamp 1336051628
tmp_table_size 33554432
tmpdir /tmp
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
unique_checks ON
updatable_views_with_limit YES
use_global_log_slow_control none
use_global_long_query_time OFF
userstat_running OFF
version 5.1.59-rel13.0-log
version_comment Percona Server (GPL), 13.0, Revision 325
version_compile_machine x86_64
version_compile_os unknown-linux-gnu
wait_timeout 28800
warning_count 0

Any help would be greatly appreciated!!!

Thank you
JG

2

I don’t see any query or SHOW CREATE TABLE output. You seem to think there is a single set of parameters that is best for all queries, but sometimes much more can be gained from rewriting a query.

You could start with [URL]MySQL Tools and Management Software to Perform System Tasks by Percona

3

I’ve downloaded the Percona ToolKit and read many, many articles to try to tweak the server but obviously, I’m missing something…

It’s a complex query… below is the query followed by some explanations that can help understand.

SELECT
u.uid,
u.email,
SHA1(CONCAT(u.uid, u.email, u.password)) AS hash,
ui.country
FROM users u
JOIN users_info ui ON ui.uid = u.uid
JOIN users_dates ud ON ud.uid = u.uid
WHERE
u.confirm_flag = 1 AND
u.status = ‘ACTIVE’ AND
ui.country IN (‘US’, ‘CA’)
AND (
ud.last_login >= NOW() - INTERVAL 270 DAY OR
ud.date_joined >= NOW() - INTERVAL 45 DAY OR
EXISTS(
SELECT *
FROM stats.users_counters
WHERE
uid = u.uid AND
type = 4 AND posted_on >= NOW() - INTERVAL 90 DAY) OR

EXISTS(
SELECT *
FROM order_headers
WHERE uid = u.uid
)
)
AND (
FIND_IN_SET(‘dailyreminders’, ui.preferences_list)
) AND (
SELECT send_mail
FROM users_mailing
WHERE uid = u.uid
ORDER BY posted_on DESC
LIMIT 1
) = 1

users_counters table contains 250 million records.
and looks like this:
CREATE TABLE users_counters (
uid int(10) unsigned NOT NULL,
type tinyint(3) unsigned NOT NULL,
posted_on datetime NOT NULL,
KEY uid (uid,type,posted_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

the users table has appros. 7 million records and looks like this:
CREATE TABLE users (
uid int(10) unsigned NOT NULL AUTO_INCREMENT,
email varchar(50) NOT NULL,
password varchar(40) NOT NULL,
confirm_flag tinyint(3) unsigned NOT NULL DEFAULT ‘0’,
status enum(‘ACTIVE’,‘INACTIVE’) NOT NULL DEFAULT ‘ACTIVE’,
PRIMARY KEY (uid),
KEY email_ndx (email(15)),
KEY idx_confirm_flag_Status_uid (confirm_flag,status,uid)
) ENGINE=InnoDB AUTO_INCREMENT=6729960 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 */

the users_info tale also contains 7 million records
CREATE TABLE users_info (
uid int(10) unsigned NOT NULL,
name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
country char(2) NOT NULL,
gender enum(‘Male’,‘Female’) NOT NULL DEFAULT ‘Male’,
mobile varchar(15) DEFAULT NULL,
postal varchar(15) DEFAULT NULL,
password_hint varchar(50) DEFAULT NULL,
personal_code varchar(25) NOT NULL,
invitation_code varchar(25) DEFAULT NULL,
reference varchar(100) DEFAULT NULL,
cobrand_keyword varchar(25) DEFAULT NULL,
preferences_list varchar(250) DEFAULT NULL,
class_list varchar(250) DEFAULT NULL,
notes varchar(50) DEFAULT NULL,
queuestatus int(1) unsigned DEFAULT NULL,
update_ns int(1) unsigned DEFAULT NULL,
nsid int(10) unsigned DEFAULT NULL,
address1 varchar(100) DEFAULT NULL,
address2 varchar(100) DEFAULT NULL,
city varchar(100) DEFAULT NULL,
region char(2) DEFAULT NULL,
birthdate date DEFAULT NULL,
register_ipaddr int(11) DEFAULT NULL,
register_useragent varchar(500) DEFAULT NULL,
PRIMARY KEY (uid),
KEY personal_code_ndx (personal_code(12)),
KEY invitation_code_ndx (invitation_code(12)),
KEY preferences_list (preferences_list),
KEY idx_uid_country_prefList (uid,country,preferences_list)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 */

the users_dates also contain 7 million records
CREATE TABLE users_dates (
uid int(10) unsigned NOT NULL,
date_joined datetime DEFAULT NULL,
last_login datetime DEFAULT NULL,
last_purchase datetime DEFAULT NULL,
PRIMARY KEY (uid),
KEY idx_uid_joined_login_dates (uid,date_joined,last_login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 */

Order_header contains 2.2 Million records
CREATE TABLE order_headers (
order_id int(10) unsigned NOT NULL AUTO_INCREMENT,
payment_ref_id varchar(20) DEFAULT NULL,
settle_ref_id varchar(20) DEFAULT NULL,
refund_ref_id varchar(20) DEFAULT NULL,
settle_date datetime DEFAULT NULL,
cancel_date datetime DEFAULT NULL,
refund_date datetime DEFAULT NULL,
refund_reason text,
cancel_attempts tinyint(3) unsigned NOT NULL DEFAULT ‘0’,
order_type varchar(20) NOT NULL DEFAULT ‘WEBORDER’,
uid int(10) unsigned NOT NULL,
email varchar(50) DEFAULT NULL,
order_status varchar(20) DEFAULT ‘ACTIVE’,
order_date datetime NOT NULL,
billing_card_type varchar(2) DEFAULT NULL,
billing_card_lastdigits varchar(5) DEFAULT NULL,
billing_name varchar(75) DEFAULT NULL,
billing_address1 varchar(75) DEFAULT NULL,
billing_address2 varchar(75) DEFAULT NULL,
billing_city varchar(50) DEFAULT NULL,
billing_state varchar(2) DEFAULT NULL,
billing_country varchar(2) DEFAULT NULL,
billing_postal varchar(12) DEFAULT NULL,
billing_phone varchar(20) DEFAULT NULL,
shipping_name varchar(75) DEFAULT NULL,
shipping_address1 varchar(75) DEFAULT NULL,
shipping_address2 varchar(75) DEFAULT NULL,
shipping_city varchar(50) DEFAULT NULL,
shipping_state varchar(2) DEFAULT NULL,
shipping_country varchar(2) DEFAULT NULL,
shipping_postal varchar(12) DEFAULT NULL,
shipping_phone varchar(20) DEFAULT NULL,
total_order_value_usd float DEFAULT ‘0’,
total_shipping_usd float DEFAULT ‘0’,
total_tax1_usd float DEFAULT ‘0’,
total_tax2_usd float DEFAULT ‘0’,
total_order_value_cad float DEFAULT ‘0’,
total_shipping_cad float DEFAULT ‘0’,
total_tax1_cad float DEFAULT ‘0’,
total_tax2_cad float DEFAULT ‘0’,
shipping_tax_group smallint(5) unsigned NOT NULL,
total_credit_applied float DEFAULT ‘0’,
total_discount_usd float DEFAULT NULL,
total_discount_cad float DEFAULT NULL,
ordered_currency varchar(3) DEFAULT NULL,
ordered_currency_rate float DEFAULT ‘1’,
order_notes varchar(100) DEFAULT NULL,
shipping_date datetime DEFAULT NULL,
tracking_number varchar(50) DEFAULT NULL,
reference_date datetime DEFAULT NULL,
reference varchar(100) DEFAULT NULL,
recipient_email varchar(50) DEFAULT NULL,
gift_flag int(1) unsigned DEFAULT NULL,
credit_total_referral decimal(12,2) DEFAULT ‘0.00’,
credit_total_transition decimal(12,2) DEFAULT ‘0.00’,
credit_total decimal(12,2) DEFAULT ‘0.00’,
credit_total_promo decimal(12,2) DEFAULT ‘0.00’,
credit_total_preacquisition decimal(12,2) DEFAULT ‘0.00’,
queuestatus int(1) unsigned DEFAULT NULL,
update_ns int(1) unsigned DEFAULT NULL,
nsid int(10) unsigned DEFAULT NULL,
merchant_ref_id bigint(20) unsigned DEFAULT NULL,
code_avs char(1) DEFAULT NULL,
code_cvd char(1) DEFAULT NULL,
ip_address int(10) unsigned DEFAULT NULL,
avs_passed tinyint(1) unsigned DEFAULT NULL,
avs_message varchar(255) DEFAULT NULL,
user_agent varchar(500) DEFAULT NULL,
PRIMARY KEY (order_id,order_type),
KEY order_id (order_id,uid),
KEY order_type (uid,order_type),
KEY cancel_attempts (cancel_attempts) USING BTREE,
KEY order_date (order_date) USING BTREE,
KEY payment_ref_id (payment_ref_id) USING BTREE,
KEY refund_ref_id (refund_ref_id) USING BTREE,
KEY refund_date (refund_date) USING BTREE,
KEY settle_ref_id (settle_ref_id) USING BTREE,
KEY settle_date (settle_date) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10390814 DEFAULT CHARSET=utf8

users_mailing has 12 million records
CREATE TABLE users_mailing (
uid int(10) unsigned NOT NULL,
posted_on datetime NOT NULL,
send_mail tinyint(3) unsigned NOT NULL DEFAULT ‘1’,
update_source tinyint(3) unsigned NOT NULL DEFAULT ‘0’,
KEY uid (uid,posted_on),
KEY idx_uid_posted_sendMail (uid,posted_on,send_mail)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 */

the EXPLAIN looks like this…
1 PRIMARY u ref PRIMARY,idx_confirm_flag_Status_uid idx_confirm_flag_Status_uid 2 const,const 3366992 Using where
1 PRIMARY ui ref idx_uid_country_prefList idx_uid_country_prefList 4 1965917_btr.u.uid 1 Using where; Using index
1 PRIMARY ud ref idx_uid_joined_login_dates idx_uid_joined_login_dates 4 1965917_btr.ui.uid 1 Using where; Using index
4 DEPENDENT SUBQUERY users_mailing ref uid,idx_uid_posted_sendMail uid 4 1965917_btr.u.uid 1 Using where; Using filesort
3 DEPENDENT SUBQUERY order_headers ref order_type order_type 4 1965917_btr.u.uid 1 Using index
2 DEPENDENT SUBQUERY users_counters ref uid uid 5 1965917_btr.u.uid,const 12 Using where; Using index

What I’ve tried with the original query is the following…

Straight_joins
added indexes
used the use index hint
used left joins instead of sub queries
broke down the query using tekporary tables with indexes

Now I’m working on pre summarizing the big tables on a nightly basis, although I’m not sure if it will be accepted (if it is time sensitive material)

That’s it… any help would be greatly appreciated.

Thank you
JG