0
votes
1answer
46 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
1
vote
1answer
254 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 ...
0
votes
0answers
59 views

MySQL: Optimizing for large but discrete data sets

In brief: I'm developing a database that handles GTFS datasets from multiple transit agencies. Each dataset contains millions of rows in the stop_times.txt file (and thus its corresponding table). ...
0
votes
0answers
35 views

How to move partitions between filesystems

We have ~9TB MySQL database which consists from a few tables which are partitioned by day. Sum of daily information is ~20GB. Database is practically write only. There are at most tens of queries ...
1
vote
1answer
65 views

Mysql, sql server, oracle tables get too large [closed]

I hear a lot about things that, it is a lot cheaper to buy new harddisk than pay staff to come up with genius ideas how to save resources. But what I don't understand, is how these databases handle a ...
1
vote
3answers
950 views

How to partition a table by timestamp, where data from every i'th day goes to partition i?

I'm looking into partitioning a table in my InnoDB database. I have a column corresponding to a UTC timestamp, and I want to partition around that. The basic idea that I want to capture is a ...
0
votes
0answers
261 views

Should I partition this large MySQL InnoDB table by date and if so, how?

Table Detail: I have a fairly large (4.6 million rows, 74 columns (average data length is 239)) InnoDB table which stores data for about 1100 sensors polled every 10 minutes (about 150k rows inserted ...
1
vote
0answers
20 views

How do I configure MySQL to store specific entire InnoDB tables on a different drive in Windows ( Server )?

After skimming the MySQL ( 5.5 ) docs on Partitioning, that seems to deal with breaking parts of the same table off onto other drives. I'm struggling to find information on how I pinpoint an ...
3
votes
1answer
122 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
2
votes
1answer
147 views

InnoDB, Hash Table, Reference Table and Fragmentation

I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format: CREATE TABLE IF NOT EXISTS `domains_list` ...
2
votes
1answer
184 views

Does partitioning a MySQL table also increase index creation performance?

This is something I was wondering since I'm planning to partition a aggregation table with 10 mil records where rebuilding indexes cause table locks for some time. Because partitions are bluntly put ...
0
votes
0answers
398 views

What can I do to repair a corrupted partitioned table on the slave?

mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1 The slave has been stopped due to power outage: Master_Log_File: mysql-bin.000491 ...
0
votes
0answers
28 views

MySQL Partitioning without specifiying any properties for partition

I have a quite huge MySQL table. In my table I have date field. Recently I created another field called YearMonth which get year from date, multiple it to 100 and adds the month from date table to it. ...
1
vote
3answers
883 views

How do I remove partitioning?

There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code: alter table my_table remove ...
0
votes
1answer
87 views

Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column): ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
0
votes
0answers
58 views

Find the partition where data lives in MySQL

Is there a way to select the partition name where a specified record actually lives in? The idea is something like: SELECT <PARTITION_NAME> FROM mytable WHERE <where_condition> I found ...
1
vote
0answers
75 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
4
votes
2answers
831 views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
2
votes
1answer
410 views

Why is MySQL order by performance poor within a single partition of a partitioned table?

I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
0
votes
0answers
24 views

MySQL poor order by performance on partitioned table [duplicate]

I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
0
votes
1answer
333 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
0
votes
1answer
239 views

MySQL Read Speed and Partitioning on Separate Drives

Introduction Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
1
vote
1answer
95 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
1answer
77 views

Add partition works but not the drop partition

We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not working well any ...
1
vote
0answers
72 views

MySQL Partitions

I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance. We've got an InnoDB table which is currently storing ~3.5 million rows, which I know ...
1
vote
3answers
4k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
2
votes
1answer
153 views

MySQL partitioned tables?

I have a database that supports a web application with several large tables. I'm wondering if partitioned tables will help speed up certain queries. Each of these tables has a colum called ...
0
votes
2answers
145 views

MySQL Cluster - partitions on disk

I have question about MySQL Cluster: Is it possible to have database larger than amount of RAM? (to not have all cluster data in-memory, but some data will reside on clusters nodes hard disks) ...
1
vote
2answers
734 views

Best option to back up mysql innodb partition

We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link ...
0
votes
1answer
177 views

Where is my old partition in mysql show create table?

I am keep adding daily base partition for a table as e.g. ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) ( PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')), PARTITION ...
0
votes
2answers
172 views

Best Way to Partition By City / State in MySQL 5.5

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so: ALTER TABLE ...
1
vote
0answers
177 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
1
vote
0answers
109 views

MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
1
vote
2answers
888 views

Best way to organize compound indexes on partitioned table (date by range)?

I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...
0
votes
0answers
50 views

How to check on my partitions?

I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had ...
2
votes
1answer
178 views

Where are partitions defined in mysql

I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data. How do I go from new disks (lets call them ...
1
vote
1answer
154 views

Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
3
votes
3answers
405 views

High insert rate on MySQL

I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event ...
0
votes
1answer
951 views

Partitioning not working need primary key?

We are trying some partitioning as below. ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate)) ( PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')), PARTITION p2 VALUES LESS THAN ...
1
vote
0answers
52 views

Partition guides and possible solutions?

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
4
votes
1answer
195 views

MySQL table design of logging tables

I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization. Currently we have a table that logs session data, it writes about ...
2
votes
1answer
255 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
4
votes
2answers
626 views

Partitioning a table vs moving entries to another table

I don't understand how partitioning helps me more or less than using a separate table to store older records. I need to maintain a list of stock entries along with historical records about the stock. ...
4
votes
1answer
1k views

MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
1
vote
0answers
59 views

MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
1
vote
2answers
477 views

SQL Partioning on date, how does it work for future records?

I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have ...
0
votes
1answer
921 views

Unable to select the records from specific partition in mysql?

I've created a table using the following query, CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id ...
1
vote
2answers
94 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...
0
votes
1answer
692 views

MySQL, “columns partitioning” when multiple columns are individiually important

MySQL 5.5 introduces "columns partitioning". http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html I'm trying to better understand how it works when two columns are important individually. ...
0
votes
1answer
99 views

Multitable partition guide

We have a main table called tblLink in it will have a date field.The tblLink will have its primary key as foreign in few other tables too and one of the table will tblMainData which over time will be ...