Tag Info

New answers tagged

5

Use the ONLY key word: TRUNCATE ONLY public.history_uint; Per documentation: If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its descendant tables (if any) are truncated. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are ...


1

This is an old question, but I came across this and was appalled at one particular comment made, suggesting the original asked was "confused". The question is perfectly clear, just not strictly about database administration. It falls into the fields of server virtualization and storage provisioning in virtual environments more than anything. It may well be ...


3

Below a couple of techniques that should coerce partition elimination while providing the same functionality as the IN or OR methods. These both use a JOIN instead, one with a row constructor and the other a table variable. The table variable (which could be passed as a table-valued parameter) allows a variable number of container values without changing ...


0

You can override the way DELETE commands are replicated to the subscriber by using the @del_cmd parameter for sp_addarticle. You can specify a custom stored procedure that takes care of the archive logic or ignore DELETE commands altogether by setting it to NONE. However, the rows kept at the subscriber won't be replicated any more if for some reason you ...


1

Below is a complete script that works as expected without the error. :setvar IsArchive "" :setvar Suffix _Partitioned CREATE SCHEMA Orders; GO CREATE SCHEMA ArchivedOrders; GO CREATE SEQUENCE Orders.NextOrderId; GO CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS RANGE RIGHT FOR VALUES(); GO CREATE PARTITION SCHEME ...


0

First, let's discuss what benefit you will get from partitioning. Please provide SHOW CREATE TABLE (with the partitioning). Partitioning, in itself, does not provide any performance benefits. Let's see the query/queries that you think will benefit. pt-online-schema-change is likely to be a way to convert to partitioning with virtually no downtime. But ...


0

The manual does not list a problem, but... Statement Based Replication should not have any problem since the Slave simply re-executes the DML statement without knowing or caring (up front) about whether the table is partitioned. Some rare statements are very likely to break replication: SELECT ... PARTITION pnnn -- 5.6.2 ALTER TABLE -- to change ...


2

But does the partitioning column have anything to do with order on the disk? From Clustered Index Structures : Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree ...


0

Have a partition named future that can collect any future data. But try to build the "tomorrow" partition just before midnight. (More in a minute.) Don't DROP future and then rebuild two partitions; instead do it all in one atomic REORGANIZE PARTITION to convert one partition (future) into pnnnnnnnn (tomorrow) and new copy of future (VALUES LESS THAN ...


3

By all means, partition by unixtime. Why ??? You can separate data by day You can archive the data of the oldest partition This can be scheduled in the MySQL event scheduler for adding new partitions deleting old partitions You drop the data of the oldest partition by DDL (ALTER TABLE tbalename DROP PARTITION partitionname) instead of DML (DELETE FROM ...


0

It is rather small to PARTITION, but using partitions and "transportable tablespaces", the archiving can be easy and efficient. Well, maybe more complex than using slow SELECTs and DELETEs. Use InnoDB, at least 5.6, and PARTITION BY RANGE, with a daily or weekly granularity. Many of the details are laid out in my blog.


0

More than a few thousand tables or databases is a bad idea in MySQL. This because of MySQL's dependence on the filesystem, and most OS's can get bogged down when a directory has "too many" entries. Hence, I vote for a single Blogs table. You will probably need few other tables to round out the app. Probably they will all be in a single database, but that ...



Top 50 recent answers are included