Optimizing mysql performance

ok guys so im trying to get my mysql performance response time up as much as possible. this is my system spec please if your able to help me get the best performance please feel free or even if you know a rough formula to run the setting to


System Spec
OS: Windows XP
CPU: Single Core 1.6GHz
Physical Memory: 2GB
Virtual Memory: 4GB
MySQL version: 5.1.36
Storage Method: MyIsam


System Idle
CPU: 0%
Memory Usage: 500MB


Main Processes Running And Needed Allowance
Firefox - 100MB Ram
Apache - 70MB Ram
ESET - 55MB Ram


Resources Left To Use
1.2GB Physical Memory
4GB Virtual Memory



ok guys thats everything i could possibly think you would need to know. i need mega low read and write times as my server has to have a very fast response time. currently running around 30 mysql queries im able to get a 17ms response time but i need this to go to about 3ms or slightly higher towards 8ms

please any help would be awesome set values of attributes in my.ini would be great but formulas to calculate what i need depending on values for example if a faster CPU was added would be the best and im sure this would also help many other users within the forum

Regards,
Jamie



p.s. if this cant be figured out without data any benchmark mysql software i can use to test my server and a way to figure it out using different allowances would be awesome as the only mysql test ive been able to find of running set queries using so many threads and checking reaction time have all been linux based

Where did you get those figures from?
What is the reason for the fast response time?

Because focusing on just getting short response times are very hard to achieve since your OS is multitasking, and especially if you use the machine as a workstation (your firefox process) then it can be virtually impossible.

And the way your queries are written and the database layout is so much more important to get a fast response time than what the MySQL server settings can ever give you.

So generally what you do is to make sure that the MySQL internal cache/buffers are set to good values and then you see if the performance is adequate for your needs.

If not then buying more RAM (if you have a large database) or faster CPU (depending on if cpu is the limit on your server) or faster disks is the solution.

But these things are very much dependent on your specific query pattern/data structure so it is impossible to give a generic formula to calculate the response time.

those response times are from my current website, im writing my online game and atm i have 52 people beta testing, most login the same time therefore the lower the time needed to submit my queries the less stress i will have on my server, so far i have tested the same configuration on a dual core 2.8GHz pc with 3GB ram running nothing but apache and mysql and the response time dropped to 7ms but refuses to go faster no matter. ive been reading around and the databse for now is only tiny.

ive heard memcached is a hell of alot faster however this may lose data on system restarts or crashes. ive also seen that uing Innodb budder i can increase the speed also but i have everything in myIsam. currently the structure is simple, i am using 2 databases. i'll post main database structures then table structures below them if you see anything wrong please tell me

main database
buildings -

• Primary Index - BuildingID
• BuildingID - varchar(15)
• 1 - varchar(255)
• 2 - varchar(255)
  continueing to
• 30 - varchar(255)

costs -

• Building - varchar(255)
• level - int(11)
• wood - int(11)
• stone - int(11)
• iron - int(11)
• time - int(11)
• DivisionFactor - int(11)

messagesystem -

• Primary Index - Id
• Id - int(11)
• from - varchar(255)
• to - varchar(255)
• content - longtext
• posted - varchar(255)
• name - varchar(255)
• WorldID - int(11)
• status - varchar(255)
• stat_other - varchar(255)

Points -

• Primary Index - BuildingID
• BuildingID - varchar(15)
• 0 - varchar(255)
• 1 - varchar(255)
  continueing to
• 30 - varchar(255)

queue -

• VillageID - int(11)
• building - varchar(255)
• start - varchar(255)
• finish - varchar(255)
• time - int(11)
• world - int(11)

troopqueue -

• VillageID - int(11)
• troop - varchar(255)
• amount - varchar(255)
• start - varchar(255)
• finish - varchar(255)
• time - int(11)
• world - int(11)

troops -

• Primary Index - TroopID
• TroopID - varchar(255)
• Type - varchar(255)
• Wood - int(11)
• Iron - int(11)
• Stone - int(11)
• Population - int(11)
• Time - int(11)
• Attack - int(11)
• Defense - int(11)

villages -

• VillageID - int(11)
• World - int(11)
• Owner - varchar(255)
• Loyalty - int(11)
• Points - int(11)
• Name - varchar(255)
• Castle - int(11)
• Quarry - int(11)
• OreMine - int(11)
• SawMill - int(11)
• WareHouse - int(11)
• Trainingcamp - int(11)
• Stone - varchar(255)
• Iron - varchar(255)
• Wood - varchar(255)
• LastLoad - int(20)
• Quarryqueue - int(11)
• OreMinequeue - int(11)
• SawMillqueue - int(11)
• WareHousequeue - int(11)
• Trainingcampqueue - int(11)
• Castlequeue - int(11)
• Sergeant - int(11)
• Cadet - int(11)

worldinfo -

• Primary Index - WorldID
• WorldID - int(11)
• Speed - int(11)
• Troopspeed - float(2,1)

User database
world1 -

• Primary Index - Username
• Username - varchar(255)
• Password - varchar(255)
• Email - varchar(255)
• Premium - varchar(255)
• Active - varchar(255)
• Points - int(11)
• Premium_End - varchar(255)
• Tribe - varchar(255)

You really should avoid using VARCHAR columns as primary keys. Use INT's instead. Especially on InnoDB (you didn't use it but I'm mentioning it in case you change tabletype).
The reason is that a INT is only 4 bytes while a VARCHAR is very often much bigger and on InnoDB it uses the primary key in a very special way internally (search on this forum or google if you really want to know why) that makes it very bad to use a varchar as a primary key.

And what about if you decide to change the name of the building, then you have to change all other tables that references it.
So you should have a structure something like:
buildingID INT
BuildingName VARCHAR()
...

Yes memcached is a hell of a lot faster, but the question is if the queries are the same with the same result all the time or if the result varies.
Memcached as well as the mysql query cache is only caches and only works for repeated queries that return the same result time and time again.

More important that just staring yourself blind on the query time you should (if you haven't already) work with using EXPLAIN ... to get the execution plan for your queries as optimal as possible.
Because about 10ms delay for sql queries is a pretty common figure and nothing to be alarmed about. It doesn't even mean that the server is under much load, because there are normally some delays in the entire chain from query to returning the result that can add up to a time like that.

is there anything i can use instead of varchar that will store text and still be small?

ok ive edited everything to run on int values as the index's but this didnt change anything really due this running on myIsam

i have some tables within the database in which nothing changes and they can be sent to cache, should i select the tables and set them to memory instead of myIsam or do you think it wont change much?

If they never change then you can try with the mysql-query-cache.
If you execute something like this in MySQL:

Your query cache should be enabled with about 20MB size cache.
If this setting works for you you can add/change it in the my.cnf startup parameter file so that it will continue to work even after restart/reboot.

yes the my.ini file is the part i am editing. i have got it down to about 9/8ms now from the original 17 but that was due to sorting out my mysql queries. i was posted my quesries would you guys be able to see if you can merge any together and optimize them as im not expert with mysql