Opened 9 years ago

Closed 9 years ago

Last modified 5 years ago

#227 closed improve feature (fixed)

Slow response searching for members

Reported by: matrixpoint Owned by:
Priority: major Milestone: Legacy Resolved
Component: BW Search Keywords:
Cc: tobixen, jeanyves

Description

I noticed during testing how slow searchmembers was on the test server compared to my laptop (localhost). I thought this would improve on bull, but it has not.

Doing a Global search (or a map based search of the world) takes about 8 (7) seconds on test, alpha and bull, but only 2 (1) seconds on localhost. Last night when I did some testing on bull, some searches didn't even complete. (This might be a temporary effect of the transition to bull, so I won't classify this ticket as a bug for now.)

Only 3 users were online on bull at the time, so the problem couldn't be load related.

This search shouldn't take so long. On CS, most searches completed within about 2-3 seconds, even though the database contained 250,000 members (instead of 2,000) and perhaps 100 members online (instead of 3).

This problem may be system-wide, not just a problem with searchmembers.

It would be helpful for solving speed problems like this to have a report available to the developers at the bottom of every page showing the time used by each db query. As the site grows, speed issues will become more and more important. Perhaps another ticket should be opened for this feature request.

Change History (17)

comment:1 Changed 9 years ago by matrixpoint

  • Type changed from improve feature to bug

Testing again today on production, a global search never returned, so I'm changing this to a bug.

comment:2 Changed 9 years ago by matrixpoint

Checked again today and things are different. Results for global search (2 rounds of tests):

Production: 2 sec
Alpha: 8-10 sec
Test: 2 sec.

It is more difficult to track such problems on test than on localhost, and even more difficult on alpha and production. Now that test is responding, I can't do as much, so will just see if the problem reoccurs on test.

comment:3 Changed 9 years ago by matrixpoint

More extensive testing on the production server today. After many rounds of testing using different sorting criteria, I observed that usually the results would appear in 6-12 seconds, but occasionally the results never appear.

When I logged out, the search results always appeared and much quicker. The result set is much smaller when logged out.

I would conclude from this that there is a serious speed problem with the DB queries in the searchmembers application that will get worse exponentially as new members register. It's conceivable that there are no indexes on the production DB member table for the order by fields used in searchmembers queries. More likely is that queries with joins are too slow and must be rewritten, and possibly new fields added to the members table (such as country name) to avoid joining.

comment:4 Changed 9 years ago by matrixpoint

I increased the number of members in my localhost DB from 39 to 1950, close to the current number of members on alpha/production. I ran several searchmember global searches using several different fields for sorting. The search results were returned from the DB in about 0.18 seconds.

Even though the queries will eventually need to be optimized when the number of members becomes much larger, the number (about 2000) is small enough now that the delay on alpha/production must have another cause. It can't be overloading, because there are so few users now.

Perhaps someone who has access to alpha/production could look into this further.

comment:5 Changed 9 years ago by micha

  • Milestone changed from 0.1.1-outreach-bugfixing to BigPicture

comment:6 Changed 9 years ago by matrixpoint

In #338 DB queries with execution times accurate to tenths of milliseconds are available at the bottom of each page. This should help track down the speed bottlenecks.

comment:7 Changed 9 years ago by matrixpoint

I uploaded the modification to searchmembers/ajax.php to test in r3770. Using the URL http://test.bewelcome.org/searchmembers/ajax/queries generates a query list for a typical member search. But it won't be really useful until moved to alpha.

comment:8 Changed 9 years ago by matrixpoint

Rev. r2775 has improved DB query listing for search members on the test server. Now, the query for an arbitrary search can be seen in the member list area below the map if the link http://test.bewelcome.org/searchmembers/index/queries is used.

comment:9 Changed 9 years ago by matrixpoint

  • Cc tobixen jeanyves added

Well here is the first result on alpha. A sample searchmembers query on alpha:

11: (5165.1 ms) select SQL_CALC_FOUND_ROWS count(comments.id) as NbComment?,members.id as IdMember?,members.BirthDate?,members.HideBirthDate?,members.Accomodation,members.Username as Username,date_format(members.LastLogin?,'%Y-%m-%d') as LastLogin?,cities.latitude as Latitude,cities.longitude as Longitude,cities.Name as CityName?,countries.Name as CountryName?,ProfileSummary?,Gender,HideGender? from (members,cities,countries) left join comments on (members.id=comments.IdToMember?) where members.Status='Active' and members.HideBirthDate?='No' and cities.id=members.IdCity? and countries.id=cities.IdCountry? group by members.id order by members.created limit 0,10

On my laptop:

11: (0.5 ms) select SQL_CALC_FOUND_ROWS count(comments.id) as NbComment?,members.id as IdMember?,members.BirthDate?,members.HideBirthDate?,members.Accomodation,members.Username as Username,date_format(members.LastLogin?,'%Y-%m-%d') as LastLogin?,cities.latitude as Latitude,cities.longitude as Longitude,cities.Name as CityName?,countries.Name as CountryName?,ProfileSummary?,Gender,HideGender? from (members,cities,countries) left join comments on (members.id=comments.IdToMember?) where members.Status='Active' and members.HideBirthDate?='No' and cities.id=members.IdCity? and countries.id=cities.IdCountry? group by members.id order by members.created limit 0,10

We have a big problem here: 5+ seconds vs. 0.5 milliseconds. A factor of about 10,000 times slower on alpha!

comment:10 Changed 9 years ago by tobixen

  • follow_up changed from none to test

Fixed :-) Seems like someone made a typo when creating indexes on the comments table.

I just love optimizing databases, so keep them coming :-)

comment:11 Changed 9 years ago by matrixpoint

Thanks, Tobias, that was fast. From alpha:

11: select SQL_CALC_FOUND_ROWS count(comments.id) as NbComment?,members.id as IdMember?,members.BirthDate?,members.HideBirthDate?,members.Accomodation,members.Username as Username,date_format(members.LastLogin?,'%Y-%m-%d') as LastLogin?,cities.latitude as Latitude,cities.longitude as Longitude,cities.Name as CityName?,countries.Name as CountryName?,ProfileSummary?,Gender,HideGender? from (members,cities,countries) left join comments on (members.id=comments.IdToMember?) where members.Status='Active' and members.HideBirthDate?='No' and cities.id=members.IdCity? and countries.id=cities.IdCountry? group by members.id order by members.created limit 0,10 (77.0 ms)

So much better!

I'd like to do some more testing with other indexes (sorting) but the map no longer works on alpha (bad Google key) so I can't vary the search parameters yet and get a query list. There is another ticket with a new key waiting to be installed. I'll Cc it to you.

comment:12 Changed 9 years ago by micha

Alpha google maps work again. So go on testing this ticket? closing it? marking it "improve feature" instead of "bug"?

comment:13 Changed 9 years ago by matrixpoint

  • Type changed from bug to improve feature

comment:14 Changed 9 years ago by matrixpoint

  • Resolution set to fixed
  • Status changed from new to closed

comment:15 Changed 9 years ago by matrixpoint

  • follow_up changed from test to none

comment:16 Changed 9 years ago by philipp

  • Milestone changed from BigPicture to unassigned

Milestone BigPicture? deleted

comment:17 Changed 5 years ago by TimLoal

  • Milestone changed from unassigned to Legacy Resolved
Note: See TracTickets for help on using tickets.