hello I was wondering about mysql performance

[mdburton]

Right now I am in a VPS server, mysql constantly will run at 18% usage and slows the site down.

I was wondering what you guys think about a

INTEL P4 3.0GHz HT
1024MB DDR RAM
120GB 7200RPM Hard Drive
1500GB Bandwidth
CentOS Linux Operating System
100Mbps Port + 10 IP's (20 Max)


The site I run is php that constantly gets information and edits information with EVERY page that is ran, right now whenever I get 10 people online flying through the pages it lags horribly. So I was hoping a dedicated server would fix this. I cannot afford a processor much better than this with these specs.

Do you think it would be an improvement? This is a 'web' based game site that heavily relies on the database to get information and change information. Every 10 minutes a php script is ran that adds 'turns' to currently 150 users whether they are online or not. I don't think I can optimize my database much since the information changes virtually every click of the page. But if you think I could optimize it then say what you think, I have never optimized a database before.

I basically want to be able to support 100 pages of php code per second or close to that.

[pbmods]

Start with these because they're free:

• Make sure your queries are optimized. Make good use of the LIMIT clause if you know how many results you're supposed to return.
• Make sure your tables are properly indexed.
• Check your slow_query log and EXPLAIN it.
• Set up a periodic script or MySQL event that runs an OPTIMIZE TABLE query on each of your tables. This is especially important if you have MyISAM tables, and you delete a lot of data on a regular basis.

The goal is to get MySQL to search as few rows as possible before returning results. If you have an unindexed table with 6000 rows, MySQL has to search every single one of those 6000 rows before returning a result (unless you use a LIMIT clause). On the other hand, if you index that table by whatever field you're searching, MySQL only has to search ONE row per result.

The downside to indexes is that they use a LOT of hard drive space.