in reply to Re: Database poller
in thread Database poller

It is in general a bad idea to create one (identical) table for each client (or one table for each day, etc.)

Yes, this probalby provides a short-term performance gain, but it will lead to serious scalability and administrative headaches, and is really not the way a relational database should be used.

If the database server is the problem then you should see what can be done to tune the server to perform better (indexes, TCP options, etc). And is it really necessary to query the database for each client 5 times per second?

Michael

Replies are listed 'Best First'.
Re: Re: Re: Database poller
by Marcello (Hermit) on Jun 16, 2003 at 11:01 UTC
    Hi mpeppler,

    Messages should be delivered to clients asap, so at least 2 times per second. The problem is not the database at the moment, but I want a scalable solution. If every client process checks the database this way, it will eventually become a performance issue. Therefore, one process checking the database allows for many clients to connect at once.

    Regards, Marcel
[reply]
Re: Re: Re: Database poller
by aquarium (Curate) on Jun 17, 2003 at 12:13 UTC
    i'm sorry but you are wrong...it produces a long-term performance gain. if you have a 100 clients, yes you will need to custom build some admin interface -- you will also want one if you're using a single table, and have a jumble of messages in it. Also, with one possibly large and much sought after table, if any client mis-behaves and slows things down, all clients will suffer. you also need to be very careful when administering messages for one client as not to affect others and corrupt their data too. Scalability? if your prog is modular enough you can farm out client tables to separate databases on separate boxes. One other thing that can be done to improve performance is to have a "already read" field that you check for when retrieving messages, and only every now and then delete all those "already read" messages in one update query, so the rdbms is not shuffling data every time you take off a message from the table. The data contention issue is the most likely bottleneck you will encounter with your current design. as the number of clients grows, performance will drop very quickly for each new client addition. sometimes you do need to de-normalize your data to suit real world applications. in fact, relational theory calls for de-normalization after first normalizing your data, if that is necessary. in this case, it is.
[reply]
      I respectfully disagree. Yes, you may need to denormalize, but it isn't the same thing as having multiple identical tables - I still contend that that's a bad idea.

      You can still partition the data over multiple servers to achieve better performance, but there shouldn't really be any performance difference between having a single table (with the proper index) and having multiple tables on a single table- unless you have a system where lock granularity is not at the row-level, as with the proper index the same amount of data needs to be read whether you hit a common table or your client's private table.

      However all this drifts away from perl, and so isn't really all that pertinent here...

      Michael

[reply]

In Section Seekers of Perl Wisdom