[EMAIL PROTECTED] wrote:
"Paul Halliday" <[EMAIL PROTECTED]> wrote on 14/03/2006 12:09:10:
As an example:
There was a table called event.
This table is now broken up like this:
event _<sensor>_<date>.
So for every sensor, and every day, there is now a new table. So if I
have 20 sensors, every day I will have 20 new tables.
With this in mind, does this design make sense?
how will this scale?
Is there anything I can do through configuration (I doubt the
developer will change the design) to speed things up? or a workaround
that I could do on my end to compensate?
Could you explain how this is meant to improve scalability? Because to my
mind it is probably the best way I can imagine to make the system
unscaleable. To me, this design very much does *not* make sense.
You have bought, in MySQL, a highly tuned specialist engine for seqrching
and sorting stuff in the most efficent manner. And then you have said that
you will disable all its optimisation and force it into a linear search.
I can think of a reason for doing this but not to extent described. Is
your developer trying to create a situation where it's easy to archive
of results earlier than a given day? So you store say 1000 days of data
and can quickly archive the oldest day at midnight each day.
Assuming this is the case: There's no point splitting further than by
day so tables per day/sensor don't make any sense unless your worried
about sub second locking (i.e. doing it wrong). You should make the
unmerged tables as large as possible without the time to delete having
an impact on your application. Having an impact depends on your
applications tolerence to locking and the amount of data your adding and
removing, you'll need to find it by testing. The table type you use will
have a big impact on concurrent access locks. MyiSAM and Innodb are the
two main candidates MyISAM is quick but is doesn't allow concurrent
access to the table. Innodb will allow concurrent access but still locks
rows and can lock the 'head point' during certain inserts.
The fact your storing sensor data worries me. How tolerent of
lag/locking on insert or retreval is your application? If it's sensitive
to more than a seconds lag you need a careful review of your design. If
it's hard real-time sack the developer then review the design.
Hope this helps
Nigel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
