Splitting a database table into multiple segments for performance or manageability.

learn more… | top users | synonyms (1)

0
votes
3answers
65 views

Redesign basic Mysql storage architecture

We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this: capture_table ---------- ...
1
vote
1answer
183 views

Archive partition before delete it?

I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use ...
0
votes
0answers
48 views

Adding Partitions to an Existing Filegroup

For maintenance reasons, the organization that controls the servers where I work has decided to limit the number of file groups and files for partitioning to just 1 of each. Their reason for this is ...
5
votes
2answers
3k views

Partitioning a Table Referenced by a Foreign Key

I have one table: CREATE TABLE [dbo].[entry] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [EntryDate] [datetime] NOT NULL, [createddate] [datetime] NOT NULL, CONSTRAINT [PK_Entry_ID] ...
1
vote
0answers
169 views

Is it best practice to include partition keys within foreign keys when referencing partitioned tables?

The large tables in our database are partitioned by an id in order to separate data and speed up IO. The table of the data is partitioned by the partition key, but the primary key is only on an ...
3
votes
2answers
316 views

mysql - is it possible to create a partition expression based on two keys / indexes?

if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like: CREATE TABLE `t1` ( id_1 bigint unsigned not null, ...
2
votes
1answer
29 views

Obtaining the next partition number

For a partitioned table, how do I determine via T-SQL what is the next partition number that will be used? For example, the switch-in today went into partition number 50. I want to build the new ...
1
vote
1answer
1k views

MySQL 5.6.17 running out of memory when opening table with many partitions

We are currently setting up a MySQL database (version 5.6.17) on an Amazon db.m1.large RDS instance for storing time series data. Each time series property of an object is stored in a separate InnoDB ...
0
votes
0answers
405 views

Copying a table with its structure, indexes, and associated triggers?

I have studied this post on Stack Overflow: Fastest way to copy a table in MySQL. But no answer is copying everything like the table's structure, data, indexes as well as associated triggers. Is it ...
0
votes
0answers
163 views

How do triggers work on partitioned tables?

I have partitioned a table on the basis of a column. This column can either have 0 or 1. So, only two partitions (p0, p1) have been created internally. On this table there are 3 triggers which execute ...
0
votes
2answers
536 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
2
votes
1answer
300 views

Is there a difference between RANGE and LIST method for partitioning by consecutive integer values?

I have a table with a year column that has consecutive values -- 2000, 2001, and so on. It's a read-heavy MyISAM table, with 25+ million rows for every value of year and a comparable set inserted in ...
1
vote
1answer
305 views

Paritioning performance for full table scan on non-paritioned index

We have a table in Postgres that we are considering partitioning. We occasionally query an indexed field that is separate from one with the partition key. My question is: Can partitioning the table ...
1
vote
0answers
349 views

SQL Server : ALTER TABLE tablename SWITCH PARTITION 2 to tempempty

Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file ...
4
votes
4answers
267 views

Are we a good fit for SQL Server Enterprise and table partitioning?

We currently have a production database that contains millions of records (14 million in the largest table). One function of the database is to provide time sheet information so a single table could ...
1
vote
2answers
9k views

How to remove a table partition

I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure ...
11
votes
1answer
219 views

How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000) In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest ...
2
votes
1answer
875 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
6
votes
1answer
8k views

ALTER TABLE … SWITCHing from regular table to partitioned table fails

The code below does the following: Creates a database play_partition in C:\TEMP Creates two identical partitioned tables play_table and archive_play_table Switches play_table partition 1 to ...
2
votes
1answer
2k views

Is DBCC SHOWFILESTATS an accurate way to see unused space?

We have a few tables each with near 100 million rows of data. A co-worker has these tables setup with partitions based on a date field. Our disk space is starting to run low and knowing that ...
1
vote
1answer
501 views

PostgreSQL partition pruning?

In postgres partition is done by child tables. My problem are, when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than ...
3
votes
1answer
1k views

Partitioning large mysql table

I have a large (40GB) table I would like to partition. The rows are essentially write once read many. Currently I am using mysql 5.5 innodb with file_per_table. It's tough to optimize this table since ...
1
vote
1answer
38 views

Are there any inherently non-partitionable datasets?

Are there any practical datasets that, by their nature, cannot be partitioned in any practical way? I am wondering about this because such datasets would be ill-suited to scaling out on commodity ...
-1
votes
1answer
1k views

Oracle Partition Table Space Issue

I have a table with list partitions on it. When i insert data i see that all the partitions have default initial size of 8 MB and I also noticed that when i truncate full table and check size it still ...
2
votes
1answer
126 views

Best Practice in storage allocation for compressed and partitioned tables

I have a fairly large database that basically consists of one table, this table is paritioned by month and all partitions are compressed with page compression. After the initial load of the data there ...
2
votes
0answers
82 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders (...
0
votes
1answer
109 views

MySQL 5.1: How to rollback partitioned table

I have partitioned a table on the basis of one flag column have value either 0 or 1. Now, I want to roll back these tables. How can I do this?
3
votes
1answer
384 views

Drop partition scheme with orphan indexes

I have a partition scheme in my SQL Server database that I am trying to drop. This scheme was used by 2 indexes, which I deleted. I can't delete the scheme, since it appears to still be used by the ...
1
vote
1answer
121 views

What is a “leaf expression”?

What is a "leaf expression"? Partition-clause expressions are limited in the following ways: Your partition-clause must calculate a single non-null value for each row. You can reference multiple ...
0
votes
1answer
314 views

Oracle DB - Is it possible to distribute data of a table onto multiple servers?

BigTable[1]-like systems (HBase[2] for instance) allows a table to be distributed transparently accross multiple servers. This works as follows: Each server perform the read/writes operations on ...
0
votes
1answer
458 views

How to split the Wordpress database?

FYI - I originally posted this question under the Wordpress Development site here: http://wordpress.stackexchange.com/questions/145861/how-to-split-the-wordpress-database I have a site that is used ...
0
votes
1answer
77 views

whether to merge partitioned table with only partially overlapping columns

I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL) in general. I have data with a few "cross-sectional" ...
0
votes
1answer
2k views

How to get accurate row counts in partitions in a table

I am trying to fetch row counts in each partition for a table. But I got confused as in sys.partitions row counts is on basis of indexes. So how can I get exact row count for partitions in a table.
0
votes
1answer
73 views

Does partitioning sometimes require denormalization?

Specific example of what I'm asking: Say that a SaaS app has an Order table with CustomerID and a child OrderItem table. We want to partition both tables and we want to use CustomerID as the ...
1
vote
2answers
755 views

Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema

I have this table definition: CREATE TABLE `va_ventas` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `Date` DATE NOT NULL, `Time` TIME NULL DEFAULT NULL, `Country` VARCHAR(3) NULL DEFAULT NULL, `...
0
votes
1answer
89 views

How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE

Consider a table with the following columns: phone BIGINT NOT NULL PRIMARY KEY, name VARCHAR(64), state VARCHAR(64) We would like to partition the table on state, however that would require ...
1
vote
3answers
7k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
1
vote
2answers
149 views

Oracle tables partition related

Here is my doubt. Is it possible to apply a partition to one table depending in the partitions values of another one? Why I am asking? I have one table where information headers are persisted, and ...
1
vote
1answer
104 views

Partitioning a table state

So far I always used date ranges to partition my tables, however now I like to do it by state or a selected list of cities where all others that are not explicitly listed in an "other" partition, is ...
-1
votes
2answers
237 views

What are the possible risks involved in partitioning large database tables? [closed]

There has been much talk about the benefits of table partitioning in Relational DBMS. I would like to know: Are there any risks to table partitioning ? What measures are there to take to minimize ...
3
votes
1answer
80 views

What inefficiency does sharding & partitioning attempt to address? [closed]

I have never implemented sharding or partitioning, but from what I understand, multiple servers are used to distribute large amounts of data. Is this because of a limited hardware budget or to ...
1
vote
1answer
22 views

Logically Horizontale-Partitioned Tables

I have two legacy tables, lets call them TableA and TableB. Both of them fall under the same schema design with identical columns, in other words you can union them directly as one table. The only ...
3
votes
2answers
963 views

Return ID from partitioned table in postgres?

When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm ...
0
votes
1answer
296 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
2
votes
1answer
251 views

Availability Groups and Partition Swapping

Is it possible to use SQL 2012 Availability Groups over two machines and perform partition swapping on the primary machine ?
0
votes
1answer
307 views

Does Oracle Partition by Reference optimizes join queries

Oracle 11g has a new feature in partitioning tables called partition by reference. I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining ...
0
votes
1answer
98 views

Proper drive partitioning in Linux for Oracle

I have a current production physical server running on RHEL and I'm going to be building a new Oracle Linux VM to take it's place. Below is a listing of the existing physical drive partitions. Can ...
4
votes
2answers
362 views

Strategy to deal with multi billion row simulation results and partitioning

I am trying to come up with a good way to deal withthe results of statistical simulations from a dba point of view. We generate about 500 million rows per day, most of which are "garbage" (i.e. the ...
11
votes
3answers
50k views

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?
1
vote
1answer
1k views

Bulk loading to partitioned table

I am designing a database application which will be using PostgreSQL 9.3. My question is regarding bulk loading a CSV to a partitioned table. I have done a lot of research, but I'm still unsure of ...