maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

yes :) but that's a start point
index  optimizations can be done with time


2014-05-21 20:36 GMT-03:00 Federico Razzoli <federico_raz@xxxxxxxx>:

> Maybe. But it cannot bet optimized. All the discarded rows must be read,
> before reaching the offset (and I suppose they're not in the buffer pool).
>
> Federico
>
>
> --------------------------------------------
> El jue, 22/5/14, Roberto Spadim <roberto@xxxxxxxxxxxxx> escribió:
>
>  Asunto: Re: [Maria-discuss] Help with history tables
>  Para: "Federico Razzoli" <federico_raz@xxxxxxxx>
>  CC: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
>  Fecha: jueves, 22 de mayo, 2014 00:47
>
>  anything
>  like delete from history where channel_id=10 order by
>  date_time desc limit 10000000000 offset 50000 ?i
>  never checked offset at delete statment
>
>
>  2014-05-21 18:43 GMT-03:00
>  Federico Razzoli <federico_raz@xxxxxxxx>:
>
>  I would use a batch operation (a cron job or an SQL event)
>  which is triggered when the workload is low. If the queries
>  are still a problem, you could relax the 50000 limit and use
>  a statistical method. For example you could periodically
>  compute how many rows per day are inserted (globally if
>  possible, otherwise for each channel). You could do this
>  once a month, for example. In the meanwhile, based on the
>  last calculated AVG, you can use faster DELETEs based on the
>  DATE column. You can do further optimizations, like
>  cleansing some channels on monday, others on tuesday, etc.
>  Everything depends on your workload and the speed of your
>  COUNT(*)s and DELETEs.
>
>
>
>
>  Federico
>
>
>
>
>
>  --------------------------------------------
>
>  El mié, 21/5/14, Roberto Spadim <roberto@xxxxxxxxxxxxx>
>  escribió:
>
>
>
>   Asunto: [Maria-discuss] Help with history tables
>
>   Para: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
>
>   Fecha: miércoles, 21 de mayo, 2014 22:32
>
>
>
>   Hi guysI have an
>
>   doubt about design
>
>   I have a history table:CREATE TABLE
>
>   HISTORY(CHANNEL INT NOT NUL DEFAULT
>
>   0,HISTORY_DATE DECIMAL(22,6) NOT NULL DEFAULT
>
>   '0' COMMENT 'UNIX
>  TIME',
>
>   VALUE DECIMAL (22,6) NOT NULL DEFAULT
>
>   0,PRIMARY KEY
>
>   (CHANNEL,HISTORY_DATE))
>
>   my question is...i need a history of
>
>   only 50000 rows for each channelwhat's the
>
>   best method to allow a good
>  history without many deletes,
>
>   analyse tables, and others stuffs to allow a good
>  table/disk
>
>   usage?
>
>
>
>   i was considering a counter, for example, after
>
>   1000 inserts, run aSELECT COUNT(*) FROM HISTORY
>
>   WHERE CHANNEL={channel_id}
>
>   get this number of rows, if it's > 50000
>
>   run:
>
>
>
>   DELETE FROM HISTORY WHERE CHANNEL={channel_id}
>
>   ORDER BY HISTORY_DATE ASC  LIMIT (number_of_rows -
>
>   50000)
>
>   any other idea?
>
>   maybe a 'batch' operation?
>
>   i was think about something like (don't work, just
>
>   a idea)
>
>   DELETE FROM HISTORY WHERE CHANNEL IN
>
>   (channel numbers)AND xxxxx? LIMIT 50000 GROUP BY
>
>   CHANNEL
>
>
>
>   i don't know anything to group by channel id
>
>   and delete rows using only one sql command , any idea?
>
>
>
>
>
>   thanks guys!
>
>   --
>
>   Roberto Spadim
>
>
>
>   SPAEmpresarialEng. Automação e Controle
>
>
>
>   -----Adjunto en línea a continuación-----
>
>
>
>   _______________________________________________
>
>   Mailing list: https://launchpad.net/~maria-discuss
>
>   Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>
>   Unsubscribe : https://launchpad.net/~maria-discuss
>
>   More help   : https://help.launchpad.net/ListHelp
>
>
>
>
>
>
>  --
>  Roberto Spadim
>  SPAEmpresarialEng. Automação e
>  Controle
>
>


-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle