maria-discuss team mailing list archive

[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