MySQL Forums
Forum List  »  Partitioning

Fortnight partitions
Posted by: Srikanth Mamidi
Date: November 19, 2012 11:24PM

Hi,

In our application we need to store users info with attributes like name,description,location etc. along with friends count and user message count.
We want to maintain the history of the friends count and messages count for a given user per fortnight. We would need upto around 6 months data(about 12 fortnights) of friends count and message count. Data older than 6 months should be deleted/truncated. Would partitioning be a good solution for this and if so what kind of partitioning on what column(s) would be useful?

Table definition is:

CREATE TABLE `user_data` (
`userID` int(10) unsigned NOT NULL,
`userName` varchar(45) DEFAULT NULL,
`location` varchar(60) DEFAULT NULL,
`friends_cnt` int(10) unsigned NOT NULL,
`messages_cnt` int(10) unsigned NOT NULL,
`geo_code` int(10) unsigned DEFAULT NULL,
`lastupd` bigint(20) unsigned DEFAULT '0',
PRIMARY KEY (`userID`),
KEY `userName_idx` (`userName`) USING BTREE,
KEY `geo_idx` (`geo_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8


--------------------------------------------------
2. InnoDB specific variables:
--------------------------------------------------
have_innodb YES
ignore_builtin_innodb OFF
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_size 1073741824
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 0
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 50485760
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
innodb_use_legacy_cardinality_algorithm ON
--------------------------------------------------------

3. my.cnf content :
---------------------------------------------------------
[mysqld]
#symbolic-links=0
key_buffer_size=33554432
innodb_buffer_pool_size=1G
innodb_log_file_size=50485760
innodb_flush_log_at_trx_commit=0
tmp_table_size=100M
max_heap_table_size=70M
max_allowed_packet=32M
max_connections=400
#open_files_limit=8192
#table_cache=256
#innodb_file_per_table=ON

The SELECTS are straightforward where only the indexed columns are used in the where clause.

select * from user_data where user_name in(<uname1>,<uname2>...);
select * from user_data where userID =<userid>;

The table does not have any foreign key relationships and no joins are needed too for our SELECT queries.Above are the only indexes/keys. We have high freq updates/inserts into the above table around 2500 per minute.

We are using batch INSERTs of 1000 and we can't use LOAD DATA as we are using INSERT ... ON DUPLICATE KEY and LOAD DATA does not have similar ON DUPLICATE KEY functionality.

There is no replication involved and we do have couple of INSERT/UPDATE triggers.

Options: ReplyQuote


Subject
Views
Written By
Posted
Fortnight partitions
4152
November 19, 2012 11:24PM
2445
November 20, 2012 10:56PM
1654
November 20, 2012 11:23PM
1764
November 21, 2012 08:54PM
1705
November 23, 2012 12:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.