maria-discuss team mailing list archive

[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