1

I'm in debate with my database admin. He claims that my queries I'm sending through PHP are the problem and he is stating several things I believe are completely false.

To begin I have a site receiving 64,000 daily views. My server monitors queries over 5 seconds. Multiple times during the day I see 3-6 "SELECT" queries all taking more than 10 seconds, sometimes even up to 200 seconds. This is on an InnoDB table on MySQL 5.0. Also PHP is calculating the speed of the query using microtime. Ironically enough I try the same queries in PHPMyAdmin and they take 0.0031 seconds.

My Admin says I shouldn't use an asterisk in my code, but nowhere online goes against that. He says I should convert all my tables from MyISAM to InnoDB. I have already done that but still the same things happen. He doesn't seem to be budging and even worse once in a while my server is down for 1-2 minutes with a MySQL error of "Too Many Connections" or "MySQL Server has gone away"

The Admin isn't listening to reason and these problems continue. I'm on a version of mysql with profiling disabled so I can't really see query times of just mysql. Can anyone suggest anything I can do or try to diagnose this? I'm only the coder but I have experience in mysql (probably more than the admin).

Improve this question
4
  • "My Admin says I shouldn't use an asterisk in my code, but nowhere online goes against that" - could you explain what you mean by this?
    – Ben Oastler
    Nov 14 '14 at 3:25
  • @BenOastler for example "SELECT * FROM posts WHERE date > 1495001 LIMIT 10"
    – keji
    Nov 14 '14 at 3:30
  • I understand its bad practice to use an asterisk but he thinks that it is causing the database problems.
    – keji
    Nov 14 '14 at 3:31
  • 2
    I agree with your admin. Only select the columns you need. The more columns, the slower your query.
    – Ben Oastler
    Nov 14 '14 at 4:15
2

In your question, there is not enough information to see where the problem is from technical point of view; however, I can see that there is a problem in communication between you as developer and the DBA. This is not abnormal though as DBAs and devs do not always 'speak the same language'.

Let's go the some points in your question:

  • You should rely on the 'slow query log' to get your slow queries. Monitoring them in PHP may not be accurate measurement for slowness of the queries.
  • The query that took very little time in PhpMyAdmin could be cached. Use "SELECT SQL_NO_CACHE", and repeat at times that are not close to each other to avoid hardware level caching.
  • "SELECT *" vs "SELECT field1, field2": If you don't need all fields in your result, don't use "SELECT *", as it will waste resources sending non-wanted values.
  • "Too Many Connections" could be of different reasons, one of them is slow queries that lock the tables so other connections are waiting and piled up to the max where the server refuses new connections.

My recommendation is that you talk to your admin and work both as one team, not as two separate teams.

HTH

Improve this answer
4
  • I'll try to communicate with him then. It really feels like it's not a DB problem but a problem connecting to it. Nonetheless I'll ask him to enable the slow query log and try some tests. Thanks :)
    – keji
    Nov 14 '14 at 4:31
  • @kdogisthebest I recommend never using SELECT *, even if you select all rows, not for performance reasons (it would be equivalent in that case) but for good programming practices (if there is a compatible change on the table, the application should not be affected; if there is an incompatible change, the application fails immediately).
    – jynus
    Nov 14 '14 at 8:14
  • I really strongly agree with Jehad on this one.... there is a major communication breakdown here between you and the DBA and you really should clear that up and work together. Also, it's kind of an obvious question, but you have limited the result set, right? If the SQL query is trying to page out 100,000 rows to the browser, that's gonna be slow no matter how well MySQL is designed. It's obvious, but I have to double check and ask
    – Evan Volgas
    Nov 14 '14 at 20:11
  • Another $0.02 * get off 5.0, it's unsupported and isn't strong with concurrent workloads. * Test on Percona Server 5.5 or 5.6. * Spend some time on with the tools.percona.com/wizard and generate a config that's going to aid the cause not limit it. * Review the slow query log using pt-query-digest to ensure that you're in tune. * There's little reason to use MyISAM these days to migrate to innodb. All of the above are something you can sit down with your admin and complete. If (s)he's still not playing nice, send them over to this post so we can have a word ;) Good luck!
    – eroomydna
    Nov 14 '14 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.