|
|
Posted by Erwin Moller on 11/24/06 09:03
Sandman wrote:
> In article <4565b82b$0$325$e4fe514c@news.xs4all.nl>,
> Erwin Moller
> <since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
> wrote:
>
>> First: Prefetching all members into memory for each page where you need
>> one or two members is absolutely a no-go. You must change that of course.
>> Just fetch the information you need WHEN you need it.
>
> Yes, but if I need to fetch information for 100 members on one page
> making 100 individual SQL queries is also a no-go.
Hi Sandman,
As Jerry and Micha indicated: Do not fetch all members into PHP memory each
page, just fetch the ones you need.
I made a few community sites that had excactly the same problems as you are
facing now, and good design solves everything.
(Luckily I designed it right from the start, in your case you must change
existing code which is always a lot more trouble.)
A few pointers that might speed things up:
1) At the start of your page decide which articles or user you need on that
page.
2) Make a query that only fetches that information.
3) Do NOT store the array (or resultset, or whatever approach you have to
represent data from the database) in a session.
Reason: I have the impression this datastructure is huge. If you are using
filebased sessionstorage (which is default), you are asking PHP to
serialize that data, safe it in a file, and unpack it next invocation of
any script that uses sessions.
This is serious overhead and could easily be a significant part of your 0.8
seconds.
4) Design your database smart. I mean by this: Design your database in such
a way that the queries you typically need run fast.
Did you normalize your database? (In case you don't know what that is: One
important thing about normalization is the fact that you represent each
piece of data only in one place(=table/column) and make the relations by
means of primary and foreign keys.)
The more joins, the slower the queries.
SO it might help to duplicate some data (denormalization) in some tables.
This can help a lot, but use it cautiously, since a good designed database
stay as close to 100% nomalized as possible.
5) Indexing a few columns might also give a huge speedadvantage.
If you are familiar with profilers, use one. MySQL, Postgres, all databases
have some tools that tell you WHAT they are doing and how much time it
takes.
If you find out that a certain column/tablescan takes up 50% of the time or
so of the total querytime, then you know that is a firstclass candidate for
indexing.
6) Prepared statements can also give some speedincrease.
7) Prepared data in the database (Last resort)
Do not use this method unless the others do not help enough.
I don't like this method, but was forced to use it myself in a few
occasions.
I'll describe a case I had and how I solved it.
It is a huge discussionplatform, and all participants had the possibility to
vote on articles of others. The weight their vote carried varried on a lot
of factors and the logic was also totally recursive, meaning that if one
visitor did one thing (eg read an article, or make a vote), all others had
to be updated.
So realtime calculation of the whole system was no option for me.
I decided to create a table that contained the calculated data, use that
data whenever I need it, and run an update every hour or so.
This method is not considered 'good programming' (at least by me), but gets
the job done, and in some situation you have no choice.
If all other approaches fail, you might do the same: create a table that
contains all data assembled excactly in the way you need it. NOT A VIEW! A
real table.
Hope that helps.
Regards,
Erwin Moller
>
>> If I understand correctly your main problem lies in the fact you must
>> fetch the online/offline information for each possible user.
>> Correct?
>
> No, there are lots of other information also being fetched. The
> online/offline status is kept in a seperate db and isn't a problem at
> all actually. Nicknames, labels, ages, full name and such things are
> what is needed. All of that is in the member DB
>
>> This is how I solved this problem when I was in a similar situation:
>> (a rough quick overview to show the idea)
>>
>> 1) Create a table like
>> CREATE TABLE tblOnlineUsers(
>> userid integer REFERENCES tbluser.userid,
>> lastActive timestamp
>> )
>>
>> 2) Whenever a logged-in user does something, update the timestamp in this
>> table for that userid.
>
> That's exactly what I'm doing.
>
>> 3) When you are on a page that needs to know the status of online users,
>> just load it into a array with a query like:
>> SELECT userid FROM tblOnlineUsers WHERE (
>> lastActive > XXX
>> )
>>
>> Where XXX is (now - your sessiontimeout).
>>
>> Now you need some logic to delete the userids for stale users.
>
> I do that in my five minute aggregation script (that aggregates lots
> of stuff for the site). There I delete from member.surfers where
> datetime < date_sub(now(), interval 10 minute)
>
>> (Users that didn't log out, but just closed their browser or walked away
>> to get a nice good lunch of three hours)
>> eg
>> When a random number between 0 and 1 is smaller than 0.1 you delete all
>> records older than the sessiontimeout.
>>
>> You also need to make sure the userid in that table is unique.
>>
>> But this solution has the advantage that:
>> - You don't load all users into memory for each scriptinvocation
>> - Your tblOnlineUsers can be searched very quickly.
>
> Yeah, I'm already doing that. I still need some way to get information
> about arbitrary user X whenever I need it. One way would be to just
> print "[[MEMBERINFO:123]]" and then save "123" in an array, fetch
> information about all id's in that array and then preg_replace() them
> all in the output buffer. But that's not very intelligent either,
> since a SQL query with "select x, x, x from member where id in(<list
> of 100's of ids>)" isn't very good.
>
> So, basically, I need a function to get information about user X from
> a member count of tens of thousands while not being time consuming if
> I do it one time on a page or if I do it 100 times on a page.
>
>
>
>
>
>
>
>
>
Navigation:
[Reply to this message]
|