Tagged Questions
1
vote
3answers
35 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
22 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
30 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
40 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
109 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
58 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
52 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
63 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
42 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
34 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 ...
0
votes
0answers
50 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
1k 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: ...
0
votes
0answers
27 views
MySQL partitioning by customer
We have a product that uses different MySQL schemas for different customers, and a single Java application that uses different persistence units for one for every customer. This makes it difficult to ...
1
vote
1answer
82 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
77 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)
...
0
votes
0answers
45 views
Partitioning for queue tables
I have a queue table where entries are added by a service with the WAITING status and a due date, which may be seconds to weeks from now. Those due entries are consumed and updated with the PENDING ...
0
votes
0answers
30 views
How Best to Parition Temporal Data for Access By Date
The latest edition of the High Performance MySQL book has this to say about paritioning a large table where all the hot data is clustered temporally:
Suppose you have a table with an ...
1
vote
2answers
247 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
112 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
92 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
75 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
73 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
355 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
43 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
99 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
104 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
235 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
0answers
41 views
Partitioning and Sub-partitioning in MySQL
I want to partition a table by month and sub-partition by day.
When new data is added for a new month, I don't want to manually update the database.
When I initially create my database, I have data ...
0
votes
1answer
304 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
43 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
132 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
150 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
1answer
324 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
552 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 ...
0
votes
0answers
52 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
301 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
418 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
83 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
421 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
87 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 ...
3
votes
1answer
184 views
Adding new key(s) to enable partitioning, INSERT performance?
I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 diferent devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
-1
votes
1answer
1k views
open files limit
I am getting "too many open files" error when I try to insert records in a partitioned table.
mysql> insert into test.ox_data_geo_span select * from vserv.ox_data_geo_span limit 100;
ERROR 1016 ...
0
votes
1answer
212 views
horizontal partitioning or multiple tables?
I'm developing an application and need to propose a database schema.
We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and ...
1
vote
1answer
256 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,
...
2
votes
1answer
124 views
Can not add list partitions
Following is the create table statement that is working as expected. But if I have a table without any partitions and if I try add partitions, it seems that I can not add more than 16 values in it.
...
5
votes
2answers
361 views
Large accounting table, best way to partitioning by two dates
I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call ...
1
vote
2answers
571 views
MySql - partition by range and unique key
i'm trying to create the following table:
CREATE TABLE `s_relations_with_partition` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`source_persona_id` int(11) NOT NULL,
`relation_type` int(11) NOT ...
0
votes
1answer
163 views
MySql - Global index
I'm new to MySql partitioning and have a question regarding indexes.
let say i have the following table:
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
...
2
votes
2answers
606 views
Separate tables or partition one huge table?
I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
