Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #4688 > unrolled thread

php DB rotation

Back to article view | Back to comp.lang.php

Contents

  php DB rotation Jim Lee <jimlee2907@yahoo.com> - 2012-01-30 18:05 -0800
    Re: php DB rotation Michael Fesser <netizen@gmx.de> - 2012-01-31 04:36 +0100
      Re: php DB rotation Jim Lee <jimlee2907@yahoo.com> - 2012-01-30 20:03 -0800
        Re: php DB rotation Captain Paralytic <paul_lautman@yahoo.com> - 2012-01-31 02:25 -0800
        Re: php DB rotation The Natural Philosopher <tnp@invalid.invalid> - 2012-01-31 14:00 +0000
      Re: php DB rotation "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-01-31 09:38 +0100
    Re: php DB rotation Denis McMahon <denismfmcmahon@gmail.com> - 2012-01-31 09:23 +0000
      Re: php DB rotation Chris Riesbeck <Chris.Riesbeck@gmail.com> - 2012-01-31 13:00 -0600

#4688 — php DB rotation

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
e.g. 
table-1-2-2012
table-1-9-2012
table-1-16-2012
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 

[toc] | [next] | [standalone]

#4690

.oO(Jim Lee)

>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
>e.g. 
>table-1-2-2012
>table-1-9-2012
>table-1-16-2012
>table-1-23-2012 ... etc
>
>I think of 2 ways to do the DB table rotation

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.

Micha

-- 
http://mfesser.de/blickwinkel

[toc] | [prev] | [next] | [standalone]

#4691

On Tue, 31 Jan 2012 04:36:37 +0100, Michael Fesser <netizen@gmx.de>
wrote:

>.oO(Jim Lee)
>
>>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
>>e.g. 
>>table-1-2-2012
>>table-1-9-2012
>>table-1-16-2012
>>table-1-23-2012 ... etc
>>
>>I think of 2 ways to do the DB table rotation
>
>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.
>
>Micha

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.

[toc] | [prev] | [next] | [standalone]

#4695

On Jan 31, 4:03 am, Jim Lee <jimlee2...@yahoo.com> wrote:
> 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.
This is not a "reason", since the REST call could easily supply the
timestamp instead of having a new table name.

[toc] | [prev] | [next] | [standalone]

#4696

Jim Lee wrote:
> On Tue, 31 Jan 2012 04:36:37 +0100, Michael Fesser <netizen@gmx.de>
> wrote:
> 
>> .oO(Jim Lee)
>>
>>> 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
>>> e.g. 
>>> table-1-2-2012
>>> table-1-9-2012
>>> table-1-16-2012
>>> table-1-23-2012 ... etc
>>>
>>> I think of 2 ways to do the DB table rotation
>> 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.
>>
>> Micha
> 
> 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.

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 hardware..so 
as to minimise the difference.

http://linux.die.net/man/8/hwclock

[toc] | [prev] | [next] | [standalone]

#4692

Am 31.01.2012 04:36, schrieb Michael Fesser:
> .oO(Jim Lee)
> 
>> 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
>> e.g. 
>> table-1-2-2012
>> table-1-9-2012
>> table-1-16-2012
>> table-1-23-2012 ... etc
>>
>> I think of 2 ways to do the DB table rotation
> 
> 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.
> 
> Micha
> 

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.

/Str.

[toc] | [prev] | [next] | [standalone]

#4694

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

> the first way check timestamp have a drawback when server's time is not
> set to correct time ...

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

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.

Rgds

Denis McMahon

[toc] | [prev] | [next] | [standalone]

#4699

On 1/31/2012 3:23 AM, Denis McMahon wrote:
> On Mon, 30 Jan 2012 18:05:56 -0800, Jim Lee wrote:
>
>> the first way check timestamp have a drawback when server's time is not
>> set to correct time ...
>
>> the second way is a better solution, but request additional setup - cron
>> job
>
> 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.

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web