partitioning

MySQL Partitioning / Sharding / Splitting - which way to go?

We have an InnoDB database that is about 70 GB and we expect it to grow to several hundred GB in the next 2 to 3 years. About 60 % of the data belong to a single table. Currently the database is working quite well as we have a server with 64 GB of RAM, so almost the whole database fits into memory, but we’re concerned about the future wh...

VmWare Primary HDD Expansion

I have a VmWare virtual machine that is coming dangerously close to it's primarry HDD's limit and I need to extend it. How do I do this? I'm working with VmWare Workstation 6.0.5 ...

SQL 2005 DB Partitioning for SharePoint

Background I have a massive db for a SharePoint site collection. It is 130GB and growing at 10gb per month. 100GB of the 130GB is in one site collection. 30GB is the version table. There is only one site collection - this is by design. Question Am I able to partition a database (SharePoint) using SQL 2005s data partitioning feature...

Table load via Partition Exchange (Oracle 10g)

I have a few questions about optimizing this type of load. One builds a new table of data to be loaded into a partitioned table and then builds the indexes on this new table. Should you build the index with the COMPUTE STATISTICS option or use the Cascade option of the DBMS_Stats? Should you gather stats on the table before the swap ...

What is the best way to partition large tables in SQL Server?

In a recent project the "lead" developer designed a database schema where "larger" tables would be split across two separate databases with a view on the main database which would union the two separate database-tables together. The main database is what the application was driven off of so these tables looked and felt like ordinary tab...

Primary partition full on debian

My primary partition has become full on Debian and I don't appear to be able to resize it while the system is running... How can I make space on this partition? Filesystem Size Used Avail Use% Mounted on /dev/sda1 259M 259M 0 100% / tmpfs 443M 0 443M 0% /lib/init/rw udev ...

Move rows between tables in SQL

I have 2 tables, an active table and an inactive table. I want to move rows from the active to the inactive table. My first thought was insert into inactive select * from active where ... delete from active active where ... However about .42 seconds later I noticed this will drop/duplicate rows if updates alter what the where clause s...

how to restrict or filter database access according to application user attributes

I've thought about this too much now with no obviously correct solution. It might be a real wood-for-the-trees situation, so I need stackoverflow's help. I'm trying to enforce database filtering on a regional basis. My system has various users and each one is assigned to a regional office. I only want users to be able to see data tha...

disk based storage in mysql cluster

I have been trying to find out if it is possible to do table partitioning with disk based tables in MySQL Cluster. I want to store a large table on disk, and do range partitioning on the table. I have a feeling the answer is no. Thanks for any info you can offer. Mike. ...

Can I ALTER a partitioned table in SQL Server 2005?

I'm looking into horizontal partitioning for a table that has time-series data in it. I've discovered that partitioning is much easier in 2005 than it was in 2000 but I can't seem to find this answer: Can I add/drop columns of a partitioned table? Are special steps required because it's partitioned? ...

Oracle "Partition By" Keyword

Can someone please explain what the "partition by" keyword does and give a simple example of it in action, as well as why one would want to use it? I have a SQL query written by someone else and I'm trying to figure out what it does. An example of partition by: SELECT empno, deptno, COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT FROM...

Huge table

I read your article(SQL Server partitioning: not the answer to everything) and being amazing of use partitioning for my case or not I must to store about 1000 record per a second this data is about location of mobile nodes, these data make my database too huge do you think i must partitioning my database or not(I have so much reporting ...

partition function in SQL Server 2005

In MSDN about partition function from here, $PARTITION(Transact-SQL). I am confused about what the below sample is doing underlying. My understanding is, this SQL statement will iterate all rows in table Production.TransactionHistory, and since for all the rows which will mapping to the same partition, $PARTITION.TransactionRangePF1(Tra...

Website Partitioning and URL space.

My website is growing. I need to partition feature sets to different group of servers instead of having all features run on a single server copy. I have 8 identical servers in a Windows Network Load Balancing setup. The problem is: I need to keep the URL space the same. What would be a workable solution? I don't want to start new domain...

Does it makes sense to partition a dedicated server hard drive?

I am going to install Debian on our new dedicated server and I have a chance to set up hard drive partitions. The server has two 750GB drives configured as RAID 1. We are going to have 10 Postgresql databases, where each database will be about 2 GB. There will be of course Apache/PHP on this server too. We'll use the server for only ou...

Efficient MySQL schema with partitioning for huge dataset (7.300.000.000 rows and roughly 80 GB of data)

This is a follow up to my question "Efficiently storing 7.300.000.000 rows" (http://stackoverflow.com/questions/665614/efficiently-storing-7-300-000-000-rows). I've decided to use MySQL with partitioning and the preliminary schema looks like this: CREATE TABLE entity_values ( entity_id MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes...

Do indexes suck in SQL?

Say I have a table with a large number of rows and one of the columns which I want to index can have one of 20 values. If I were to put an index on the column would it be large? If so, why? If I were to partition the data into the data into 20 tables, one for each value of the column, the index size would be trivial but the indexing ef...

history rows management in database

hey guys, As in many databases, i am designing a database that should keep record of previous versions of the rows changed in each table. The standard solution to this problem is to keep a history table for each data table, and whenever a row needs to be updated in the data table, a copy of the current row gets inserted to the history ...

Can I use SQL Server Partitioning to Optimize Fulltext Searches?

I have a very large table with a fulltext indexed column. If I partition this table sensibly (for me, sensibly is by date), will it speed up queries? Or will the full text clause still search through the whole table, even if the query limits to a single partition? From what I've seen so far, I think the answer is partitioning won't help...

Cross-platform partition management library?

Hi, I'm looking for some sort of partition management library (preferably a Python one, but anything works) that will function on both Windows and Linux. (For working with USB devices, specifically, but any harddisk tool should do) We'd much rather not implement two different libraries as that makes it harder to maintain, but so far we...