Slow Performance with multiple users

[webuserdan]

We have developed a site that sometimes gets up to 15,000 visitors in one day and when it gets really busy the server slows to a crawl and we have to take the site offline.

The site runs Joomla 1.5 and Virtuemart but we have a Jumi ap on the homepage that shows only 1 product per day (which it gets based on the dates set in virtuemart).

We don't usually work with sites like this and are bit stuck on how to increase Joomla's performance in this issue.

This site is on its own high spec expensive server which runs an Intel Core i7 920 (2.66 GHZ 8MB L3 Cache) processor and 12 Gig RAM 2 x x250 SATAII 7.2k Hard drives.

When it last happened we asked the data centre what was causing the problem. They replied saying that for some reason the tables were becoming locked. I've included the log showing locked tables (the actual accountname has been replaced with the words accountname for privacy reasons).

4 | eximstats | localhost | eximstats | Sleep | 22 | | |
| 16 | accountname_user | localhost | accountname_website | Query | 22 | Copying to tmp table | SELECT M.* FROM `jos_users` AS M LEFT JOIN `jos_acajoom_subscribers` AS N ON M.email = N.email WHE |
| 32 | accountname_user | localhost | accountname_website | Query | 20 | Locked | UPDATE jos_users SET lastvisitDate = '2010-02-09 23:26:12' WHERE id = 481 |
| 47 | accountname_user | localhost | accountname_website | Query | 18 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='[email protected]' |
| 49 | accountname_user | localhost | accountname_website | Query | 18 | Locked | SELECT * FROM jos_users WHERE id = '1234' |
| 60 | accountname_user | localhost | accountname_website | Query | 17 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='Chemiluminescence' |
| 67 | accountname_user | localhost | accountname_website | Query | 16 | Locked | SELECT * FROM jos_users WHERE id = '6755' |
| 70 | accountname_user | localhost | accountname_website | Query | 15 | Locked | SELECT * FROM jos_users WHERE id = '1420' |
| 83 | accountname_user | localhost | accountname_website | Query | 13 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='Mylk' |
| 93 | accountname_user | localhost | accountname_website | Query | 12 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='benji8' |
| 94 | accountname_user | localhost | accountname_website | Query | 12 | Locked | UPDATE jos_users SET lastvisitDate = '2010-02-09 23:26:20' WHERE id = 4047 |
| 100 | accountname_user | localhost | accountname_website | Query | 11 | Locked | SELECT * FROM jos_users WHERE id = '6834' |
| 101 | accountname_user | localhost | accountname_website | Query | 11 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='minh.t.au' |
| 109 | accountname_user | localhost | accountname_website | Query | 10 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='ratnagiri' |
| 119 | accountname_user | localhost | accountname_website | Query | 8 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='Mylk' |
| 144 | accountname_user | localhost | accountname_website | Query | 5 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='vsia2165' |
| 145 | accountname_user | localhost | accountname_website | Query | 5 | Locked | SELECT `id`, `password`, `gid` FROM `jos_users` WHERE username='ericajill3' |
| 167 | accountname_user | localhost | accountname_website | Query | 1 | Locked | SELECT * FROM jos_users WHERE id = '6852' |
| 171 | accountname_user | localhost | accountname_website | Sleep | 0 | | |
| 172 | root | localhost | | Query | 0 | | show processlist |

Has anyone encountered this or has any opinions on how we can improve the performance of Joomla in this scenario?


*UPDATE*
I have also slightly increased some of the buffer sizes for mysql to hopefully speed up any "copying to tmp table" processes that may occur. Hopefully that will help. Ultimately this is going to be an issue with whatever code is making these database requests, which is something we wouldn't be able to handle. Please let me know if these adjustments improve your performance at all.

[HolmesA]

You need to analyze queries that take long time and add indexes to the database.
"copying to tmp table" means that mysql is creating a temporary table on disk which is very slow. Most of the time it is either GROUP BY or SORT command with no appropriate tables indexes that cause Mysql to create a tmp table.

[webuserdan]

Hey mate thanks for that, we turned on slow query logging and found that the slow queries belonged to an Acajoom user Sync. I asked them what it was for but they couldn't really tell me so we are moving away from Acajoom and having a stand-alone eMarketing solution instead and see how that goes.

Thanks again.

[vipinsahu]

sql tuning is a good concept . You can also add a hight performance cache handler as well (Memcache , xcache) and don't forget to check your apache keep alive is on or not and with proper setting

[ryadavalli]

I know this is an old post, but we are encountering the same issue. Locked users table when many users are trying to login. This seems to be degrading the performance. We are on Joomla 1.5. Has anyone seen this issue and has experience in dealing with it?