NYCPHP Meetup

[Mitch Pirtle]

Joseph Crawford Jr. wrote:

>Better yet just count the files that are in your session save directory ;)
>

I'd love to, but I need to aggregate those counts in groups ("125 people 
in group A" etc.).

One of the biggest issues is lock contention on the accounts table, as 
it is constantly getting hammered with SELECTs ('SELECT count(*) FROM 
accounts WHERE group = 3 AND online = 1'), and constantly getting 
hammered with UPDATEs when 10,000 people show up and login ('UPDATE 
accounts SET online = 1 WHERE id = 12323').

Ultimately, the output would include:

group 1   3,545 online
group 2   4,292 online
group 3   5,123 online
...and so on...

The site gets over 3.5 million page views daily, and averages around 
10,000 people logging in daily.  Served on two dual Xeon servers, the 
hardware is fine, but the statistics in MySQL show that InnoDB has some 
major lock issues:

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1823, signal count 1709
Mutex spin waits 153589, rounds 772170, OS waits 437
RW-shared spins 1276, OS waits 587; RW-excl spins 531, OS waits 103


These stats were collected right after a restart in the morning, with 
less than 1,000 people online.

I'm redesigning the site with fixes to the most glaring architectural 
issues (using classes instead of redundant includes scattered randomly 
about the filesystem is a big plus), and also will be switching to 
PostgreSQL ;-)

So again, my biggest dilemma is deciding how to separate the account 
info that is static (username, email) with stuff that is constantly 
changing (online status, last logged in timestamp).  Any pearls of 
wisdom out there?

-- Mitch