php DB rotation

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a PHP server controller thatl read/write to Database table

PHP server will start read / write to a new DB table every week/monday
table-1-23-2012 ... etc

I think of 2 ways to do the DB table rotation

1) check the serve timestamp, if today's date is week of 1-23-2012,
then read/write to table-1-23-2012

2) have a unix corn job run every monday to generate a text file on
PHP server with DB table named on that date -  on each PHP request,
check the text file's table name - then read/write to that DB table

any other solution to DB table rotation?

the first way check timestamp have a drawback when server's time is
not set to correct time, since there are many PHP server running for
load balance, it's not a good idea.

the second way is a better solution, but request additional setup -
cron job

Re: php DB rotation

.oO(Jim Lee)

Quoted text here. Click to load it

The first question that comes to mind: Why table rotation? A good DBMS
can easily handle millions of records, if properly indexed.

Or do you really have millions of new records each week? If not, just
add a timestamp to your records and you can easily filter them by date.



Re: php DB rotation


Quoted text here. Click to load it

I must rotate to a new table, because there is another REST layer on
top of the DB, the REST call will have a new table-name every week to
read/write to.

my main problem now, is how to reliably compute the DB table-name?

using the server timestamp is a solution, I must make sure server time
is acurrant though.

Re: php DB rotation

Quoted text here. Click to load it
This is not a "reason", since the REST call could easily supply the
timestamp instead of having a new table name.

Re: php DB rotation

Jim Lee wrote:
Quoted text here. Click to load it

That you can do by simply tying the server in to ntp.

or you might be able to do an NTP call to the 'net directly..if it fails
I guess you use the old server name.

My machines do record reasonably weird ties at boot..I suppose they
start off the realtime clock - but that changes pretty quickly.

IIRC you CAN use ntp to update the realtime clocks on most
as to minimise the difference.

Re: php DB rotation

Am 31.01.2012 04:36, schrieb Michael Fesser:
Quoted text here. Click to load it

This subject cannot be discussed without knowing which database system is used on
which OS.

And then, go to the appropriate db group. I can tell that in the postgresql
performance groups "table partitioning" is a big subject.


Re: php DB rotation

On Mon, 30 Jan 2012 18:05:56 -0800, Jim Lee wrote:

Quoted text here. Click to load it

A cron job running from the server clock will not be any more accurate
than using the server's time in PHP or in the database itself.

If the server clock is wrong, the cron job will update the text file at
the wrong time anyway. As both methods rely on the server clock being
correct, I'd go with checking the time when you write to the table.

You don't have to wait until the day you're going to start populating it
to create the table either.


Denis McMahon

Re: php DB rotation

On 1/31/2012 3:23 AM, Denis McMahon wrote:
Quoted text here. Click to load it

Having just read this same poster ask the same question on, but claiming it was for a Java controller,
I'm guessing troll.

Site Timeline