Announcement

Collapse
No announcement yet.

Posting private messages really slow

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

  • tortyNet
    replied
    Seems to be a channel listing query. They tend to take some time if not already in cache. :-)

    Leave a comment:


  • tortyNet
    replied
    15 PrivateMessage
    29 Channel

    Result to your query:

    Code:
    35    105    29    1479934935    NULL    1    NULL    admin    <i><small>Moderatoren: dieMeike, Nudelsuppe</small></i>    Schule & Job    Schule &amp; Job    17    schule-job    2    0    1479934935    1527629634    17364315    die-Aileen    333888        10343    0    206083    672        1    42    3    NULL    1480597095    0    2880100701    NULL    1    0    0    0    0    0    NULL    NULL    1    1    0    1    1    2    1    138        0    0
    A Channel Result

    Leave a comment:


  • Wayne Luke
    replied
    The query above looks like a search query. It is excluding Attachments (contenttypeid=15) and Reports (contenttypeid=29). Though the contenttypeid values can vary per installation.

    What does "select * from node where nodeid=35" return?

    Leave a comment:


  • tortyNet
    replied
    What are the INNODB variables set to for MySQL?
    All INNODB Variables? Ok .. here you go

    Code:
     [TABLE="align: center, border: 0, cellpadding: 4, cellspacing: 0"]
    [TR]
    [TD="align: left"]innodb_adaptive_flushing[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_adaptive_flushing_lwm[/TD]
     			[TD]10[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_adaptive_hash_index[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_adaptive_max_sleep_delay[/TD]
     			[TD]150000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_additional_mem_pool_size[/TD]
     			[TD]8388608[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_api_bk_commit_interval[/TD]
     			[TD]5[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_api_disable_rowlock[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_api_enable_binlog[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_api_enable_mdl[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_api_trx_level[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_aurora_enable_auto_akp[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_autoextend_increment[/TD]
     			[TD]64[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_autoinc_lock_mode[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_dump_at_shutdown[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_dump_now[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_filename[/TD]
     			[TD]ib_buffer_pool[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_instances[/TD]
     			[TD]8[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_load_abort[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_load_at_startup[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_load_now[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_buffer_pool_size[/TD]
     			[TD]20090716160[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_change_buffer_max_size[/TD]
     			[TD]25[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_change_buffering[/TD]
     			[TD]none[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_checksum_algorithm[/TD]
     			[TD]none[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_checksums[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_cmp_per_index_enabled[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_commit_concurrency[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_compression_failure_threshold_pct[/TD]
     			[TD]5[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_compression_level[/TD]
     			[TD]6[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_compression_pad_pct_max[/TD]
     			[TD]50[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_concurrency_tickets[/TD]
     			[TD]5000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_data_file_path[/TD]
     			[TD]ibdata1:12M:autoextend[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_data_home_dir[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_disable_sort_file_cache[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_doublewrite[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_fast_shutdown[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_file_format[/TD]
     			[TD]Antelope[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_file_format_check[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_file_format_max[/TD]
     			[TD]Barracuda[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_file_per_table[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_flush_log_at_timeout[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_flush_log_at_trx_commit[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_flush_method[/TD]
     			[TD]O_DIRECT[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_flush_neighbors[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_flushing_avg_loops[/TD]
     			[TD]30[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_force_load_corrupted[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_force_recovery[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_aux_table[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_cache_size[/TD]
     			[TD]8000000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_enable_diag_print[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_enable_stopword[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_max_token_size[/TD]
     			[TD]84[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_min_token_size[/TD]
     			[TD]3[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_num_word_optimize[/TD]
     			[TD]2000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_result_cache_limit[/TD]
     			[TD]2000000000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_server_stopword_table[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_sort_pll_degree[/TD]
     			[TD]2[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_total_cache_size[/TD]
     			[TD]640000000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_ft_user_stopword_table[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_io_capacity[/TD]
     			[TD]200[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_io_capacity_max[/TD]
     			[TD]2000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_large_prefix[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_lock_wait_timeout[/TD]
     			[TD]50[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_locks_unsafe_for_binlog[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_log_buffer_size[/TD]
     			[TD]8388608[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_log_file_size[/TD]
     			[TD]50331648[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_log_files_in_group[/TD]
     			[TD]2[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_log_group_home_dir[/TD]
     			[TD]./[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_lru_scan_depth[/TD]
     			[TD]1024[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_max_dirty_pages_pct[/TD]
     			[TD]75[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_max_dirty_pages_pct_lwm[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_max_purge_lag[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_max_purge_lag_delay[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_mirrored_log_groups[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_monitor_disable[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_monitor_enable[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_monitor_reset[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_monitor_reset_all[/TD]
     			[TD] [/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_old_blocks_pct[/TD]
     			[TD]37[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_old_blocks_time[/TD]
     			[TD]1000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_online_alter_log_max_size[/TD]
     			[TD]134217728[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_open_files[/TD]
     			[TD]6000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_optimize_fulltext_only[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_page_size[/TD]
     			[TD]16384[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_print_all_deadlocks[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_purge_batch_size[/TD]
     			[TD]300[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_purge_threads[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_random_read_ahead[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_read_ahead_threshold[/TD]
     			[TD]56[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_read_io_threads[/TD]
     			[TD]8[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_read_only[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_replication_delay[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_rollback_on_timeout[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_rollback_segments[/TD]
     			[TD]128[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_shared_buffer_pool_uses_huge_pages[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_sort_buffer_size[/TD]
     			[TD]1048576[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_spin_wait_delay[/TD]
     			[TD]6[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_stats_auto_recalc[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_stats_method[/TD]
     			[TD]nulls_equal[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_stats_on_metadata[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_stats_persistent[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_stats_persistent_sample_pages[/TD]
     			[TD]20[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_stats_sample_pages[/TD]
     			[TD]8[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_stats_transient_sample_pages[/TD]
     			[TD]8[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_strict_mode[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_support_xa[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_sync_array_size[/TD]
     			[TD]1[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_sync_spin_loops[/TD]
     			[TD]30[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_table_locks[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_thread_concurrency[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_thread_sleep_delay[/TD]
     			[TD]10000[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_undo_directory[/TD]
     			[TD].[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_undo_logs[/TD]
     			[TD]128[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_undo_tablespaces[/TD]
     			[TD]0[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_use_native_aio[/TD]
     			[TD]OFF[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_use_sys_malloc[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_version[/TD]
     			[TD]1.2.10[/TD]
     		[/TR]
    [TR]
    [TD="align: left"]innodb_write_io_threads[/TD]
     			[TD]4[/TD]
     		[/TR]
    [/TABLE]
    Code:
     [TABLE="align: center, border: 0, cellpadding: 4, cellspacing: 0"]
    [TR]
    [TD="align: left"]lock_wait_timeout[/TD]
     			[TD]31536000[/TD]
     		[/TR]
    [/TABLE]
    Are you storing your INNODB tables in a single compressed archive file (the default value on MySQL) or in a single file per table?
    Is that this one?
    Code:
     [TABLE="align: center, border: 0, cellpadding: 4, cellspacing: 0"]
    [TR]
    [TD="align: left"]innodb_file_per_table[/TD]
     			[TD]ON[/TD]
     		[/TR]
    [/TABLE]
    Thanks for your patience and help

    Leave a comment:


  • Wayne Luke
    replied
    What are the INNODB variables set to for MySQL?

    What is the lock_wait_timeout value set to in MySQL?

    Are you storing your INNODB tables in a single compressed archive file (the default value on MySQL) or in a single file per table?

    You can see your MySQL variables in the AdminCP under Maintenance -> Diagnostics -> System Information.

    Leave a comment:


  • tortyNet
    replied
    Originally posted by Wayne Luke View Post
    Do you have the MySQL Slow Query Log turned on?

    Your waterfall above shows it is waiting for the first byte. This indicates something in the processing is slowing things down.
    Yes we do ... and we do have some with queries like this:

    Code:
    SELECT DISTINCT node.starter AS nodeid
                FROM node as node
                LEFT JOIN node AS starter ON starter.nodeid = IF(node.starter = 0, node.nodeid, node.starter)
                WHERE starter.sticky <> '1' AND
                    node.contenttypeid <> 29 AND
                    node.parentid = 35 AND
                    node.showpublished > 0 AND
                    node.showapproved  > 0 AND
                    node.viewperms = 2 AND
                    node.inlist = 1 AND
                    node.contenttypeid <> 15 AND
                    node.protected <> '1'
    
                ORDER BY starter.lastcontent DESC,node.nodeid ASC
    
    
    /**getSearchResults**/
    ContentTypeID don't point to pms ... However the seem to bulk up from time to time

    Query Time 10-15 Seconds
    Rows sent 14k-20k
    Rows examined 40k-90k

    As we don't have DB and Webserver on the same machine sending 14k of results could take a second or two.

    Maybe a limit missing?
    Last edited by tortyNet; Mon 28 May '18, 11:21am.

    Leave a comment:


  • tortyNet
    replied
    No recent start .. it was always slow. We came from vb4 and moved to 5 like 1.5 years ago. It got better with moving the whole site to memcache as caching instead of database caching.

    I'm aware that pms are mainly uncached by design so they always hit the database instead of caches but the difference between a "normal post" and a pm seem to become larger lately. So I thought maybe we run into somekind of limit, we have users with 5-8k PMs in their archive and they are annoyed that sending a single pm takes 10 and more seconds in the evening (peak load for the site) while a forum post only talks half a second at the same time.


    Leave a comment:


  • Wayne Luke
    replied
    Do you have the MySQL Slow Query Log turned on?

    Your waterfall above shows it is waiting for the first byte. This indicates something in the processing is slowing things down.

    Leave a comment:


  • BirdOPrey5
    replied
    Did this issue start recently? Has anything changed around the time it started? Or has it been getting worse? Did it ever work as fast as you'd expect?

    Leave a comment:


  • tortyNet
    replied
    No real speedup. Seems like the more traffic we have the slower it gets (while posting to Forum is still fast, even edit and re-save is fast)
    I will try to find some time to dig into the queries that get triggered while posting a new private message (there are quite some permission checks etc and more tables involved than in a usual forum post).

    Thanks for your help

    Leave a comment:


  • tortyNet
    replied
    We are using memcache as primary cache so cache and cacheevent are empty. I cleared the searchlog .. lets see if this helps

    Leave a comment:


  • Mark.B
    replied
    It may be worth emptying these tables just to see if it helps. :

    Code:
    DELETE FROM cache
    DELETE FROM cacheevent
    DELETE FROM searchlog

    Leave a comment:


  • tortyNet
    replied
    Originally posted by Joe D. View Post
    Out of curiosity if you go to Admin CP -> Maintenance -> Diagnostics, and run the email diagnostic, does it send quickly? Do you get any errors there?
    We thought about that already. Just tested again .. we are using a smtp relay. Works nearly instant so no server delay here .. also no errors obviously. But thanks for the help :-)

    Leave a comment:


  • BirdOPrey5
    replied
    Out of curiosity if you go to Admin CP -> Maintenance -> Diagnostics, and run the email diagnostic, does it send quickly? Do you get any errors there?

    Leave a comment:


  • tortyNet
    replied
    Regarding the pure size of 30 Gig Database and the GDPR issues we would run into, that's not really an option for us :-)

    Leave a comment:

Related Topics

Collapse

Working...
X