MySQL performance

[Darth Wong]

Does anyone have any MySQL performance tips? I had to disable searching on the main board (it still works on the Archive board) because the system was hitting the swap partition hard every time someone tried to search, and the board would basically lock up for 30 seconds or so.

PS. One brute-force solution is to upgrade from 1GB of RAM to 2GB of RAM, but that will be expensive. Unfortunately, my motherboard only supports 2GB of RAM if I downgrade from PC3200 to PC2700, and the 1GB of RAM that I already have is PC3200. So I would have to buy the full 2GB and then find something to do with the old 1GB of PC3200.

[Darth Wong]

24 hours and not one response. Does this mean that nobody here knows anything about MySQL performance, or does it mean that nobody here cares that the board is getting slower?

[Spacebeard]

I'm not at all familiar with MySQL, but if it's not radically different from PostgreSQL, Oracle, or DB2, then you may be able to alleviate your load by adjusting the size of its buffers. I think heavy paging activity (I'm assuming you've verified that this is the case using vmstat or the like) probably means that the buffer is too large.

You might also make sure that your swap and data are on different disks attached to different controllers. If the MySQL server is multiprocess or multithreaded, the server might be able to service other clients rather than having the processes deadlocked waiting for the same disk.

[Darth Wong]

Looks like I may need to get a beefier server. Up till now I have tried to solve all performance problems in software. But there's only so much you can do with a single-CPU box that has only 1 plain-vanilla IDE hard drive for all of its mass storage.

[Dalton]

Well, Aron Kerkhof knows MySQL if you want to try to get in contact with him.

[Uraniun235]

If it's hitting the page file a lot because of memory issues, then 2 gigs running at PC2700 speeds would probably be faster than 1 gig at PC3200 and a whole bunch of pages to the comparatively much slower hard drive.

[Brother-Captain Gaius]

So the mobo won't like it if you stick a PC2700 stick in with the PC3200? If the slower stick is first, any subsequent sticks will automatically run at the speed of the first.

[Thunderfire]

Depends on the table. Adding an index or two can speed up selects.

[Meest]

So the mobo won't like it if you stick a PC2700 stick in with the PC3200? If the slower stick is first, any subsequent sticks will automatically run at the speed of the first.

If it's using registered memory I don't think you want the 3200 to downclock to 2700, it's always safer using same rated RAM timings.

[Spacebeard]

Depends on the table. Adding an index or two can speed up selects.

If I recall correctly, phpBB's search tables already have all the indexes they need. I doubt if any sequential tablespace scans are being performed at all.

I still maintain that, if MySQL has a memory buffer of variable size as most databases do, excessive paging is probably due to its buffer being too large. If it were too small, you would see excessive disk I/O as it loads index leaves into memory, but no paging.

[Kreshna Aryaguna Nurzaman]

If I recall correctly, phpBB's search tables already have all the indexes they need. I doubt if any sequential tablespace scans are being performed at all.

"Already have all the indexes"? I've never handled MySQL before, but are those indexes auto-generated or some sort? Just for record, Microsoft SQL has a feature called "Index Creation Wizard". Based on my experience, such indexes are mostly the worst, because they usually includes unecessary columns or even skipping some important ones. Nah, a manually self-made (and self-tuned) index is usually better.

Anyway, does MySQL has the option to show the execution plan?

[Kreshna Aryaguna Nurzaman]

PS. One brute-force solution is to upgrade from 1GB of RAM to 2GB of RAM, but that will be expensive. Unfortunately, my motherboard only supports 2GB of RAM if I downgrade from PC3200 to PC2700, and the 1GB of RAM that I already have is PC3200. So I would have to buy the full 2GB and then find something to do with the old 1GB of PC3200.

Just wonder, do you also use the server for any other purposes beside the board sever? How large the database is? How many record it has, approximately? (I'm not pretty familiar with PHPBB database structure: I guess one post corresponds to one record. Am I correct?) I count there are already 952285 posts in the forum (not including testing).

As others have mentioned, an oversized buffer is always the first culprit because it causes swapping. Is swapping actually the case here? Do you have some kind of "performance monitor" where you can detect swapping? Particularly during the search. However, if you already have "rightsized" the buffer cache and the search process is still slow, then it's time for a larger RAM; particularly since you said you've already tuned everything through software.

Looks like I may need to get a beefier server. Up till now I have tried to solve all performance problems in software. But there's only so much you can do with a single-CPU box that has only 1 plain-vanilla IDE hard drive for all of its mass storage.

Anyway, a single hard drive to cram every database file is a bad idea. Multiple, small hard drives is always better than a large single one, because you should spread multiple database files to streamline I/O. Here's my suggestion:

(1) two equally sized hard drives; one for tables, another for index.

(2) a small but extra fast hard drives for redolog (or MySQL's equivalent of redolog). I guess you can put this one together with the O/S.

(3) another small but extra fast hard drive for swap partition, but you may not need this once you've eliminated the swap (by rightsizing the buffer)


EDIT: Again, I'm not familiar with PHPBB database structure, but does it have reference tables or some sort (like user names, post dates, etc)? And does the reference tables is being joined by the main tables (tables containing posts) during the search? (for example if we're searching based on user names) Does the search function involve a lot of joining, particularly joining with reference tables?

And if that's the case, is it possible in MySQL to "pin" those reference tables permanently in the buffer? If that's the case, and the search involves a lot of joins, then by all means keep those ref tables on the buffer. It would speed up join operations with those tables since the database doesn't have to read from the hard drives for those joined reference tables.

[General Brock]

Like to help, but I doubt I could even come up with an original quip.

A couple of sites seemed relevant on swapping,

http://kerneltrap.org/node/3202?from=50 ... er_page=50

http://jeremy.zawodny.com/blog/archives/000132.html

You probably already know of:

http://www.linuxquestions.org/questions ... /2004/03/4

and related sites, but I'm not sure what questions to look for.

Otherwise, sorry.

[Uraniun235]

Anyway, a single hard drive to cram every database file is a bad idea. Multiple, small hard drives is always better than a large single one, because you should spread multiple database files to streamline I/O. Here's my suggestion:

(1) two equally sized hard drives; one for tables, another for index.

(2) a small but extra fast hard drives for redolog (or MySQL's equivalent of redolog). I guess you can put this one together with the O/S.

(3) another small but extra fast hard drive for swap partition, but you may not need this once you've eliminated the swap (by rightsizing the buffer)

Those are fine performance suggestions, but a far superior alternative to buying a stack of hard drives would be to get that extra gig of RAM because RAM will always be faster than any hard drive. PC3200 RAM should downclock to PC2700 just fine, but if someone's got a link saying otherwise, I'd love to read about it.

Now, once Mike hits a point where he simply can't add any more RAM, then it would be prudent to start addressing other bottlenecks like the hard drives, but until then the money spent making those paging operations faster is going to be more expensive and less productive than money spent making the paging operations not as frequently necessary.

Also, by the time that we start talking about multiple hard drive configurations like that, it may be time to start considering a board fundraising drive to try and gather the cash needed to get a beefier system overall that could be expanded out to more than 2 gigs of RAM and support some nice, fast SATA (or, Yoda willing, SCSI!) hard drives.

[Darth Wong]

Just so you guys know, the database for this forum is currently 2GB. That is not a typo. The indexes alone are more than 700MB. That's why I'm suspecting the 1GB of RAM as the primary bottleneck, and I noticed it swapping heavily whenever people would try to search.

[General Brock]

Nick Piggin [interview] explained that swap can improve performance no matter how much RAM you have, " well it is a magical property of swap space, because extra RAM doesn't allow you to replace unused memory with often used memory. The theory holds true no matter how much RAM you have. Swap can improve performance. It can be trivially demonstrated." This said, numerous Linux users do report success running a swapless system.

As to why swap performance might be becoming more and more an issue over time, Rik van Riel [interview] suggested that this could be due to the growing gap between the speed of RAM and the speed of hard drives, " the speed of hard disks doesn't grow anywhere near as fast as the size of memory and applications. This means that over the last years, swapping in any particular application has gotten SLOWER than it used to be ... This means that even though the VM is way smarter than it used to be, the visibility of any wrong decision has increased."

I don't know what to make of that, other than getting a more powerful processor that can make better use of the RAM, or maybe the HDD hardware is wearing on your server.

On the other hand, if you want to buy a new server, I'm sure no-one would mind but your bank book. I could chip in a few loonies, but nothing realistically helpful.

[Uraniun235]

What about pruning the active board? I notice we have open threads dating back to 2003.

[Kreshna Aryaguna Nurzaman]

Those are fine performance suggestions, but a far superior alternative to buying a stack of hard drives would be to get that extra gig of RAM because RAM will always be faster than any hard drive. PC3200 RAM should downclock to PC2700 just fine, but if someone's got a link saying otherwise, I'd love to read about it.
<snip>

The storage tuning wasn't meant to solve the immediate problem (which is swapping). It's actually something more related to long-term performance. Well it's just my habit to start the tuning from the storage first, because no matter how large the RAM you have, hard drive read/write is always an unavoidable process, especially on my clients (costumers) where typical database size is 50GB or more.

Just so you guys know, the database for this forum is currently 2GB. That is not a typo. The indexes alone are more than 700MB. That's why I'm suspecting the 1GB of RAM as the primary bottleneck, and I noticed it swapping heavily whenever people would try to search.

Yup, adding more RAM is a must, but once you did, remember to "balance" the size of your buffer area so it'll large enough to take advantage of the extra RAM, but not overly large to the point it causes swapping.

Also, by the time that we start talking about multiple hard drive configurations like that, it may be time to start considering a board fundraising drive to try and gather the cash needed to get a beefier system overall that could be expanded out to more than 2 gigs of RAM and support some nice, fast SATA (or, Yoda willing, SCSI!) hard drives.

Or an external hard drive array