Some trouble with TTFB

icechieff · Sun Feb 14, 2021 6:30 pm

installed elasticsearch, and while it update search cache mysql have much more load, but no troubles...

: mysql2.png (18.77 KiB)

Mon Feb 15, 2021 3:22 pm

From my experience, i think there is some SQL long query... some table is big or unoptimized.

icechieff · Mon Feb 15, 2021 6:10 pm

Is there a way to help mysql with this? I'm using forum engine, not write it by myself(

Mon Feb 15, 2021 6:20 pm

SMF ?

icechieff · Mon Feb 15, 2021 6:59 pm

icechieff · Mon Feb 15, 2021 7:00 pm

may be to try set long_query to 1 sec? because there is no queries longer than 2 sec

Mon Feb 15, 2021 7:48 pm

i agree, try with 1 sec.

Code: Select all

long_query_time=1

slow_query_log
slow_query_log_file=/var/log/mysql/mariadb-slow.log

[mariadb]
slow_query_log
slow_query_log_file=/var/log/mysql/mariadb-slow.log

icechieff · Wed Feb 17, 2021 3:43 pm

found a long query, up to 2 sec some times.

icechieff · Wed Feb 17, 2021 6:38 pm

here is my slow query log

Code: Select all

/usr/sbin/mariadbd, Version: 10.5.8-MariaDB-1:10.5.8+maria~buster-log (mariadb.org binary distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time		    Id Command	Argument
# Time: 210217  8:41:09
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 20  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 2.041774  Lock_time: 0.000061  Rows_sent: 10  Rows_examined: 65545
# Rows_affected: 0  Bytes_sent: 93815
use icechieff_ipb_inno;
SET timestamp=1613540469;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Theme\class_core_global_plugins::whoweAre:61*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics`  LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(3,5,10,12,20,21,22,7,27,28,30,31,32,33,34,35,36,37,38,41,43,44,45,46,47,48,49,50,51,56,57,58,59,60,61,62,63,64,66,67,68,69,70,71,72,73,74,76,77,78,79,80,81,83,84,85,87,88,26,89,90,93,94,11,97,98,99,100,103,104,105,24,40,109,114,116,119,121,122,125,126,127,130,132,133,134,135,136,138,141,145,149,147,175,154,150,152,153,155,157,158,159,160,161,162,163,164,165,167,168,146,169,170,172,173,174,54,55,140,177,178,180,182,183,184,101,185,186,187,188,189,191,192,198) ORDER BY last_post DESC LIMIT 10;
# Time: 210217  8:47:15
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 437  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 2.108669  Lock_time: 0.000064  Rows_sent: 10  Rows_examined: 65545
# Rows_affected: 0  Bytes_sent: 93813
SET timestamp=1613540835;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Theme\class_core_global_plugins::whoweAre:61*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics`  LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(3,5,10,12,20,21,22,7,27,28,30,31,32,33,34,35,36,37,38,41,43,44,45,46,47,48,49,50,51,56,57,58,59,60,61,62,63,64,66,67,68,69,70,71,72,73,74,76,77,78,79,80,81,83,84,85,87,88,26,89,90,93,94,11,97,98,99,100,103,104,105,24,40,109,114,116,119,121,122,125,126,127,130,132,133,134,135,136,138,141,145,149,147,175,154,150,152,153,155,157,158,159,160,161,162,163,164,165,167,168,146,169,170,172,173,174,54,55,140,177,178,180,182,183,184,101,185,186,187,188,189,191,192,198) ORDER BY last_post DESC LIMIT 10;
# Time: 210217  8:47:16
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 439  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 2.098024  Lock_time: 0.000079  Rows_sent: 10  Rows_examined: 65545
# Rows_affected: 0  Bytes_sent: 93813
SET timestamp=1613540836;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Theme\class_core_global_plugins::whoweAre:61*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics`  LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(3,5,10,12,20,21,22,7,27,28,30,31,32,33,34,35,36,37,38,41,43,44,45,46,47,48,49,50,51,56,57,58,59,60,61,62,63,64,66,67,68,69,70,71,72,73,74,76,77,78,79,80,81,83,84,85,87,88,26,89,90,93,94,11,97,98,99,100,103,104,105,24,40,109,114,116,119,121,122,125,126,127,130,132,133,134,135,136,138,141,145,149,147,175,154,150,152,153,155,157,158,159,160,161,162,163,164,165,167,168,146,169,170,172,173,174,54,55,140,177,178,180,182,183,184,101,185,186,187,188,189,191,192,198) ORDER BY last_post DESC LIMIT 10;
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 440  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 2.066020  Lock_time: 0.000086  Rows_sent: 10  Rows_examined: 65545
# Rows_affected: 0  Bytes_sent: 93813
SET timestamp=1613540836;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Theme\class_core_global_plugins::whoweAre:61*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics`  LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(3,5,10,12,20,21,22,7,27,28,30,31,32,33,34,35,36,37,38,41,43,44,45,46,47,48,49,50,51,56,57,58,59,60,61,62,63,64,66,67,68,69,70,71,72,73,74,76,77,78,79,80,81,83,84,85,87,88,26,89,90,93,94,11,97,98,99,100,103,104,105,24,40,109,114,116,119,121,122,125,126,127,130,132,133,134,135,136,138,141,145,149,147,175,154,150,152,153,155,157,158,159,160,161,162,163,164,165,167,168,146,169,170,172,173,174,54,55,140,177,178,180,182,183,184,101,185,186,187,188,189,191,192,198) ORDER BY last_post DESC LIMIT 10;
# Time: 210217  8:47:34
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 469  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 2.128797  Lock_time: 0.000068  Rows_sent: 10  Rows_examined: 65545
# Rows_affected: 0  Bytes_sent: 93813
SET timestamp=1613540854;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Theme\class_core_global_plugins::whoweAre:61*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics`  LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(3,5,10,12,20,21,22,7,27,28,30,31,32,33,34,35,36,37,38,41,43,44,45,46,47,48,49,50,51,56,57,58,59,60,61,62,63,64,66,67,68,69,70,71,72,73,74,76,77,78,79,80,81,83,84,85,87,88,26,89,90,93,94,11,97,98,99,100,103,104,105,24,40,109,114,116,119,121,122,125,126,127,130,132,133,134,135,136,138,141,145,149,147,175,154,150,152,153,155,157,158,159,160,161,162,163,164,165,167,168,146,169,170,172,173,174,54,55,140,177,178,180,182,183,184,101,185,186,187,188,189,191,192,198) ORDER BY last_post DESC LIMIT 10;
/usr/sbin/mariadbd, Version: 10.5.8-MariaDB-1:10.5.8+maria~buster-log (mariadb.org binary distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time		    Id Command	Argument
# Time: 210217 19:18:47
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 20  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 1.616082  Lock_time: 0.000020  Rows_sent: 15  Rows_examined: 1415
# Rows_affected: 0  Bytes_sent: 247
use icechieff_ipb_inno;
SET timestamp=1613578727;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Content\_Item::_comments:1949*/ SELECT pid FROM `forums_posts` FORCE INDEX(`first_post`) WHERE forums_posts.topic_id=16749 AND (forums_posts.queued IN(0,2)) ORDER BY post_date asc LIMIT 1400,25;
# Time: 210217 19:18:48
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 18  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 3.296809  Lock_time: 0.000064  Rows_sent: 15620  Rows_examined: 15620
# Rows_affected: 0  Bytes_sent: 281427
SET timestamp=1613578728;
/*icechieff_ipb_inno::icechieff_ipb::IPS\plugins\whoweare\widgets\_whoweare::render:833*/ SELECT `pid`, `topic_id`, `post_date` FROM `forums_posts` WHERE author_id = 1;
/usr/sbin/mariadbd, Version: 10.5.8-MariaDB-1:10.5.8+maria~buster-log (mariadb.org binary distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time		    Id Command	Argument
/usr/sbin/mariadbd, Version: 10.5.8-MariaDB-1:10.5.8+maria~buster-log (mariadb.org binary distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time		    Id Command	Argument
# Time: 210217 19:56:32
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 8  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 1.725260  Lock_time: 0.000087  Rows_sent: 151  Rows_examined: 571
# Rows_affected: 0  Bytes_sent: 500126
use icechieff_ipb_inno;
SET timestamp=1613580992;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Node\_Model::loadIntoMemory:717*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_forums.id  LEFT JOIN `core_members` ON core_members.member_id=forums_forums.last_poster_id WHERE (sub_can_post=0 OR min_posts_view<=0) AND (( FIND_IN_SET(2,perm_view) ) OR perm_view='*' ) ORDER BY position;
/usr/sbin/mariadbd, Version: 10.5.8-MariaDB-1:10.5.8+maria~buster-log (mariadb.org binary distribution). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time		    Id Command	Argument
# Time: 210217 20:06:31
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 11  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 2.925707  Lock_time: 0.000045  Rows_sent: 15614  Rows_examined: 15614
# Rows_affected: 0  Bytes_sent: 281319
use icechieff_ipb_inno;
SET timestamp=1613581591;
/*icechieff_ipb_inno::icechieff_ipb::IPS\plugins\whoweare\widgets\_whoweare::render:833*/ SELECT `pid`, `topic_id`, `post_date` FROM `forums_posts` WHERE author_id = 1;
# Time: 210217 20:06:33
# User@Host: icechieff_ipb[icechieff_ipb] @ localhost []
# Thread_id: 11  Schema: icechieff_ipb_inno  QC_hit: No
# Query_time: 1.329583  Lock_time: 0.000089  Rows_sent: 8  Rows_examined: 16381
# Rows_affected: 0  Bytes_sent: 66985
SET timestamp=1613581593;
/*icechieff_ipb_inno::icechieff_ipb::IPS\Theme\class_core_global_plugins::whoweAre:61*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics`  LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_topics.approved < 2 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(3,5,10,12,17,20,21,22,7,27,28,30,31,32,33,34,35,36,37,38,41,43,44,45,46,47,48,49,50,51,56,57,58,59,60,61,62,63,64,66,67,68,69,70,71,72,73,74,76,77,78,79,80,81,83,84,85,87,88,26,89,90,93,94,11,97,98,99,100,102,103,104,105,24,40,109,114,116,16,119,121,122,125,126,127,130,132,133,134,135,136,138,141,145,149,147,175,154,150,152,153,155,157,158,159,160,161,162,163,164,165,167,168,146,169,170,172,173,174,54,55,140,177,178,179,180,182,183,184,101,185,186,187,188,189,191,192,198) ORDER BY last_post DESC LIMIT 8;
/usr/sbin/mariadbd, Version: 10.5.8-MariaDB-1:10.5.8+maria~buster-log (mariadb.org binary distribution). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time		    Id Command	Argument

may be some cache options in my.cnf can help me?

icechieff · Sun Feb 28, 2021 2:52 pm

just moved mysql to old ocz revodrive, and voila!:

https://webpagetest.org/result/210221_D ... 31dd9f330/

myVesta

Some trouble with TTFB

Login • Register

Tag cloud