Simple wpdb->query sometimes kills my CPU?

  • Very quick summary. I have a website that has about 200-250 users on at its peak times.

    Sometimes, all is great. Sometimes, CPU load gets up to 15,16,17 (should be <4 ideally) and the site almost becomes unresponsive. Sometimes this doesn’t happen, even with 300 users on. It honestly fluctuates at any given time. Can’t figure out what its related to. Have many plugins and a very active Buddypress community (caching as well, but doesn’t really help with logged-in users) so narrowing it down is tough.

    ANYWAY.
    I accidentally found out one day removing this one query of a plugin “relieves” the load almost immediately.

    $wpdb->query("UPDATE " . $wpdb->wpfilebase_files
                    . " SET file_hits = file_hits + 1, weekly_file_hits = weekly_file_hits + 1, daily_file_hits = daily_file_hits + 1, file_last_dl_ip = '"
                    . $downloader_ip . "', file_last_dl_time = '"
                    . $this->file_last_dl_time . "' WHERE file_id = "
                    . (0+$this->file_id));

    This query is called when a file gets downloaded. Removing this one query – not all the hundreds of lines of code around it is also executed per download – just this one line. This relieves the load to “acceptable” ranges (still not ideal, but the site becomes snappier, there’s definitely cause-and-effect here, I mean I kinda expect the site to slow down when there’s 200-300 users on but this line being removed seems to relieve the “crippling” of the site).

    But, why?
    It’s not a crazy query. This table is indexed as well. Is there another way for me to perform the same query in order to test? (Note the “file_hits + 1” type of stuff which isn’t doable with a wpdb->update call)

    • This topic was modified 5 years, 5 months ago by FTLRalph.
Viewing 11 replies - 1 through 11 (of 11 total)
  • Dion

    (@diondesigns)

    You said the table is indexed. Is the file_id column indexed? If not, that’s your problem.
    If it’s indexed, then here’s two suggestions.

    First, make sure the table is using the InnoDB engine and not the myISAM engine. Tables that are updated frequently will benefit from the row-locking of InnoDB.

    Second, check your memory usage. This issue could easily occur if your site has maxed out available memory, and/or is using “swap” memory.

    Beyond that, I’d suggest having someone look at your MySQL configuration.

    Thread Starter FTLRalph

    (@ftlralph)

    Thanks

    I do admit I don’t know much about this stuff, been learning as I go

    But I do believe it is indexed

    View post on imgur.com

    This particular table is classified as MyISAM however.
    I’ll look into it, here’s hoping it’s a matter of changing a simple selectbox setting to get it to InnoDB lol but I doubt it.

    Dion

    (@diondesigns)

    Hopefully switching to InnoDB will help. (Note: full InnoDB support requires MySQL/MariaDB 5.5 or later.)

    I see another problem based on that image — the table has three FULLTEXT indexes. Updating a table having a FULLTEXT index can take a long time if it contains more than, say, a couple thousand records. With three of them and 25000 records…that’s a problem. Maybe the plugin author never imagined that the table would contain more than a few hundred records.

    Thread Starter FTLRalph

    (@ftlralph)

    Would that still be an issue if there are only a few UPLOADS a day (that’s when the fulltext indexes would be getting updated with new info) – vs the thousands of downloads per day which I don’t believe do anything other than increment download counters?

    There is of course text-searching all the users perform to find files, but that’s not “adding” new data through updates, its searching.

    Thread Starter FTLRalph

    (@ftlralph)

    Hm, unfortunately looks like converting to InnoDB isn’t going to be that easy, phpmyadmin telling me:

    #1071 – Specified key was too long; max key length is 767 bytes

    Upon some research, this seems to be (I think) related to the indexes. And I think, the fact that file_description (which has a Fulltext index) is of type “Text”

    View post on imgur.com

    If that’s actually the case, I’m not sure how to proceed, it def needs to be text, there’s a lot of it in the descriptions (varchar(255) wont cut it).

    Unless I can, delete the indexes? I mean, I could re-create them if needed but I don’t want to set necessary fires. They’re utilized when searching for files.

    Dion

    (@diondesigns)

    What version of MySQL are you using? FULLTEXT indexes weren’t supported by InnoDB until MySQL 5.6.7.

    Try running OPTIMIZE and then ANALYZE on the table. THe former will defragment the table, the latter will help the query optimizer do its job.

    Did you check your memory usage to see if you’re near max, or are using “swap” memory?

    Have you contacted the author of the plugin? Given the unusual table schema, I’d be really curious what they had to say..

    There are ways to debug your issue, but they are well beyond the scope of this site. If none of the above lead you to the cause of the problem, I suggest you find someone to help with the debugging. To point you in the right direction, the EXPLAIN statement would be very useful in this situation.

    Almost forgot…do not delete the FULLTEXT indexes. That will break your site!

    Thread Starter FTLRalph

    (@ftlralph)

    Just wanna say thanks for all the ideas and advice, really appreciate it, at least I got something to work of trying to get Innodb implemented
    I don’t wanna keep you in this back and forth but if you have any further ideas please do feel free to let me know lol

    Memory seems alright, this is at a moderately-high load level right now

    View post on imgur.com

    I have to admit I used to run “Optimize” on the table every other day when I noticed this and idk if it was just coincidence, but that used to help. (Not anymore lol)
    But I tried the optimize-analyze just now

    The plugin is long since abandoned and tbh I’ve been the one messing with adding that last index that (I thought) would help when searching a combination of file name,author,description meta data.

    Well on my local install “Server version: 10.1.38-MariaDB”
    I was able to delete the indexes, then convert to Innodb, then somehow kinda recreate the indexes as they were but now searching doesn’t work anymore

    On the live site, however “Server version: 5.5.60-MariaDB ” which is obviously much lower, but I believe just high enough to maybe work

    Dion

    (@diondesigns)

    MariaDB 5.5.x does not support FULLTEXT indexes for InnoDB.

    If you added a FULLTEXT index, I’d suggest removing only that index and see if it helps.

    Thread Starter FTLRalph

    (@ftlralph)

    Yes that’s right.

    I’m just testing on my local install (with 10.1.x) to test if this is even a viable solution.

    After trying again today, I was able to convert the table to InnoDB and preserve all the indexes (had to change file_display_name from varchar(300) to (255) beforehand).

    Searching works, and the table is InnoDB. I’m thinking of trying this on the live site.

    I’m Googling around and I see:
    Full-text indexes can be used only with MyISAM and Aria tables, from MariaDB 10.0.5 with InnoDB tables and from MariaDB 10.0.15 with Mroonga tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

    Thanks again for all the ideas and help. I’m going to look into updating to at least that version of MariaDB and give Innodb on this table go.
    The alternative is I can leave it as it is now, remove the fulltext indexes, and change the searching from match...against to like

    Thread Starter FTLRalph

    (@ftlralph)

    So just to keep this thread updated, I was able to
    1) drop the fulltext indexes
    2) convert the table to innodb
    3) revamp search to use LIKE and not MATCH...AGAINST since no more fulltext indexes

    And unfortunately 24 hours later same deal, pretty much, load very high and commenting out that query relieves it somewhat.

    Honestly the inefficient searching (LIKE) may now be contributing to the problem, gotta keep tinkering

    Hi, FTLRalph. I am here because I am interesting for any message where people are discussing fulltext search.

    So, about the slow and inefficient “LIKE” search in WP. I also faced the same issue some years ago and decided to develop something universal to implement faster and more flexible search, which will actually replace the default WP search without the need to change something in the code.

    The thing I’ve found is the INDEXED search which is implementable with MySQL and PHP only and could work as a WP plugin without any external services or installed applications.

    Thus, I got the idea and reimplemented it in the pure PHP with WP database structure support and lots of extensions. And the result is called “WP Fulltext Search” plugin, which you can find in the WP repository (https://www.ads-software.com/plugins/fulltext-search/). It’s free and GPL licensed.

    Please try to use it for search on your fields which contains no FULLTEXT indexes anymore. I guarantee a sufficient increase in speed.

    For the WPFTS it does not matter which type of MySQL tables you’re using and which indexes you have on database columns.

    Have a nice day!

Viewing 11 replies - 1 through 11 (of 11 total)
  • The topic ‘Simple wpdb->query sometimes kills my CPU?’ is closed to new replies.