sql - Using MySQL Partitioning to speed up concurrent deletes and selects? -

-

i have mysql innodb table contains 8.5 million rows. table structure looks this:

create table `mydatatable` (   `ext_data_id` int(10) unsigned not null,   `datetime_utc` date not null default '0000-00-00',   `type` varchar(8) not null default '',   `value` decimal(6,2) default null,   primary key (`ext_data_id`,`datetime_utc`,`type`),   key `datetime_utc` (`datetime_utc`) ) engine=innodb default charset=utf8;

every night, delete expired values table following query:

delete mydatatable datetime_utc < '2013-09-23'

this query not seem use indizes, , takes quite time run. concurrent updates , selects on same table. these locked then, causing website unresponsive @ time.

i looking various ways speed setup. cam across mysql partitioning , wondering if fit. adding , selecting newer data table , deleting old ones. create partitions based on mod(dayofyear(datetime),4). when delete, delete values partition 1 reading or writing from.

will experience locking setup? partitioning improve query speed , availability in case? or should solution, , if so, one?

since mysql 5.5 use function columns, simplifies partitioning non-integer columns (such datetime_utc).

as performance:

  • dropping partition constant time operation list , range partitioning. speed equivalent of truncate table or rm file, practically independent of size of partition.
  • doing select on partitioned table benefits partition pruning, read partition match search criteria. can speed range scans.

tip:

do not forget add "default" partition, such as

    partition the_last_one values less than(maxvalue)

in order avoid insert/update statements fail since no partition found insert into.


Comments

Post a Comment

Popular posts from this blog

c# - How Configure Devart dotConnect for SQLite Code First? -

-
can configure following sample code ? when run following code, error : no entity framework provider found ado.net provider invariant name 'devart.data.sqlite' setting in machine.config <system.data> <dbproviderfactories> <add name="dotconnect sqlite" invariant="devart.data.sqlite" description="devart dotconnect sqlite" type="devart.data.sqlite.sqliteproviderfactory, devart.data.sqlite, version=4.6.287.0, culture=neutral, publickeytoken=09af7300eec23701" /> </dbproviderfactories> </system.data> after add below block code in app.config <?xml version="1.0"?> <configuration>   <configsections> <!-- more information on entity framework configuration, visit http://go.microsoft.com/fwlink/?linkid=237468 --> <section name="entityframework" type="system.data.entity.internal.configfile.entityframeworksection, entit...
Read more

c++ - Clear the memory after returning a vector in a function -

-
i have function processes , stores lot of data in , returns results vector of class. amount of data stored in function tremendous , want clear storage memory of function after finished job. necessary (does function automatically clear memory) or should clear memory function? update: vector<customers> process(char* const *filename, vector<int> id) { vector<customers> list_of_customers; (perform actions) return list_of_customers; } variables defined locally within function automatically released @ end of function scope. destructors objects called, should free memory allocated objects (if objects coded correctly). example of such object std::vector . anything you've allocated new must have corresponding delete release storage. try avoid doing own allocation , use raii instead, i.e. containers or smart pointers.
Read more

erlang - Saving a digraph to mnesia is hindered because of its side-effects -

-
updated the digraph library in erlang implements data type side-effectful. each operation on data type saves new state in ets table -- instead of returning altered version of itself, more common in functional languages. the problem usage perspective, impedes effort store or pass around state in convenient way requiring me first "collect" state before can start juggle around. the closest solution have seen far serializer/deserializer , these have drawback tied current structure of digraphs instead of operating on abstract type -- prevents future proof solution. update pascal pointed out serializer utilizes interface of digraph, , hence eliminates drawback mentioned above. better, albeit still inconvenient, see no better alternative. what recommendation on how store digraphs? should go different data type altogether? thanks. i not sure understand concern. fact digraph stored in ets transparent in user interface, might want chnge either exchange digr...
Read more