| php DB rotation [message #176874] |
Tue, 31 January 2012 02:05  |
Jim Lee
Messages: 2
Registered: January 2012
Karma: 0
|
Junior Member |
|
|
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
|
|
|
|
| Re: php DB rotation [message #176876 is a reply to message #176874] |
Tue, 31 January 2012 03:36  |
Michael Fesser
Messages: 215
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
.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
|
|
|
|
| Re: php DB rotation [message #176877 is a reply to message #176876] |
Tue, 31 January 2012 04:03 |
Jim Lee
Messages: 2
Registered: January 2012
Karma: 0
|
Junior Member |
|
|
On Tue, 31 Jan 2012 04:36:37 +0100, Michael Fesser <netizen(at)gmx(dot)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.
|
|
|
|
| Re: php DB rotation [message #176878 is a reply to message #176876] |
Tue, 31 January 2012 08:38 |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
| Re: php DB rotation [message #176879 is a reply to message #176874] |
Tue, 31 January 2012 09:23 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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
|
|
|
|
| Re: php DB rotation [message #176880 is a reply to message #176877] |
Tue, 31 January 2012 10:25 |
Captain Paralytic
Messages: 204
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
| Re: php DB rotation [message #176881 is a reply to message #176877] |
Tue, 31 January 2012 14:00 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
Jim Lee wrote:
> On Tue, 31 Jan 2012 04:36:37 +0100, Michael Fesser <netizen(at)gmx(dot)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
|
|
|
|
| Re: php DB rotation [message #176884 is a reply to message #176879] |
Tue, 31 January 2012 19:00 |
Chris Riesbeck
Messages: 3
Registered: September 2011
Karma: 0
|
Junior Member |
|
|
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.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]