fantasts

Forum Replies Created

Viewing 7 replies - 1 through 7 (of 7 total)
  • Forum: Fixing WordPress
    In reply to: The paging is very slow under a large number of articles.
    Thread Starter fantasts

    (@fantasts)

    7 years, 2 months ago

    No changes, I have tested it in the experimental environment.
    The website you listed cleverly uses the count calculations that were avoided on the previous and next pages. And optimized the wordpress database query.
    You may not be a technician, you can see a simple example.

    Select * from table limit 400000, 20
    It takes 3.229 seconds
    Select * from table limit 800000, 20
    It took 37.44 seconds

    The above is the paging method currently used by wordpress.
    Let’s change the way we write it and optimize it.

    SELECT * FROM table WHERE ID > =(select id from product limit 800000, 1) limit 20
    It takes 0.2 seconds
    SELECT * FROM table WHERE ID BETWEEN 800000 AND 800020;
    It takes 0.1 seconds

    The gap is so obvious! I think wordpress has to do something, I am tired of constantly optimizing wordpress.

    • This reply was modified 7 years, 2 months ago by fantasts.
    Forum: Fixing WordPress
    In reply to: The paging is very slow under a large number of articles.
    Thread Starter fantasts

    (@fantasts)

    7 years, 2 months ago

    @macmanx

    server configuration
    Processor E5-2670 @ 2.60GHz *2
    Ram 32 GiB

    mysql Ver 15.1 Distrib 10.3.7-MariaDB, for Linux (x86_64) using readline 5.1
    PHP 7.2.7
    wordpress 5.0.3

    This is a very obvious problem, you only need to test it to get the answer.
    WordPress is still using the most primitive paging method, which is extremely inefficient.

    • This reply was modified 7 years, 2 months ago by fantasts.
    Forum: Fixing WordPress
    In reply to: The paging is very slow under a large number of articles.
    Thread Starter fantasts

    (@fantasts)

    7 years, 2 months ago

    @macmanx
    I am a programmer and I have tried the methods mentioned in the guide.
    I think this is a flaw in WordPress. I am tired of constantly optimizing the wordpress database. I hope that future version updates will be resolved.
    At the moment, I can only solve this problem by redesigning the pagination method.

    Forum: Fixing WordPress
    In reply to: The paging is very slow under a large number of articles.
    Thread Starter fantasts

    (@fantasts)

    7 years, 2 months ago

    @macmanx
    I can do as much as I can.
    This is undoubtedly a flaw in WordPress, using the most primitive pagination method.

    Forum: Plugins
    In reply to: [Front End PM] Slow query causes server to crash
    Thread Starter fantasts

    (@fantasts)

    7 years, 2 months ago

    @shamim51
    Thank you very much for your reply!
    How can I contact you?
    Notification settings have always been turned off.
    I re-discovered the website issue and it was indeed caused by the PM plugin.
    I got some new slow queries, and these query commands are frequent.

    # User@Host: darkanddark[darkanddark] @ localhost []
# Thread_id: 47789270  Schema: darkanddark  QC_hit: No
# Query_time: 1.278143  Lock_time: 0.045469  Rows_sent: 1  Rows_examined: 12212
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1552314448;
SELECT COUNT(*) FROM wp_fep_messages INNER JOIN wp_fep_participants ON ( wp_fep_messages.mgs_id = wp_fep_participants.mgs_id ) WHERE 1=1 AND wp_fep_messages.mgs_parent = 0 AND wp_fep_messages.mgs_type = 'message' AND wp_fep_messages.mgs_status = 'publish' AND ((wp_fep_participants.mgs_participant = 305727 AND wp_fep_participants.mgs_deleted = 0));

    The website has a total of 16524 letters.

    Slow query log screenshot
    https://cdnimg.acgget.com/images/201903/85f196f4e2f5f230.jpg

    server configuration
    Processor E5-2670 @ 2.60GHz *2
    Ram 32 GiB

    • This reply was modified 7 years, 2 months ago by fantasts.
    Forum: Plugins
    In reply to: [Front End PM] Slow query causes server to crash
    Thread Starter fantasts

    (@fantasts)

    7 years, 2 months ago

    @shamim51

    The number of registered users of the website reached 500,000, with an average of 70,000 visitors per day.

    In order to reduce the server load, even the pages accessed by the logged in user are cached. Only a small percentage of access is bypassed because the cache has not yet been generated.

    But the front-end-pm page is not cached.

    Once a new article is published, the home page and category page cache will be cleaned up.

    I tested to remove the cache cleanup code, even if I didn’t clean up the cache. Publishing an article will still cause the server to crash.

    I have turned on the redis object cache, and under normal circumstances the load is only about 3. Once the server crashes, the CPU will reach 100% use.

    I am not very clear about what caused it. I only know that everything is fine after closing the plugin. And there are a lot of such queries in the database slow query.

    SELECT COUNT(*) FROM wp_fep_messages INNER JOIN wp_fep_participants ON ( wp_fep_messages.mgs_id = wp_fep_participants.mgs_id ) WHERE 1=1 AND wp_fep_messages.mgs_parent = 0 AND wp_fep_messages.mgs_type = 'message' AND wp_fep_messages.mgs_status = 'publish' AND ((wp_fep_participants.mgs_participant = <strong>userID</strong> AND wp_fep_participants.mgs_deleted = 0));
    The userID is constantly changing.

    I am using nginx fastcgi_cache as a cache.
    And used the parameters:

    Fastcgi_cache_lock on;
Fastcgi_cache_lock_age 60s;
Fastcgi_cache_lock_timeout 60s;

    This means that the current page has only one access to the backend before the cache is generated. All subsequent accesses are blocked in the cache layer.

    Forum: Plugins
    In reply to: [Front End PM] 0 messages updated
    Thread Starter fantasts

    (@fantasts)

    7 years, 3 months ago

    @shamim51
    11393 fep_db_version a:0:{} yes
    update
    11393 fep_db_version 700 yes
    No change.
    It is worth mentioning that the page cannot display the received information.

    • This reply was modified 7 years, 3 months ago by fantasts.
Viewing 7 replies - 1 through 7 (of 7 total)