Announcement

Collapse
No announcement yet.

Slow queries, locking up board

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • alexi
    replied
    Originally posted by pirate4x4lance View Post
    We were having this issue back in 2006. We are up to 9 million posts now, and have zero issues running Sphinx (and 4 dedicated servers lol)
    I just looked at the last reply date
    Sphinx is a god send!

    Leave a comment:


  • pirate4x4lance
    replied
    We were having this issue back in 2006. We are up to 9 million posts now, and have zero issues running Sphinx (and 4 dedicated servers lol)

    Leave a comment:


  • alexi
    replied
    I agree, default search is just not going to work with that many posts. There are several sphinx based solutions any of which would help this situation

    Leave a comment:


  • IBxAnders
    replied
    4 million post table is usually the threshold requiring a third party solution, ie. we use a custom sphinx based search engine. It's a bit too early to officially comment, but vb 4.0 structure will allow to possibly resolve the issue without ditching the default fulltext search.

    Leave a comment:


  • Zachery
    replied
    I believe that is a search query, chances are its looking at a lot of data and potetiallty causing issues.

    Leave a comment:


  • hydn
    replied
    nothing yet

    Leave a comment:


  • hydn
    replied
    Same issue here. If I find something i will be sure to post the link or info here.

    Leave a comment:


  • tpearl5
    replied
    I'm having the same problems now. Did you ever figure out what the cause was?

    Leave a comment:


  • bplein
    replied
    Nobody has any ideas why this query might be taking so long?

    I will make the changes that were suggested in the server tuning thread, but nothing is actually attacking the problem (slow query for one particular query). Just tuning the DB a little will make the problem go away for a month, but as we continue to grow, we're bound to hit it again.

    Ideas?

    Leave a comment:


  • pirate4x4lance
    replied
    Can anyone help us with this?

    Leave a comment:


  • bplein
    replied
    Code:
    atlas2 mysql # uname -a
    Linux atlas2 2.6.11-gentoo-r4 #2 SMP Tue Apr 5 18:26:54 PDT 2005 i686 Intel(R) Xeon(TM) CPU 2.80GHz GenuineIntel GNU/Linux
    atlas2 mysql #
    atlas2 mysql # ulimit -aH
    core file size        (blocks, -c) unlimited
    data seg size         (kbytes, -d) unlimited
    file size             (blocks, -f) unlimited
    max locked memory     (kbytes, -l) 32
    max memory size       (kbytes, -m) unlimited
    open files                    (-n) 1024
    pipe size          (512 bytes, -p) 8
    stack size            (kbytes, -s) unlimited
    cpu time             (seconds, -t) unlimited
    max user processes            (-u) 16382
    virtual memory        (kbytes, -v) unlimited
    
    atlas2 mysql # cat /proc/cpuinfo
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 15
    model           : 4
    model name      : Intel(R) Xeon(TM) CPU 2.80GHz
    stepping        : 1
    cpu MHz         : 2793.678
    cache size      : 1024 KB
    physical id     : 0
    siblings        : 1
    fdiv_bug        : no
    hlt_bug         : no
    f00f_bug        : no
    coma_bug        : no
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 5
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl cid cx16 xtpr
    bogomips        : 5537.79
    
    processor       : 1
    vendor_id       : GenuineIntel
    cpu family      : 15
    model           : 4
    model name      : Intel(R) Xeon(TM) CPU 2.80GHz
    stepping        : 1
    cpu MHz         : 2793.678
    cache size      : 1024 KB
    physical id     : 0
    siblings        : 1
    fdiv_bug        : no
    hlt_bug         : no
    f00f_bug        : no
    coma_bug        : no
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 5
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl cid cx16 xtpr
    bogomips        : 5586.94

    Leave a comment:


  • pirate4x4lance
    replied
    Thanks for posting this Bill. It is critical we get some help with this asap, as we have our search disabled to stay afloat. Any help with matter is appreciated!
    Last edited by pirate4x4lance; Wed 1 Mar '06, 9:31am.

    Leave a comment:


  • bplein
    replied
    Code:
    +----------------------------+-----------+
    | Variable_name              | Value     |
    +----------------------------+-----------+
    | Aborted_clients            | 291       |
    | Aborted_connects           | 5471      |
    | Binlog_cache_disk_use      | 0         |
    | Binlog_cache_use           | 0         |
    | Bytes_received             | 137527804 |
    | Bytes_sent                 | 423703462 |
    | Com_admin_commands         | 0         |
    | Com_alter_db               | 0         |
    | Com_alter_table            | 39        |
    | Com_analyze                | 0         |
    | Com_backup_table           | 0         |
    | Com_begin                  | 0         |
    | Com_change_db              | 56712     |
    | Com_change_master          | 0         |
    | Com_check                  | 0         |
    | Com_checksum               | 0         |
    | Com_commit                 | 0         |
    | Com_create_db              | 0         |
    | Com_create_function        | 0         |
    | Com_create_index           | 0         |
    | Com_create_table           | 12        |
    | Com_dealloc_sql            | 0         |
    | Com_delete                 | 1542      |
    | Com_delete_multi           | 0         |
    | Com_do                     | 0         |
    | Com_drop_db                | 0         |
    | Com_drop_function          | 0         |
    | Com_drop_index             | 0         |
    | Com_drop_table             | 0         |
    | Com_drop_user              | 0         |
    | Com_execute_sql            | 0         |
    | Com_flush                  | 0         |
    | Com_grant                  | 0         |
    | Com_ha_close               | 0         |
    | Com_ha_open                | 0         |
    | Com_ha_read                | 0         |
    | Com_help                   | 0         |
    | Com_insert                 | 22474     |
    | 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_preload_keys           | 0         |
    | Com_prepare_sql            | 0         |
    | Com_purge                  | 0         |
    | Com_purge_before_date      | 0         |
    | Com_rename_table           | 0         |
    | Com_repair                 | 0         |
    | Com_replace                | 1594      |
    | Com_replace_select         | 0         |
    | Com_reset                  | 0         |
    | Com_restore_table          | 0         |
    | Com_revoke                 | 0         |
    | Com_revoke_all             | 0         |
    | Com_rollback               | 0         |
    | Com_savepoint              | 0         |
    | Com_select                 | 146898    |
    | Com_set_option             | 5         |
    | Com_show_binlog_events     | 0         |
    | Com_show_binlogs           | 0         |
    | Com_show_charsets          | 0         |
    | Com_show_collations        | 0         |
    | Com_show_column_types      | 0         |
    | Com_show_create_db         | 0         |
    | Com_show_create_table      | 0         |
    | Com_show_databases         | 0         |
    | Com_show_errors            | 0         |
    | Com_show_fields            | 0         |
    | Com_show_grants            | 0         |
    | Com_show_innodb_status     | 0         |
    | Com_show_keys              | 0         |
    | Com_show_logs              | 0         |
    | Com_show_master_status     | 0         |
    | Com_show_ndb_status        | 0         |
    | Com_show_new_master        | 0         |
    | Com_show_open_tables       | 0         |
    | Com_show_privileges        | 0         |
    | Com_show_processlist       | 21        |
    | Com_show_slave_hosts       | 0         |
    | Com_show_slave_status      | 0         |
    | Com_show_status            | 13        |
    | Com_show_storage_engines   | 0         |
    | Com_show_tables            | 0         |
    | Com_show_variables         | 5         |
    | Com_show_warnings          | 0         |
    | Com_slave_start            | 0         |
    | Com_slave_stop             | 0         |
    | Com_stmt_close             | 0         |
    | Com_stmt_execute           | 0         |
    | Com_stmt_prepare           | 0         |
    | Com_stmt_reset             | 0         |
    | Com_stmt_send_long_data    | 0         |
    | Com_truncate               | 0         |
    | Com_unlock_tables          | 0         |
    | Com_update                 | 59728     |
    | Com_update_multi           | 0         |
    | Connections                | 62193     |
    | Created_tmp_disk_tables    | 257       |
    | Created_tmp_files          | 139       |
    | Created_tmp_tables         | 4834      |
    | Delayed_errors             | 0         |
    | Delayed_insert_threads     | 0         |
    | Delayed_writes             | 0         |
    | Flush_commands             | 1         |
    | Handler_commit             | 0         |
    | Handler_delete             | 4688      |
    | Handler_discover           | 0         |
    | Handler_read_first         | 20924     |
    | Handler_read_key           | 6940929   |
    | Handler_read_next          | 32451590  |
    | Handler_read_prev          | 202569    |
    | Handler_read_rnd           | 2388788   |
    | Handler_read_rnd_next      | 15291668  |
    | Handler_rollback           | 52490     |
    | Handler_update             | 56090     |
    | Handler_write              | 2276349   |
    | Key_blocks_not_flushed     | 0         |
    | Key_blocks_unused          | 282319    |
    | Key_blocks_used            | 181601    |
    | Key_read_requests          | 32084316  |
    | Key_reads                  | 181728    |
    | Key_write_requests         | 36219     |
    | Key_writes                 | 31272     |
    | Max_used_connections       | 651       |
    | Not_flushed_delayed_rows   | 0         |
    | Open_files                 | 1292      |
    | Open_streams               | 0         |
    | Open_tables                | 1287      |
    | Opened_tables              | 1378      |
    | Qcache_free_blocks         | 1213      |
    | Qcache_free_memory         | 56094352  |
    | Qcache_hits                | 160885    |
    | Qcache_inserts             | 144374    |
    | Qcache_lowmem_prunes       | 0         |
    | Qcache_not_cached          | 2523      |
    | Qcache_queries_in_cache    | 3413      |
    | Qcache_total_blocks        | 8265      |
    | Questions                  | 506474    |
    | Rpl_status                 | NULL      |
    | Select_full_join           | 824       |
    | Select_full_range_join     | 0         |
    | Select_range               | 34272     |
    | Select_range_check         | 0         |
    | Select_scan                | 21382     |
    | Slave_open_temp_tables     | 0         |
    | Slave_retried_transactions | 0         |
    | Slave_running              | OFF       |
    | Slow_launch_threads        | 0         |
    | Slow_queries               | 15        |
    | Sort_merge_passes          | 71        |
    | Sort_range                 | 30212     |
    | Sort_rows                  | 3324068   |
    | Sort_scan                  | 10109     |
    | Table_locks_immediate      | 433405    |
    | Table_locks_waited         | 3975      |
    | Threads_cached             | 509       |
    | Threads_connected          | 3         |
    | Threads_created            | 789       |
    | Threads_running            | 2         |
    | Uptime                     | 4732      |
    +----------------------------+-----------+
    Uptime: 4732  Threads: 3  Questions: 506475  Slow queries: 15  Opens: 1378  Flush tables: 1  Open tables: 1287  Queries per second avg: 107.032
    mysqladmin  Ver 8.40 Distrib 4.0.24, for pc-linux-gnu on i686
    Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL license
    
    Server version          4.1.18-log
    Protocol version        10
    Connection              Localhost via UNIX socket
    UNIX socket             /var/run/mysqld/mysqld.sock
    Uptime:                 1 hour 18 min 52 sec
    
    Threads: 3  Questions: 506475  Slow queries: 15  Opens: 1378  Flush tables: 1  Open tables: 1287  Queries per second avg: 107.032

    Leave a comment:


  • bplein
    replied
    Code:
    mysql> explain SELECT DISTINCT thread.threadid
        -> FROM thread AS thread
        -> INNER JOIN post AS post ON(thread.threadid = post.threadid  AND post.userid IN(21704))
        -> WHERE MATCH(post.title, post.pagetext) AGAINST ('shocks') AND thread.forumid NOT IN (0,31,54,52,64,46,27,47,41,66,65,62) AND thread.replycount >= 8 AND thread.forumid IN(26)
        -> LIMIT 200;
    +----+-------------+--------+----------+-----------------------+---------+---------+-------------------------+------+------------------------------+
    | id | select_type | table  | type     | possible_keys         | key     | key_len | ref                     | rows | Extra                        |
    +----+-------------+--------+----------+-----------------------+---------+---------+-------------------------+------+------------------------------+
    |  1 | SIMPLE      | post   | fulltext | userid,threadid,title | title   |       0 |                         |    1 | Using where; Using temporary |
    |  1 | SIMPLE      | thread | eq_ref   | PRIMARY,forumid       | PRIMARY |       4 | pirate4x4.post.threadid |    1 | Using where                  |
    +----+-------------+--------+----------+-----------------------+---------+---------+-------------------------+------+------------------------------+
    2 rows in set (0.00 sec)

    Leave a comment:


  • bplein
    replied
    Code:
    mysql> show variables;
    +---------------------------------+---------------------------------------+
    | Variable_name                   | Value                                 |
    +---------------------------------+---------------------------------------+
    | back_log                        | 50                                    |
    | basedir                         | /usr/                                 |
    | binlog_cache_size               | 32768                                 |
    | bulk_insert_buffer_size         | 33554432                              |
    | 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/charsets/            |
    | collation_connection            | latin1_swedish_ci                     |
    | collation_database              | latin1_swedish_ci                     |
    | collation_server                | latin1_swedish_ci                     |
    | concurrent_insert               | ON                                    |
    | connect_timeout                 | 10                                    |
    | 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                                  |
    | expire_logs_days                | 0                                     |
    | flush                           | OFF                                   |
    | flush_time                      | 0                                     |
    | ft_boolean_syntax               | + -><()~*:""&|                        |
    | ft_max_word_len                 | 84                                    |
    | ft_min_word_len                 | 2                                     |
    | ft_query_expansion_limit        | 20                                    |
    | ft_stopword_file                | (built-in)                            |
    | group_concat_max_len            | 1024                                  |
    | have_archive                    | NO                                    |
    | have_bdb                        | NO                                    |
    | have_blackhole_engine           | 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 | 18874368                              |
    | innodb_autoextend_increment     | 8                                     |
    | innodb_buffer_pool_awe_mem_mb   | 0                                     |
    | innodb_buffer_pool_size         | 134217728                             |
    | innodb_data_file_path           | ibdata1:10M:autoextend                |
    | innodb_data_home_dir            |                                       |
    | 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             |                                       |
    | 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_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                | 1044480                               |
    | key_buffer_size                 | 536870912                             |
    | key_cache_age_threshold         | 300                                   |
    | key_cache_block_size            | 1024                                  |
    | key_cache_division_limit        | 100                                   |
    | language                        | /usr/local/mysql/share/mysql/english/ |
    | large_files_support             | ON                                    |
    | license                         | GPL                                   |
    | local_infile                    | ON                                    |
    | locked_in_memory                | OFF                                   |
    | log                             | OFF                                   |
    | log_bin                         | OFF                                   |
    | log_error                       | /var/log/mysql/mysqld.err             |
    | log_slave_updates               | OFF                                   |
    | log_slow_queries                | ON                                    |
    | log_update                      | OFF                                   |
    | log_warnings                    | 1                                     |
    | long_query_time                 | 30                                    |
    | low_priority_updates            | OFF                                   |
    | lower_case_file_system          | OFF                                   |
    | lower_case_table_names          | 0                                     |
    | max_allowed_packet              | 67107840                              |
    | max_binlog_cache_size           | 4294967295                            |
    | max_binlog_size                 | 1073741824                            |
    | max_connect_errors              | 10                                    |
    | max_connections                 | 650                                   |
    | max_delayed_threads             | 20                                    |
    | max_error_count                 | 64                                    |
    | max_heap_table_size             | 16777216                              |
    | max_insert_delayed_threads      | 20                                    |
    | max_join_size                   | 4294967295                            |
    | max_length_for_sort_data        | 1024                                  |
    | max_relay_log_size              | 0                                     |
    | max_seeks_for_key               | 4294967295                            |
    | max_sort_length                 | 1024                                  |
    | max_tmp_tables                  | 32                                    |
    | max_user_connections            | 0                                     |
    | max_write_lock_count            | 4294967295                            |
    | myisam_data_pointer_size        | 4                                     |
    | myisam_max_extra_sort_file_size | 6442450944                            |
    | myisam_max_sort_file_size       | 6442450944                            |
    | myisam_recover_options          | OFF                                   |
    | myisam_repair_threads           | 2                                     |
    | myisam_sort_buffer_size         | 33554432                              |
    | 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                | 4260                                  |
    | pid_file                        | /var/run/mysqld/mysqld.pid            |
    | port                            | 3306                                  |
    | preload_buffer_size             | 32768                                 |
    | protocol_version                | 10                                    |
    | query_alloc_block_size          | 16384                                 |
    | query_cache_limit               | 2097152                               |
    | query_cache_min_res_unit        | 4096                                  |
    | query_cache_size                | 67108864                              |
    | query_cache_type                | ON                                    |
    | query_cache_wlock_invalidate    | OFF                                   |
    | query_prealloc_size             | 16384                                 |
    | range_alloc_block_size          | 2048                                  |
    | read_buffer_size                | 1044480                               |
    | read_only                       | OFF                                   |
    | read_rnd_buffer_size            | 16773120                              |
    | relay_log_purge                 | ON                                    |
    | relay_log_space_limit           | 0                                     |
    | rpl_recovery_rank               | 0                                     |
    | secure_auth                     | OFF                                   |
    | server_id                       | 0                                     |
    | skip_external_locking           | ON                                    |
    | skip_networking                 | OFF                                   |
    | skip_show_database              | OFF                                   |
    | slave_net_timeout               | 3600                                  |
    | slave_transaction_retries       | 0                                     |
    | slow_launch_time                | 2                                     |
    | socket                          | /var/run/mysqld/mysqld.sock           |
    | sort_buffer_size                | 2097144                               |
    | sql_mode                        |                                       |
    | sql_notes                       | ON                                    |
    | sql_warnings                    | ON                                    |
    | storage_engine                  | MyISAM                                |
    | sync_binlog                     | 0                                     |
    | sync_frm                        | ON                                    |
    | sync_replication                | 0                                     |
    | sync_replication_slave_id       | 0                                     |
    | sync_replication_timeout        | 0                                     |
    | system_time_zone                | PST                                   |
    | table_cache                     | 1800                                  |
    | table_type                      | MyISAM                                |
    | thread_cache_size               | 512                                   |
    | thread_stack                    | 196608                                |
    | time_format                     | %H:%i:%s                              |
    | time_zone                       | SYSTEM                                |
    | tmp_table_size                  | 67108864                              |
    | tmpdir                          | /tmp/mysql                            |
    | transaction_alloc_block_size    | 8192                                  |
    | transaction_prealloc_size       | 4096                                  |
    | tx_isolation                    | REPEATABLE-READ                       |
    | version                         | 4.1.18-log                            |
    | version_comment                 | Source distribution                   |
    | version_compile_machine         | i686                                  |
    | version_compile_os              | pc-linux-gnu                          |
    | wait_timeout                    | 28800                                 |
    +---------------------------------+---------------------------------------+
    185 rows in set (0.00 sec)

    Leave a comment:

widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X