Announcement

Announcement Module
Collapse
No announcement yet.

Percona MySQL 5.1.59-rel13.0-log Performance Issues

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Percona MySQL 5.1.59-rel13.0-log Performance Issues

    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_so rt_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 https://tools.percona.com/

    Comment


    • #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

      Comment


      • #4
        I see one weird thing:

        WHERE (SELECT send_mail FROM users_mailing WHERE uid = u.uid ORDER BY posted_on DESC LIMIT 1) = 1

        uses the index on (`uid`,`posted_on`), but it only uses the part on `uid` and resorts to 'using filesort'. I don't see why it does not use the full index.

        Changing the join order could be beneficial, e.g. if ui.country IN ('US', 'CA') filters more rows than (u.confirm_flag = 1 AND u.status = 'ACTIVE').

        If your result set is large, it may be beneficial to run your query with SELECT uid FROM [rest of query], so the ORDER BY is performed on a smaller set, and then join the other columns.

        Comment


        • #5
          I tried a few things and it didn't change the issue (including your suggestions)...
          I thank you for your help...

          I'm now working on the issue from another angle. Why are the tables so big and can we clean them up, archive them or summarize them...
          Which would simplify the query and help Performance when using those huge tables.

          Thank you
          JG

          Comment


          • #6
            Sub-queries can very often give poor performance and the solution is many times to remove the sub-query from the WHERE and instead use the sub-query to create a derived table that you join with.

            The difference is that a sub-query has to be evaluated once for each record in the outer query while the derived table is only created once but might become very large if the group by doesn't collapse it enough, so you will have to try which way is fastest.


            So I tried to rewrite the query, but it's completely untested and I'm not sure I got everything right, so you will have to check if it does what you want to (and if it indeed is faster ):

            SELECTu.uid,u.email,SHA1(CONCAT(u.uid, u.email, u.password)) AS `hash`,ui.countryFROM users uJOIN users_info ui ON ui.uid = u.uid AND ui.country IN ('US', 'CA') AND FIND_IN_SET('dailyreminders', ui.preferences_list)INNER JOIN ( SELECT um1.uid FROM users_mailing um1 INNER JOIN (SELECT uid, MAX(posted_on) AS maxPostedOn FROM users_mailing) um2 ON um1.uid = um2.uid AND um1.posted_on = um2.maxPostedOn WHERE um1.send_mail = 1 ) umsm ON u.uid = umsm.uidINNER JOIN ( SELECT ud.uid FROM users_dates ud WHERE ud.last_login >= NOW() - INTERVAL 270 DAY OR ud.date_joined >= NOW() - INTERVAL 45 DAY UNION SELECT uc.uid FROM stats.users_counters WHERE type = 4 AND posted_on >= NOW() - INTERVAL 90 DAY UNION SELECT oh.uid FROM order_headers ) lastActiveSelection ON u.uid = lastActiveSelection.uidWHERE u.confirm_flag = 1 AND u.status = 'ACTIVE'


            Try it out and I'll keep my fingers crossed

            Comment

            Working...
            X