MySQL Forums
Forum List  »  Install & Repo

MySQL configuration for remote access
Posted by: aaron
Date: June 20, 2012 01:52PM

I have a database server that is not at the same location as the webserver that runs PHP. The ping time between them is roughly 35 ms.

I recently added more RAM to the database server (MySQL 5.051b, running on windows, tables are all MyISAM) so it now has 3 GB. Most of my queries are SELECTs (server statistics since last reboot: 90 inserts, 200 updates, and 380,000 selects).

I'm wondering how I can change MySQL configuration to improve performance?


Sometimes very simple queries (Select * from table where ID=12) on properly indexed tables are taking 0.1 seconds. Sometimes more complex queries, which normally take 0.1 seconds are taking 2 seconds. I have relatively few queries that are > 10 seconds (9 of them).

Key efficiency is 99.92%. The table indexes fit in 50 MB. I gave it 256 mb, and could reduce this down to as low as 64 mb without a problem, right?

Query Cache hit rate is 21.6%. I could increase the query cache as it is only 64 mb. I'm not sure how much this will help as there are a lot of possible queries (for instance a common one is "search by zip code" and there are 40,000 of those in the United States), and any edits to the tables will cause the cache entry to not be useful.

It is creating some tmp_disk_tables (1000), very few tmp_files (5), and mostly a lot of tmp_tables (210,000).


Besides optimizing MySql, I'll also reduce the number of queries to minimize the 35 ms ping time loss.

Options: ReplyQuote


Subject
Written By
Posted
MySQL configuration for remote access
June 20, 2012 01:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.