AlexJ Posted December 1, 2020 Posted December 1, 2020 (edited) Anyone facing slowness while submitting post? I constantly face that issue on our site. Anything that can be done to resolve it? I have plenty of CPU and RAM available. Here is my mysqltuner results. Using MySQL 5.7. php 7.4.x and Apache. -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 6d 7h 4m 43s (38M q [71.579 qps], 954K conn, TX: 485G, RX: 37G) [--] Reads / Writes: 51% / 49% [--] Binary logging is disabled [--] Physical Memory : 62.7G [--] Max MySQL memory : 22.0G [--] Other process memory: 0B [--] Total buffers: 14.6G global + 75.2M per thread (100 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 16.7G (26.61% of installed RAM) [OK] Maximum possible memory usage: 22.0G (35.04% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (319/38M) [OK] Highest usage of available connections: 28% (28/100) [OK] Aborted connections: 0.00% (22/954913) [!!] Query cache may be disabled by default due to mutex contention. [!!] Query cache efficiency: 0.1% (20K cached / 19M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 4% (103K temp sorts / 2M sorts) [!!] Joins performed without indexes: 305485 [OK] Temporary tables created on disk: 16% (65K on disk / 406K total) [OK] Thread cache hit rate: 99% (28 created / 954K connections) [!!] Table cache hit rate: 0% (2K open / 246K opened) [OK] table_definition_cache(3000) is upper than number of tables(1308) [OK] Open file limit used: 5% (263/5K) [OK] Table locks acquired immediately: 99% (15M immediate / 15M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 26.2% (1B used / 6B cache) [OK] Key buffer size / total MyISAM indexes: 6.0G/1.9G [OK] Read Key buffer hit rate: 99.4% (2B cached / 12M reads) [!!] Write Key buffer hit rate: 3.1% (208M cached / 6M writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 8.0G/4.6G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal to 25% [OK] InnoDB buffer pool instances: 8 [--] Number of InnoDB Buffer Pool Chunk : 64 for 8 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 100.00% (11252173170 hits/ 11252300777 total) [OK] InnoDB Write log efficiency: 94.19% (48990375 hits/ 52010341 total) [OK] InnoDB log waits: 0.00% (0 waits / 3019966 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: ROW [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysql/error.log file Control error line(s) into /var/log/mysql/error.log file We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See https://dev.mysql.com/doc/internals/en/join-buffer-size.html (specially the conclusions at the bottom of the page). Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/ This is MyISAM only table_cache scalability problem, InnoDB not affected. See more details here: https://bugs.mysql.com/bug.php?id=49177 This bug already fixed in MySQL 5.7.9 and newer MySQL versions. Beware that open_files_limit (5000) variable should be greater than table_open_cache (2445) Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 2M, or use smaller result sets) join_buffer_size (> 4.0M, or always use indexes with JOINs) table_open_cache (> 2445) Edited December 1, 2020 by AlexJ
bfarber Posted December 1, 2020 Posted December 1, 2020 Do you have any custom applications or plugins? What email handling are you using? Are there a lot of followers to the topic or forum that is being posted in? Does the author who is posting have many followers? What are you using for search?
AlexJ Posted December 2, 2020 Author Posted December 2, 2020 11 hours ago, bfarber said: Do you have any custom applications or plugins? Yes. I have custom applications and plugins. I had even tried disabling plugin and applications and it didn't help. So I thought they are not causing the issue, I could be wrong. 11 hours ago, bfarber said: What email handling are you using? Are there a lot of followers to the topic or forum that is being posted in? Does the author who is posting have many followers? I am using amazon ses. 4$/month - much better then maintaining my postfix. In majority topics - we have like 5 followers or some just none. In those topics also, it takes time. 11 hours ago, bfarber said: What are you using for search? Nothing special, just MySQL stock set up. Anything special should I try? Back in the days, you had suggested to use combo of memory + MyISAM tables and now most of them are converted to InnoDB now. Anything, I should use? Thanks.
bfarber Posted December 2, 2020 Posted December 2, 2020 How slow is "slow" in this case? Are you seeing speeds comparable to what you see here? 2 seconds slower? 10 seconds slower? If the issue only occurs when submitting posts, something is happening when submitting the post that is taking a while. It may or may not be database related so mysqltuner is a good step, but might not highlight the issue. For instance, if it's taking a while to send emails through the email handler, that can slow things down. Or if you use Elasticsearch and there's a slow network connection between your server and the ES server, that can slow things down. It's somewhat hard to pinpoint though with just the information available here.
AlexWebsites Posted December 31, 2020 Posted December 31, 2020 On 12/1/2020 at 9:07 PM, AlexJ said: I am using amazon ses. 4$/month - much better then maintaining my postfix. In majority topics - we have like 5 followers or some just none. In those topics also, it takes time. This is most likely the issue if you are using SES/SMTP. IPS tasks it out if over 30 topic followers as mentioned in this topic. Lower, it sends individually which lags the posting. Solution is to move to SES API, which we are waiting for. An app is awaiting approval in marketplace from @Jon Erickson AlexJ 1
AlexJ Posted January 5, 2021 Author Posted January 5, 2021 On 12/31/2020 at 12:49 PM, AlexWebsites said: This is most likely the issue if you are using SES/SMTP. IPS tasks it out if over 30 topic followers as mentioned in this topic. Lower, it sends individually which lags the posting. Solution is to move to SES API, which we are waiting for. An app is awaiting approval in marketplace from @Jon Erickson How's the application? I just can't believe IPS doesn't add API by default. Would make a huge difference.
Recommended Posts