database - MySQL does not utilize server fully -

i have mariadb 10.0.17 running on amazon rds db.m4.xlarge (16gb ram, 4vcpu) multi az deployment. use provisioned iops storage max set 10000 iops. users table contains 17m records; user_properties table contains 350m records.

user_properties table describes "map" of props attached user. upkey key, string_value, integer_value etc values per-type; string, date, integer, double. indexes per-type.

we try insert insert more data user_properties table: application inserts data innodb temp table temp1, data gets copied temp1 user_properties table.

problem reach 2500 write iops , 500-1000 read iops. queue depth holds on ~7 in average. mysql server cpu usage holds on 20-30% , never reaches 60%. application seems feed enough data mysql: feed similar data files db , see how processing time increases table size increases. time application waits mysql query completion. in process insertion temp1 table takes small fraction of overall time, time waiting insertion temp1 table user_properties.

could me making mysql faster? should increase/change?

create table if not exists `users` (   `id` bigint(20), // column not used now. filled null   `version` bigint(20) not null,   `email` varchar(255) collate utf8_unicode_ci default null,   `uuid` varchar(80) collate utf8_unicode_ci default null,   `partner_id` bigint(20) not null,   `password` varchar(255) collate utf8_unicode_ci default null,   `date_created` datetime default null,   `last_updated` datetime default null,   primary key (`id`),   unique key `unique-email` (`partner_id`,`email`),   unique key `users_uuid` (`uuid`),   key `idx_013_partner_id_uuid` (`partner_id`,`uuid`),   key `idx_014_uuid` (`uuid`),   constraint `fkb2d9febe725c505e` foreign key (`partner_id`) references `partner` (`id`),   constraint `fk_046_partner` foreign key (`partner_id`) references `partner` (`id`) ) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_unicode_ci;   create table if not exists `user_properties` (   `id` bigint(20) not null auto_increment,   `version` bigint(20) not null,   `date_created` datetime default null,   `last_updated` datetime default null,   `upkey` varchar(255) collate utf8_unicode_ci not null,   `user_id` bigint(20) default null,   `security_level` int(11) not null,   `_content` longtext collate utf8_unicode_ci not null,   `class` varchar(255) collate utf8_unicode_ci not null,   `date_value` datetime default null,   `integer_value` bigint(20) default null,   `double_value` double default null,   `string_value` varchar(255) collate utf8_unicode_ci default null,   `uuid` varchar(80) collate utf8_unicode_ci default null,   primary key (`id`),   unique key `idx_004_uuid` (`uuid`),   key `idx_005_string_value` (`upkey`,`string_value`),   key `idx_006_integer_value` (`upkey`,`integer_value`),   key `idx_007_double_value` (`upkey`,`double_value`),   key `idx_008_date_value` (`upkey`,`date_value`),   key `idx_key_value_user_upkey_string` (`user_id`,`upkey`,`string_value`),   constraint `fk_users` foreign key (`user_id`) references `users` (`id`) on delete cascade on update cascade ) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_unicode_ci; 

do need both id , uuid? think not.

do need 3 unique keys table? think not. (remember primary key unique.)

uuid has bad i/o properties when tables huge. reconsider use of them. index on uuid very random. when index (or table) becomes large fit in buffer_pool, fetches tend involve i/o rather being cached. 350m rows , 16gb of ram, suspect significant part of performance problem due uuids.

user_properties "key-value" store, correct? schema design pattern sucks. typical select? suspect this:

select ..._value user_properties     user_id = '...'       , upkey = '...'; 

assuming correct, performance can improved having

primary key(user_id, upkey, id) 

this "cluster" key-value pairs given user in single location (probably 1-2 blocks), thereby making fetching faster.

more on evils of key-value , suggestion improvement.
more on evils of uuids.