Announcement

Collapse
No announcement yet.

Slow queries, locking up board

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

  • Slow queries, locking up board

    I administer a site with about 4.2 million posts, over 350,000 threads, and typically 1200-1400 users online (15 minute cookie).

    This query (from a search) is killing us:

    PHP Code:
    [email protected]: xxxxx[xxxxx] @  [192.168.xxx.xxx]
    # Query_time: 38  Lock_time: 0  Rows_sent: 3  Rows_examined: 24856
    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.titlepost.pagetextAGAINST ('shocks') AND thread.forumid NOT IN (0,31,54,52,64,46,27,47,41,66,65,62) AND thread.replycount >= AND thread.forumid IN(26)
    LIMIT 200
    This type of query often results in "Copying to tmp table". I don't have any evidence that this is going to disk, but it's very slow, may take several minutes. We are using fulltext search, and when this copy to tmp occurs, all new posts are blocked, and it cascades to the point where it may lock up the site for posting for a minute or more.

    The database is MySQL v 4.1.18, running on a dedicated server, dual xeon 2.8 with 2GB RAM. Gentoo Linux. RAID-1 10Krpm disks. We were fine until we upgraded to 3.5, really seemed to hit the wall at 3.5.2, but we're not certain.

    my.cnf:
    PHP Code:
    [mysqld]
    ft_min_word_len 2
    user            
    mysql
    pid
    -file        = /var/run/mysqld/mysqld.pid
    socket          
    = /var/run/mysqld/mysqld.sock
    log
    -error       = /var/log/mysql/mysqld.err
    basedir         
    = /usr
    datadir         
    = /var/lib/mysql
    tmpdir          
    = /tmp/mysql
    language        
    = /usr/local/mysql/share/mysql/english
    skip
    -locking
    skip
    -name-resolve


    back_log 
    50
    bind
    -address=192.168.0.100

    max_connections 
    650
    key_buffer 
    16M
    myisam_sort_buffer_size 
    64M
    join_buffer_size 
    1M
    read_buffer_size 
    1M
    sort_buffer_size 
    2M
    table_cache 
    1800
    thread_cache_size 
    512
    wait_timeout 
    180
    connect_timeout 
    10
    tmp_table_size 
    64M
    max_allowed_packet 
    64M
    bulk_insert_buffer_size 
    8M
    thread_concurrency 
    4
    query_cache_limit 
    2M
    query_cache_size 
    64M
    query_cache_type 
    1
    query_prealloc_size 
    16384
    query_alloc_block_size 
    16384





    log_slow_queries
    long_query_time 
    30

    key_buffer 
    512M
    read_rnd_buffer_size 
    16M
    bulk_insert_buffer_size 
    32M
    myisam_sort_buffer_size 
    32M

    myisam_max_sort_file_size 
    6G
    myisam_max_extra_sort_file_size 
    6G

    myisam_repair_threads 
    2



    skip
    -bdb


    innodb_additional_mem_pool_size 
    18M
    innodb_buffer_pool_size 
    128M

    innodb_data_file_path 
    ibdata1:10M:autoextend
    innodb_thread_concurrency 
    8

    innodb_flush_log_at_trx_commit 
    1

    innodb_log_buffer_size 
    8M

    innodb_log_file_size 
    100M
    innodb_log_files_in_group 
    2

    innodb_max_dirty_pages_pct 
    90


    innodb_lock_wait_timeout 
    50 
    I will post more later (gotta run to work), but for now, I'd like any pointers or tips for the right way to debug this.

    Thanks.
    Last edited by bplein; Wed 1 Mar '06, 7:20am. Reason: edit title
    Administrator, http://Pirate4x4.com

  • #2
    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)
    Administrator, http://Pirate4x4.com

    Comment


    • #3
      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)
      Administrator, http://Pirate4x4.com

      Comment


      • #4
        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
        Administrator, http://Pirate4x4.com

        Comment


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

          Comment


          • #6
            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
            Administrator, http://Pirate4x4.com

            Comment


            • #7
              Can anyone help us with this?

              Comment


              • #8
                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?
                Administrator, http://Pirate4x4.com

                Comment


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

                  Capture more registrations - Advanced Guest Posting & Registration
                  Cell Phone Forums | Nikonites

                  Comment


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

                    Comment


                    • #11
                      nothing yet

                      Comment


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

                        Comment


                        • #13
                          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.
                          anders | vbulletin team | check out the new vbulletin facebook app
                          Proudly vBulletin'ing since 2001
                          Please be my friend!
                          http://www.twitter.com/inetskunkworks
                          vBulletin Performance Articles:
                          Click here to read

                          Comment


                          • #14
                            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
                            Webmaster in charge of technical stuff and taking out the trash. www.disboards.com, www.wdwinfo.com
                            www.dreamsunlimitedtravel.com and a few others I am forgetting!

                            Comment


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

                              Comment

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