Tag Info

New answers tagged

0

If you choose not to use built-in partitioning you can roll your own by having a view create view capture_table as select * from data_table_2014-01-1 UNION ALL select * from data_table_2014-01-2 UNION ALL select * from data_table_2014-01-3 etc. Overnight maintenance creates a new data table, drops the old ones and re-generates the definition of the view. ...


0

Create a new table that is partitioned as you want and copy the data from the old table into it rather than trying to apply the new partitioning scheme to an existing table. Once the data is copied, remove the relations to the old table, drop it, and rename the new to take the place of the old and recreate all of the relations. Perform this within a ...


0

You do not have to include your partition key in your clustered index if the primary key itself is not partitioned. You can create an identity column to serve as the primary key and the clustered value, but partition the table by another value. That would be my preference because it will result in smaller indexes because the cluster key is smaller. ...


1

I had written an interesting alternative of implementing your own manual hash indexes for your table, and then I made maths and realised your constraints: Having into memory 3 bigints will cost you 500*10^6*(8*8*8)/(1024*1024*1024) = 11.17GB that you do not have. RDS is simply not adequate for you anymore, as it is not flexible enough to try some ...


2

There are a few good DMVs (system views) where you can find this info: sys.partition_functions and sys.partition_schemes can be joined to sys.partition_range_values to get all existing ranges. The number of ranges should be the number of partitions. The data_space_id of the partition scheme can be joined to that of the index or heap in sys.indexes on the ...


0

MySQL partitioning is handled as follows: MyISAM : Two file handles per partition (.MYD,.MYI) InnoDB : One file handle per partition (.ibd) Subpartitions simply create separate files, possibly multiplying the partitions What you are trying to achieve can only be accomplished with a simply change Create a column called part which forms a partition ...


1

I found out that due to this bug, MySQL 5.6.17 uses 6kb memory per open subpartition. This would result in about 500 GB of memory usage for 100 connections each having 100 tables with 800 partitions each, with 8 subpartitions each.


1

You could just use the SQL statement: SELECT sum(out_nb_comm) AS sum_out_nb_comm FROM count_comm($in_id_alias); Or, if you want to wrap it in a function, a simple SQL function does the job: CREATE OR REPLACE FUNCTION sum_count_comm(in_id_alias int) RETURNS int AS $func$ SELECT sum(out_nb_comm)::int FROM count_comm($1)); $func$ LANGUAGE sql;


0

instead of scanning one large index the database will scan multiple smaller tables in parallel. PostgreSQL does not currently support parallel query (though hopefully it isn't far away), so it will be digging through your partitions essentially one-at-a-time if you are querying based on something other than the exclusion constraint for your partitions. ...


1

The 2 will be equivalently impossible ;-). If you try that, you will get the following error: ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function Which is one of the biggest limitations of partitioning. Any unique key must contain the columns of the partitioning function. So either: Drop the primary key (not ...


2

So, I found a workaround... First I made a proxy function on which the first proxy function will run : CREATE OR REPLACE FUNCTION p_count_comm( in_id_alias INTEGER ) RETURNS INTEGER AS $func$ BEGIN RETURN (SELECT sum(tmp_nb_commentaire) out_nb_commentaire FROM count_comm(in_id_alias)); END; $func$ LANGUAGE plpgsql; And I modified my first ...


1

In my opinion, you should use an SSD if you are facing performance problems. Here is a 120 GB drive for approx. 64 Euro (< 100$ US). This will keep your application ticking over for a couple of years without any need for a redesign and performance should substantially improve for minimum expense. Down the road, if you are ultimately deciding to move to a ...


0

My opinion since you are going to have multiple VMs and you want to stick with Mysql use Fabric to shard your database. http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric-quick-start-sharding-scenario.html. Sharding is horizontal partitioning and you can split your data across multiple servers and gain faster writes and reads.


0

Below this section it goes on to update, in the exact same manner, every table in the view. I cannot see why this is, as I have specified the Table_Year (which the table is partitioned on) within the query text. Shouldn't SQL only update the necessary table? The view meets all the partitioning requirements for both Table_Year and calStartDate. The ...


1

[Note: also answered at answers.SQLPerformance.com.] These aren't actually partitioned tables, and even if they were, partition elimination wouldn't really work for updating indexes unless all indexes were also partition-aligned. Since you are using Express Edition and can't actually use partitioning, I have a different approach to recommend: dynamic ...



Top 50 recent answers are included