| Need advise of tuning mysql performance for fast transaction time [message #494] |
Thu, 21 December 2006 01:47  |
 genux33
Messages: 1
Registered: December 2006 |
Junior Member |
|
|
Hi,
I wrote a php script to perform load testing as following
- select * from users where user_id = 'xxxxx' //xxxxsx is randomly generated, simulating user logging in
- select pets.* from pets INNER JOIN users ON pet_owner_id='xxxxx' //load user's pets
- select messages.* from messages INNER JOIN users ON message_user_id='xxxxx' //load user's messages
- select friends.* from friends INNER JOIN users on friends_id='xxxxx' //load user's friends
- select testimonials.* from testimonials INNER JOIN testimonials on testimonial_user_id='xxxxx' //load user's testimonials
- select guides.* from guides .......... //load latest uploaded guides
- select events.* from events ....... //load latest events
Database size as following
No Database Table Number of records
1 countries 239
2 Events 20,8212
3 Friends 53,5935
4 Guides 59,388
5 Hobbies 10
6 Messages 514,488
7 Mstatus 5
8 Pets 10,715,973
9 Purpose 5
10 Testimony 200,000
11 User_to_hobbies 5,446,651
12 users 1,019,994
When i execute the script manually from browser, time taken to load the page is 1.3 sec - 1.9sec
As i did a load of 100 concurrent users, the longest transaction time is more than 50sec..
Is that any way to reduce the transaction time taken?
Regards,
Zhiwei
|
|
|
| Re: Need advise of tuning mysql performance for fast transaction time [message #495 is a reply to message #494 ] |
Thu, 21 December 2006 03:41  |
carpii
Messages: 46
Registered: November 2006 |
Member |
|
|
tune each query individually using EXPLAIN, rather than globbing them all together in a mass stress test, Maybe its one query performing badly because it has no suitable indices
also consider replacing 'select *' with 'select field1, field3 etc'. In most cases you dont need every single field returning, and
this will often reduce network traffic
|
|
|
Members
Search
Help
Register
Login
Home
