Too Many Queries – are db procedures the answer?

  • Resolved arathra

    (@arathra)


    7 years, 9 months ago

    Our WP database crashed several times. When our server support looked into it they found that there were too many connections to the db.

    There used to be 20k connections allowed to the db, but the technician removed this limit.

    But this strikes me as being bandaid solution which doesn’t really address the underlying problem which is one of code.

    We have several heavy queries which get specific sets of users from the db and which need to do lots of checks.

    So I’m wondering, do you think that it would be beneficial to write MySQL procedures/functions to help out? Or is this simply a matter of poor query coding?

    Or maybe this: we’re using get_users( $args ) for these queries; would it be better to use SQL queries directly? Just a thought!

    Any help or ideas appreciated!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz

    (@bcworkz)

    You need to determine where the bottleneck is. Rewriting all queries might help but it’s not very productive. There’s probably only one query that would need to be rewritten. There are benchmarking and performance testing tools available to help you find the bottleneck. I don’t have any experience with them, so I cannot recommend anything.

    If the number of connections was a problem, while you can rewrite queries, you must use the global $wpdb object to make the queries. Making more connections through mysqli functions is not going to help.

    It may be the DB itself needs to be optimized and tuned. Or perhaps you need more serious hardware. Determining the optimal approach for very large databases requires someone experienced in these matters. You can’t throw more resources at the problem without knowing what resource is in short supply.

    Thread Starter arathra

    (@arathra)

    Thanks @bcworkz – I’m taking a look at using $wpdb since I’m (kind of) familiar with that.

    I don’t have the skills to optimise the db so I’ll try and find someone to help out with that.

    Thread Starter arathra

    (@arathra)

    Just to finish this off, I managed to get the code working nicely using a simple SQL query and $wpdb

    $results = $wpdb->get_results( $sql );

    Thanks.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Too Many Queries – are db procedures the answer?’ is closed to new replies.

Tags