Portal Home > Knowledgebase > Articles Database > MySQL Optomization tips
MySQL Optomization tips
| Posted by Awmusic12635, 05-06-2012, 10:17 PM |
| Hello,
I am currently developing a project that is very database heavy. I have some basic caching running, but one of the main advantages of this project advertised over the rest is speed. Currently i believe the database has 130,000 objects in it but will eventually be increased to about 500,000.
Usage: The database is browsed and searched entirely often and loaded through PHP to be displayed.
I welcome any and all tips to keep the server and database running fast as possible. With the current hardware I have. Intel Xeon e3-1230 , 8Gb of ram ( could upgrade easily to 16). Not interested in changing Storage config though.
It's a cpanel dedicated server running centos 6.
Also would love to hear ways to help it scale.
Last edited by Awmusic12635; 05-06-2012 at 10:27 PM.
|
| Posted by VectorVPS, 05-07-2012, 03:06 AM |
| InnoDB or MyISAM tables (or both)?
What kind of workload (from your description it sounds like it is read-heavy)?
Have you optimized your queries, adding indexes where needed?
Total database size (on disk, not # of records)?
How much RAM can you dedicate to MySQL?
|
| Posted by ArturasLIX, 05-07-2012, 03:33 AM |
| Do you experiencing any problem now? Is there high cpu usage or I/O usage?
|
| Posted by Awmusic12635, 05-07-2012, 07:22 AM |
| No, CPU is running fine, not even close to Mac, and I/O is pretty low as well.
|
| Posted by Awmusic12635, 05-07-2012, 03:50 PM |
| MyISAM tables are used I believe.
Yes, the main work load is read heavy, though in bursts it can be write heavy (about once a day). The project itself is Lima Installer, web based version of Cydia for Jailbroken iOS devices. (Installs packages through the browser instead of the slower app).
I believe the total database size is about 20mb currently.
I can dedicated as much as I need, currently have 8Gb on my server but I can upgrade to 16 if needed.
|
| Posted by Server Management, 05-07-2012, 03:52 PM |
| What is your current my.cnf contents?
If your running Apache you might want to setup Nginx as a reverse proxy for some free additional performance
|
| Posted by Awmusic12635, 05-07-2012, 04:04 PM |
| The content is:
Also yes, I am running apache, might give Nginx a try soon, just never really got around to it
|
| Posted by Prestahost, 05-07-2012, 06:20 PM |
| this doesnt sound as a particularly large database and perhaps there will be no problems, the size itself is not the sole factor... this may help:
- examining the slow queries log, add indexes if appropriate, creating and maintaining helper tables to prevent complicated joins if feasible
- monitoring is important for fine-tuning the buffers, check there for intro: hackmysql.com/mysqlreportguide
|
| Posted by Awmusic12635, 05-07-2012, 06:31 PM |
| It's not that it's slow at this moment, it's more that I want it to be optimized as much as possible so that it has plenty of speed to scale. Currently running with 1,000 beta testers but have another 7k waiting for an invite. Not even counting the amount once it's out of beta. Also, thanks for the link
|
| Posted by Collabora, 05-07-2012, 06:57 PM |
| The mySQL site has a bunch of good stuff on the subject: http://dev.mysql.com/doc/refman/5.0/...imization.html
|
| Posted by dewaforex, 05-10-2012, 03:22 AM |
| I think 20MB is small, my database it self on 320MB RAM vps is about 28MB..
|
Add to Favourites 
Print this Article
Also Read
