large InnoDB table partitioning without explicit PK
Posted by:
Miko M
Date: November 12, 2014 07:41PM
Welcome All The Forum Users,
As a new to this forum let me apologize for my misconduct by asking for help from the very beginning. ;-)
I've just moved from Oracle and MS SQL to MySQL (still being really impressed what this small but great piece of software can do), and need a piece of advice.
I am just wondering whether someone could share some experience with range partitioning of a large InnoDB table without a primary key?
Any recommendations in terms of the engine (other than InnoDB)?
Many thanks!
Miko
### some details
One partitioned table: 96 HOURLY partitions, partition by range (timestamp)
Average record size 209B
Data populated from JDBC
(Current) number of records inserted per hour: 2M (peak 5M) -> 200 million of records in the table (number of partitions: 96)
Estimated number of records inserted per hour: 20M (peak 50M) -> 800 million of records in the table (number of partitions: 36)
HW: 2 x 6 cores Xeon, 64GB RAM, decent SAN, OS: pretty old RedHat, MySQL version: 5.2.10-MariaDB, all on VMWare ESXi 5
Initial PRIMARY KEY (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`) has been removed and replaced by two indexes (idx0 and idx1)
Impletented HOURLY partitioning (fully automated process) along with additional timestamp column (`col20`) as the partitioning key (RANGE partitioning).
Played with MyISAM - (performance) results seemed to be better than InnoDB but table level locking and possible long recover after crash discorged me from using it.
Binlog was disabled to increase i/o.
### changes planned in the (near) future:
Change version to mariadb10.0.x to support better partition pruning (it is almost not supported now) on RedHat 6.5
Add an additional index key
Enable replication (and bin log)
Use temp tables / heap storage engine to copy in data from a single partition of Table1 and play with inserted records (do aggregations and store results into other tables)
I'm aware of costly JDBC inserts (no batch inserts, autocommit, etc.) and "ON DUPLICATE KEY UPDATE"
There are neither records deletion in that table no other updates just partition drops.
### table
MariaDB [dbs1]> show create table Table1\G
*************************** 1. row ***************************
Table: Table1
Create Table: CREATE TABLE `Table1` (
`col1` varchar(11) NOT NULL DEFAULT '',
`col2` varchar(24) NOT NULL DEFAULT '',
`col3` varchar(30) NOT NULL DEFAULT '',
`col4` varchar(30) NOT NULL DEFAULT '',
`col5` varchar(60) NOT NULL DEFAULT 'NULL',
`col6` smallint(5) unsigned NOT NULL DEFAULT '0',
`col7` mediumint(8) unsigned DEFAULT '0',
`col8` int(10) unsigned DEFAULT '0',
`col9` varchar(15) DEFAULT NULL,
`col10` smallint(5) unsigned DEFAULT '0',
`col11` tinyint(3) unsigned DEFAULT '0',
`col12` varchar(20) DEFAULT NULL,
`col13` varchar(20) DEFAULT NULL,
`col14` smallint(5) unsigned DEFAULT '0',
`col15` tinyint(3) unsigned DEFAULT '0',
`col16` varchar(10) DEFAULT NULL,
`col17` varchar(30) DEFAULT NULL,
`col18` tinyint(3) unsigned DEFAULT '0',
`col19` smallint(5) unsigned DEFAULT '0',
`col20` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `idx0` (`col20`),
KEY `idx1` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(`col20`))
(PARTITION P1970010101 VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION P2014110718 VALUES LESS THAN (1415343600) ENGINE = InnoDB,
..................
PARTITION P2014111306 VALUES LESS THAN (1415818800) ENGINE = InnoDB) */
1 row in set (0.01 sec)
------------------------------------
### my.cnf
[mysqld]
datadir=/database/mysql
socket=/database/mysql/mysql.sock
user=mysql
bind-address=0.0.0.0
innodb_file_per_table
event_scheduler=on
default_time_zone='+13:00'
log-slow-queries=/var/lib/mysql/mysql-slowquery.log
innodb_log_file_size=1024M
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=10000
innodb_adaptive_flushing=ON
innodb_stats_on_metadata=0
join_buffer_size=1M
query_cache_size=8M
default_table_type=INNODB
lower_case_table_names=1
max_connections=100
max_allowed_packet=16M
innodb_buffer_pool_size=32768M
innodb_log_buffer_size=16M
read_rnd_buffer_size=3M
key_buffer_size=256K
table_cache=1024
thread_cache=16
innodb_flush_log_at_trx_commit=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
max_allowed_packet=16M
------------------------------------
### JDBC inserts (binlog):
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
insert into Table1(col1,col2,..., col19) values (val1, val2,..., val19) ON DUPLICATE KEY UPDATE col1 = col1
COMMIT/*!*/;
-----------------------------------
### innodb engine
MariaDB [dbs1]> show processlist;
+--------+-----------------+----------------+------+---------+------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------------+----------------+------+---------+------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 24 | Waiting for next activation | NULL |
| 440994 | report | MOONLB01:57964 | dbs1 | Sleep | 14 | | NULL |
| 441254 | report | MOONLB01:59457 | dbs1 | Sleep | 4 | | NULL |
| 441372 | report | MOONLB01:60176 | dbs1 | Sleep | 4 | | NULL |
| 441384 | report | MOONLB01:60263 | dbs1 | Sleep | 4 | | NULL |
| 441398 | report | localhost | dbs1 | Query | 0 | NULL | show processlist |
| 441402 | report | MOONLB01:60323 | dbs1 | Sleep | 14 | | NULL |
+--------+-----------------+----------------+------+---------+------+-----------------------------+------------------+
7 rows in set (0.00 sec)
MariaDB [dbs1]>
MariaDB [dbs1]> show engine innodb status;
| InnoDB | |
=====================================
141113 13:46:57 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1801497 1_second, 1801496 sleeps, 163596 10_second, 168376 background, 168376 flush
srv_master_thread log flush and writes: 1816569
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 46221923, signal count 50373467
Mutex spin waits 816549526, rounds 873233709, OS waits 1222535
RW-shared spins 48178388, OS waits 25438899; RW-excl spins 325087452, OS waits 15631264
Spin rounds per wait: 1.07 mutex, 22.88 RW-shared, 8.64 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (read thread)
I/O thread 11 state: waiting for i/o request (read thread)
I/O thread 12 state: waiting for i/o request (read thread)
I/O thread 13 state: waiting for i/o request (read thread)
I/O thread 14 state: waiting for i/o request (read thread)
I/O thread 15 state: waiting for i/o request (read thread)
I/O thread 16 state: waiting for i/o request (read thread)
I/O thread 17 state: waiting for i/o request (read thread)
I/O thread 18 state: waiting for i/o request (write thread)
I/O thread 19 state: waiting for i/o request (write thread)
I/O thread 20 state: waiting for i/o request (write thread)
I/O thread 21 state: waiting for i/o request (write thread)
I/O thread 22 state: waiting for i/o request (write thread)
I/O thread 23 state: waiting for i/o request (write thread)
I/O thread 24 state: waiting for i/o request (write thread)
I/O thread 25 state: waiting for i/o request (write thread)
I/O thread 26 state: waiting for i/o request (write thread)
I/O thread 27 state: waiting for i/o request (write thread)
I/O thread 28 state: waiting for i/o request (write thread)
I/O thread 29 state: waiting for i/o request (write thread)
I/O thread 30 state: waiting for i/o request (write thread)
I/O thread 31 state: waiting for i/o request (write thread)
I/O thread 32 state: waiting for i/o request (write thread)
I/O thread 33 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3514490 OS file reads, 4950455 OS file writes, 1683729 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.04 writes/s, 0.40 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 953, seg size 955,
184 inserts, 184 merged recs, 2 merges
Hash table size 70803091, node heap has 175013 buffer(s)
141.06 hash searches/s, 2022.53 non-hash searches/s
---
LOG
---
Log sequence number 1903783930922
Log flushed up to 1903781590963
Last checkpoint at 1903778377095
Max checkpoint age 1738750649
Checkpoint age target 1684414692
Modified age 5553827
Checkpoint age 5553827
0 pending log writes, 0 pending chkp writes
576912 log i/o's done, 0.16 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 35248930816; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 3433841936 (566424728 + 2867417208)
Page hash 35402344
Dictionary cache 144210006 (141607792 + 2602214)
File system 200176 (82672 + 117504)
Lock system 85003656 (84999896 + 3760)
Recovery system 0 (0 + 0)
Threads 407776 (406936 + 840)
Dictionary memory allocated 2602214
Buffer pool size 2097151
Buffer pool size, bytes 34359721984
Free buffers 0
Database pages 1922138
Old database pages 709519
Modified db pages 263
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7379379, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4811049, created 29564720, written 33170084
0.00 reads/s, 11.58 creates/s, 7.02 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1922138, unzip_LRU len: 0
I/O sum[316]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5661, id 1561672000, state: sleeping
Number of rows inserted 1829966289, updated 29650, deleted 0, read 1251724575289
713.47 inserts/s, 0.04 updates/s, 0.00 deletes/s, 0.09 reads/s
------------
TRANSACTIONS
------------
Trx id counter 11DA1E796
Purge done for trx's n:o < 11DA16A29 undo n:o < 0
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 11DA1E78F, not started, process no 5661, OS thread id 1099671872
mysql tables in use 134, locked 134
MySQL thread id 441415, query id 1836075949 MOONLB01 10.10.10.3 report Table lock
insert into Table1 (col1,col2,..., col19) values (val1, val2,..., val19) ON DUPLICATE KEY UPDATE col1 = col1
---TRANSACTION 11DA1E791, not started, process no 5661, OS thread id 1572751680
MySQL thread id 441414, query id 1836075942 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA1E793, not started, process no 5661, OS thread id 1579239744
MySQL thread id 441413, query id 1836075943 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA1E790, not started, process no 5661, OS thread id 1574226240
MySQL thread id 441412, query id 1836075940 MOONLB01 10.10.10.3 report
---TRANSACTION 0, not started, process no 5661, OS thread id 1580124480
MySQL thread id 441398, query id 1836075947 localhost report
show engine innodb status
---TRANSACTION 11DA1E792, not started, process no 5661, OS thread id 1572456768
MySQL thread id 441402, query id 1836075944 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA1D8B3, not started, process no 5661, OS thread id 1576880448
MySQL thread id 441384, query id 1836072136 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA1E794, not started, process no 5661, OS thread id 1577175360
MySQL thread id 441372, query id 1836075946 MOONLB01 10.10.10.3 report closing tables
insert into Table1 (col1,col2,..., col19) values (val1, val2,..., val19) ON DUPLICATE KEY UPDATE col1 = col1
---TRANSACTION 11DA1E78E, not started, process no 5661, OS thread id 1573046592
mysql tables in use 134, locked 134
MySQL thread id 441254, query id 1836075948 MOONLB01 10.10.10.3 report Table lock
insert into Table1 (col1,col2,..., col19) values (val1, val2,..., val19) ON DUPLICATE KEY UPDATE col1 = col1
---TRANSACTION 11DA1E795, ACTIVE 0 sec, process no 5661, OS thread id 1580419392 inserting
mysql tables in use 134, locked 134
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 440994, query id 1836075945 MOONLB01 10.10.10.3 report update
insert into Table1 (col1,col2,..., col19) values (val1, val2,..., val19) ON DUPLICATE KEY UPDATE col1 = col1
TABLE LOCK table `dbs1`.`Table1` /* Partition `P2014111314` */ trx id 11DA1E795 lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.01 sec)
=====================================
141113 13:47:29 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1801510 1_second, 1801509 sleeps, 163597 10_second, 168377 background, 168377 flush
srv_master_thread log flush and writes: 1816582
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 46222587, signal count 50374016
Mutex spin waits 816560107, rounds 873245312, OS waits 1222550
RW-shared spins 48179071, OS waits 25439317; RW-excl spins 325091985, OS waits 15631446
Spin rounds per wait: 1.07 mutex, 22.88 RW-shared, 8.64 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (read thread)
I/O thread 11 state: waiting for i/o request (read thread)
I/O thread 12 state: waiting for i/o request (read thread)
I/O thread 13 state: waiting for i/o request (read thread)
I/O thread 14 state: waiting for i/o request (read thread)
I/O thread 15 state: waiting for i/o request (read thread)
I/O thread 16 state: waiting for i/o request (read thread)
I/O thread 17 state: waiting for i/o request (read thread)
I/O thread 18 state: waiting for i/o request (write thread)
I/O thread 19 state: waiting for i/o request (write thread)
I/O thread 20 state: waiting for i/o request (write thread)
I/O thread 21 state: waiting for i/o request (write thread)
I/O thread 22 state: waiting for i/o request (write thread)
I/O thread 23 state: waiting for i/o request (write thread)
I/O thread 24 state: waiting for i/o request (write thread)
I/O thread 25 state: waiting for i/o request (write thread)
I/O thread 26 state: waiting for i/o request (write thread)
I/O thread 27 state: waiting for i/o request (write thread)
I/O thread 28 state: waiting for i/o request (write thread)
I/O thread 29 state: waiting for i/o request (write thread)
I/O thread 30 state: waiting for i/o request (write thread)
I/O thread 31 state: waiting for i/o request (write thread)
I/O thread 32 state: waiting for i/o request (write thread)
I/O thread 33 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3514490 OS file reads, 4950499 OS file writes, 1683744 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.37 writes/s, 0.47 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 953, seg size 955,
184 inserts, 184 merged recs, 2 merges
Hash table size 70803091, node heap has 174986 buffer(s)
227.96 hash searches/s, 1682.45 non-hash searches/s
---
LOG
---
Log sequence number 1903791589175
Log flushed up to 1903791589175
Last checkpoint at 1903784909893
Max checkpoint age 1738750649
Checkpoint age target 1684414692
Modified age 6679282
Checkpoint age 6679282
0 pending log writes, 0 pending chkp writes
576918 log i/o's done, 0.19 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 35248930816; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 3433399568 (566424728 + 2866974840)
Page hash 35402344
Dictionary cache 144210006 (141607792 + 2602214)
File system 200176 (82672 + 117504)
Lock system 85002152 (84999896 + 2256)
Recovery system 0 (0 + 0)
Threads 407776 (406936 + 840)
Dictionary memory allocated 2602214
Buffer pool size 2097151
Buffer pool size, bytes 34359721984
Free buffers 0
Database pages 1922165
Old database pages 709529
Modified db pages 309
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7379379, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4811049, created 29565040, written 33170385
0.00 reads/s, 10.00 creates/s, 9.41 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1922165, unzip_LRU len: 0
I/O sum[301]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5661, id 1561672000, state: sleeping
Number of rows inserted 1829986451, updated 29650, deleted 0, read 1251724575289
630.04 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 11DA23657
Purge done for trx's n:o < 11DA16A29 undo n:o < 0
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 5661, OS thread id 1580124480
MySQL thread id 441398, query id 1836096159 localhost report
show engine innodb status
---TRANSACTION 11DA2340C, not started, process no 5661, OS thread id 1572456768
MySQL thread id 441402, query id 1836095568 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA22857, not started, process no 5661, OS thread id 1576880448
MySQL thread id 441384, query id 1836092572 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA23437, not started, process no 5661, OS thread id 1577175360
MySQL thread id 441372, query id 1836095613 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA2342C, not started, process no 5661, OS thread id 1573046592
MySQL thread id 441254, query id 1836095603 MOONLB01 10.10.10.3 report
---TRANSACTION 11DA2349D, not started, process no 5661, OS thread id 1580419392
MySQL thread id 440994, query id 1836095714 MOONLB01 10.10.10.3 report
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
MariaDB [dbs1]>