maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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
SPAEmpresarial
Eng. Automação e Controle