MySQL Optimization?

[jitspoe]

So, as several of you have noticed, the login server has been getting a bit bogged down recently.  Since the login system is kind of my "Hello, world!" of database programming, I'm probably doing a lot of stuff in a non-optimal manner.  Does anybody know of a way to figure out which queries are bogging the server down so that I can look for ways to optimize them?

Edit: Could also be PHP code that's slowing down.  I'm not sure if the bottleneck is on the database or php.

[KnacK]

* KnacK uses his xbain bat signal...........

[b00nlander]

try this: http://www.mysql.de/products/enterprise/query.html

and you don't want to share any of the specs / code?

[Zorchenhimer]

Well, you could time the different scripts and/or queries, at least the PHP ones anyway.

Code: [Select]

<?php
$start_time = microtime(true);

<bunch of code>

$total_time = microtime(true) - $start_time;

echo "Total time spent: $total_time";
?>

Or, you could manually pass suspicious queries directly to the MySQL console.  That should give you something like "X rows in set (0.01 sec)".


Or you could try b00lander's link. 

[IronFist]

The most immediate thing I can think of is reviewing your table indexes to verify that fields you are hitting a lot are covered.

The only thing I've used in the past when playing around with MySQL was the slow query log feature:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Last comment is interesting, since it looks like you can make it specifically log queries that are not using indexes.

As suggested, Bain probably knows a lot more about (free) MySQL performance monitoring.

[jitspoe]

So I think I've figured out what (one of) the issue(s) is.  Apparently text fields can't be indexed?  Switching to a varchar would probably fix it.  Ultimately, though, I really need to figure out a better way to handle lists/arrays of numbers.

[sk89q]

You probably should normalize it since MySQL is relational. I really wouldn't store a list as varchar if I have to find rows that have a certain entry in the list.

[jitspoe]

This is interesting - I was running an IP log query, which I do pretty regularly, but for some reason, this one bogged down the whole DB.  I found a nice command that lists all the current queries and such:

Code: [Select]

+-----+----------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id  | User     | Host      | db       | Command | Time | State                | Info                                                                                                 |
+-----+----------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
|   6 | dpforums | localhost | dpforums | Sleep   |  470 |                      | NULL                                                                                                 |
|   8 | dpforums | localhost | dpforums | Sleep   |   95 |                      | NULL                                                                                                 |
|  11 | root     | localhost | dplogin  | Query   |    0 | NULL                 | show processlist                                                                                     |
|  19 | dpforums | localhost | dpforums | Sleep   |  320 |                      | NULL                                                                                                 |
|  23 | dpforums | localhost | dpforums | Sleep   |  362 |                      | NULL                                                                                                 |
|  28 | dpforums | localhost | dpforums | Sleep   |  362 |                      | NULL                                                                                                 |
|  29 | dpforums | localhost | dpforums | Sleep   |  118 |                      | NULL                                                                                                 |
|  30 | dpforums | localhost | dpforums | Sleep   |  401 |                      | NULL                                                                                                 |
|  43 | dpforums | localhost | dpforums | Sleep   |  327 |                      | NULL                                                                                                 |
|  46 | dpforums | localhost | dpforums | Sleep   |  313 |                      | NULL                                                                                                 |
|  59 | dpforums | localhost | dpforums | Sleep   |  113 |                      | NULL                                                                                                 |
|  64 | dpforums | localhost | dpforums | Sleep   |  277 |                      | NULL                                                                                                 |
|  75 | dpforums | localhost | dpforums | Sleep   |  103 |                      | NULL                                                                                                 |
|  88 | dpforums | localhost | dpforums | Sleep   |  275 |                      | NULL                                                                                                 |
|  98 | dpforums | localhost | dpforums | Sleep   |   90 |                      | NULL                                                                                                 |
| 132 | dpforums | localhost | dpforums | Sleep   |  109 |                      | NULL                                                                                                 |
| 135 | dpforums | localhost | dpforums | Sleep   |  102 |                      | NULL                                                                                                 |
| 136 | dpforums | localhost | dpforums | Sleep   |   93 |                      | NULL                                                                                                 |
| 166 | dpforums | localhost | dpforums | Sleep   |  345 |                      | NULL                                                                                                 |
| 169 | dpforums | localhost | dpforums | Sleep   |  450 |                      | NULL                                                                                                 |
| 245 | dpforums | localhost | dpforums | Sleep   |   12 |                      | NULL                                                                                                 |
| 549 | dplogin  | localhost | dplogin  | Query   |  311 | Copying to tmp table | SELECT MAX(`cl`.`timestamp`) as `timestamp`, `ci`.`ip` as `ipdec`, `ci`.`iptext`, `ci`.`type`, `ci`. |
| 555 | dplogin  | localhost | dplogin  | Query   |  299 | Locked               | UPDATE `dplogin_accounts` SET `temprandstr` = 'edited**************************' WHERE `id` = '5082' |
| 558 | dplogin  | localhost | dplogin  | Query   |  248 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '91442'                                                    |
| 560 | dplogin  | localhost | dplogin  | Query   |  227 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '77738'                                                    |
| 561 | dplogin  | localhost | dplogin  | Query   |  204 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '63500'                                                 |
| 564 | dplogin  | localhost | dplogin  | Query   |  185 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '66450'                                                    |
| 567 | dplogin  | localhost | dplogin  | Query   |  148 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '91442'                                                    |
| 568 | dplogin  | localhost | dplogin  | Query   |  143 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '6427'                                                  |
| 569 | dplogin  | localhost | dplogin  | Query   |  127 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '91442'                                                    |
| 570 | dplogin  | localhost | dplogin  | Query   |  121 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '6427'                                                  |
| 572 | dplogin  | localhost | dplogin  | Query   |  107 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '91442'                                                    |
| 573 | dplogin  | localhost | dplogin  | Query   |  100 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '6427'                                                  |
| 576 | dplogin  | localhost | dplogin  | Query   |   91 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '62169'                                                 |
| 577 | dplogin  | localhost | dplogin  | Query   |   86 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '91442'                                                    |
| 579 | dpforums | localhost | dpforums | Sleep   |   14 |                      | NULL                                                                                                 |
| 580 | dplogin  | localhost | dplogin  | Query   |   80 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '6427'                                                  |
| 581 | dplogin  | localhost | dplogin  | Query   |   64 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '65871'                                                 |
| 582 | dplogin  | localhost | dplogin  | Query   |   63 | Locked               | SELECT `pwhash` FROM `dplogin_accounts` WHERE `id` = '42204' LIMIT 1                                 |
| 584 | dplogin  | localhost | dplogin  | Query   |   58 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '43465'                                                 |
| 585 | dpforums | localhost | dpforums | Sleep   |    9 |                      | NULL                                                                                                 |
| 586 | dplogin  | localhost | dplogin  | Query   |   44 | Locked               | SELECT * FROM dplogin_accounts WHERE id = '66594'                                                    |
| 587 | dplogin  | localhost | dplogin  | Query   |   44 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '65871'                                                 |
| 588 | dpforums | localhost | dpforums | Sleep   |   43 |                      | NULL                                                                                                 |
| 589 | dplogin  | localhost | dplogin  | Query   |   38 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '43465'                                                 |
| 591 | dplogin  | localhost | dplogin  | Query   |   23 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '65871'                                                 |
| 592 | dplogin  | localhost | dplogin  | Query   |   17 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '43465'                                                 |
| 593 | dpforums | localhost | dpforums | Sleep   |   11 |                      | NULL                                                                                                 |
| 594 | dplogin  | localhost | dplogin  | Query   |    2 | Locked               | SELECT name FROM dplogin_accounts WHERE id = '65871'                                                 |
+-----+----------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
First off, I'm not sure why that query took so long to begin with.  Second, I'm not sure why it's locking everything else.  Third, why are there so many forum connections just "sleeping"?

[sk89q]

(2) MyISAM will lock the entire table. That is just how it works. You're going to have you fix that query.
(3) Did you turn on persistent connections?

[b00nlander]

why are all the dplogin_accounts queries executed multiple times for the same entry?

[jitspoe]

(2) MyISAM will lock the entire table. That is just how it works. You're going to have you fix that query.

Ick.  There's no way to tell it I don't really care if the data changes, I just want to grab what I can and not block everything else?  How do the forums do it?  I've noticed you can search for something, which may take a minute or two, but it won't block the forums for everybody else.

(3) Did you turn on persistent connections?

Yes.  I just thought it was odd that it seemed to keep adding connections instead of reusing the existing ones.  Once that query finished up, though, they all cleared up.

why are all the dplogin_accounts queries executed multiple times for the same entry?

Probably somebody reloading since the page didn't load the first time, or servers retrying.

[sk89q]

1. Well, the problem is not that that one SELECT is blocking all the other SELECTs. MyISAM handles concurrent SELECTs without a problem with a table lock. It's that you also have an UPDATE, which has to wait on the long running SELECT. All the other SELECTs, which were issued after the UPDATE, now have to wait until the UPDATE finishes. However, the UPDATE won't even start until the long-running SELECT finishes. Everything is basically fine and dandy until you decide you want to change the table, and then a domino effect occurs. This is the result of table locking.

You can set LOW_PRIORITY for the UPDATE (UPDATE LOW_PRIORITY `dplogin_accounts`), which will try to make MySQL push back the update until there are no SELECTs.  The only issue here is that, if the table is very busy with SELECTs, the UPDATE might be pushed back for a very long time.

Another option is to split the table. For columns that get updated more often but read less often, you can move them into a second table.

You can also switch to InnoDB in order to use automatic row-level locks instead. That will slow down queries that select from multiple rows though, because of the overhead of locking and unlocking each individual row.

Or another option is to "hold" all the UPDATE queries manually. You can have a script sit around to fire all the UPDATE queries rapidly at the same time at some interval. That means that the data will be a bit stale for some time, but then UPDATEs won't be interspersed between the long-running queries. The blocks will still occur with this option, but they will be less frequent.

The best solution, however, is to make that SELECT quick.

2. Persistent connections tend to do that, and I don't think they offer much of a benefit. If your database is on a different system and you're connecting via TCP, then perhaps you may want to use persistent connections.

[S8NSSON]

Good lord how slow are your select statements?
The DB or that code sucks if the traffic on this forum bogs it down.
Why all the SELECT *...do you really need all fields out of the table all the time?

[jitspoe]

Well, the reason that particular select statement was so slow is because I accidentally used the wrong field and ended up requesting IP logs for everybody who wasn't logged in (a very long list, I'm sure).

So are selects only locked when there is an update?  Or can one select block another select?  I guess what happened here was the first select blocked the update, and that update blocked the rest of the selects.  That update should be near-instant.  All it's doing is setting a temporary random string.  I could probably put that in a different table.  Basically, when a client connects to a server, it contacts the login server directly, validates the login info, then gets assigned a random string.  That random string is hashed with the login info, passed through the game server, and validated on the login server, so the game server knows the client is legit.  The reason for doing this is so malicious server admins can't sniff packets and start using somebody's account.

On the SELECT *'s, I probably don't need all the info.  I may revisit those.  There aren't many fields in dplogin_accounts, anyway, though.

I keep running "show processlist" when I can, but the database has yet to get bogged down again, so I haven't been able to pinpoint which query is causing the issue.  There's rarely more than one query active at a time.

[sk89q]

SELECTs won't block each other, because once the table is locked, MySQL can just read from anywhere in the table (this is a feature of MyISAM, which makes it fast for SELECTs). A SELECT will block an UPDATE/DELETE though, and an UPDATE/DELETE will block a SELECT.

By the way, for that random string, you could make a new table of type MEMORY, since the data isn't kept very long (from what I gather). That should make it blazing fast and not slowly eat away at your HDD.

[jitspoe]

Thanks for the tips.  When the login server starts getting bogged down again, and I figure out which queries are slowing it down, I should have plenty of ways to optimize it.