-2

Possible Duplicate:
Tactics for using PHP in a high-load site

I am new here. This is the first time.

One of my sites (which is in php/mysql) is getting good traffic and I upgraded the hosting to VPS too. At peak hours, it gets 7-8 users per hour and almost 50k database requests. But still I am having performance issues at peak hours. This site does not database transaction. It just gives xml feeds for mobile apps from mysql server (one way traffic, no data comes from users). Now I talked to my hosting about speed and they talked about few things and I desperately need your opinions for this-

  1. They talked about installing memcached. Will just installing it solve problem? How do I use this? Because they talked as if just installing this will solve the issue.
  2. Mysql query cache. I know how to use this. Will it improve performace. Most of my users request for 3-4 results( 70% traffic).
  3. Since all the queries are select queries only, how can I optimize so that mysql tables are not locked while getting results. Or any other suggestion I can get from you.

Thanks a lot for your time and help.

Community
  • 1
  • 1
toddler
  • 1

2 Answers2

0

They talked about installing memcached. Will just installing it solve problem? How do I use this? Because they talked as if just installing this will solve the issue.

It will help, but not solve all problems. But if you don't know how to use it, it won't help you on anything.

Mysql query cache. I know how to use this. Will it improve performace. Most of my users request for 3-4 results( 70% traffic).

It will reduce the queries. But if you want to delivery real-time results, it won't help.

Since all the queries are select queries only, how can I optimize so that mysql tables are not locked while getting results. Or any other suggestion I can get from you.

  • MyISAM = table-level lock;
  • InnoDB = row-level lock.

Use EXPLAIN to see what you can optimize on your queries.

With the details you gave, this is all help I can provide.

rlcabral
  • 1,496
  • 15
  • 39
0

1) Memcached can help but it will mask the real problem. This works like any cache: temporary store the result of resource demanding processes like big queries, long algorithms etc. You have to change you application to use this caching system.

2) Query cache. In your case MySQL Query cache might be a better solution which is effective if your traffic is read heavy. But regarding your third question you have locking issues as well and if you turn query cache on you will have mutex contentions too. Query cache invalidation is expensive procedure.

3/a) First of all if you haven't done yet change the storage engine to InnoDB which has row level locking and in newer versions of MySQL has more optimization than MyISAM has. (Try to use the latest version)

3/b) Check the indexes on tables and use EXPLAIN to analyze queries.

+) "7-8 users per hour and almost 50k database requests" this seems an enormous number of queries for 7-8 users. Try to check your database schema and application code. I saw things like this with queries in foreach which usually can be combined to one more complex query.

Károly Nagy
  • 1,734
  • 10
  • 13