Utilize maximum system resources for better performance
Hello,
i have website hosted on dedicated sever where i would like mysql to utilize maximum resources it needs and give high performance .
server has 32 gb memory.
i have tried changing few things in my.cnf but server still show 29 gb free memory.
i am using myisma for my main table , as there are 90% read operations while 10% are write operations.
its biggest table also in the db, with 6.5 mil rows .
i have added many indxes but still always the first query takes longer time while queries thereafter are quicker.
here are more details
[mysqld]
join_buffer_size = 128M
sort_buffer_size = 8M
read_rnd_buffer_size = 2M
read_buffer_size = 8M
datadir=/home/data/mysql
socket=/home/data/mysql/mysql.sock
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
ft_min_word_len=1
ft_stopword_file = ""
default_time_zone='+00:00'
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
slow-query-log = on
slow-query-log-file = /var/log/mysql-slow-queries.log
long_query_time = 1
log_queries_not_using_indexes = 1
max_allowed_packet=16M
key_buffer_size=2300M
query_cache_type = 1
query_cache_limit = 2M
query_cache_min_res_unit = 2k
query_cache_size = 80M
#############################################################################
Cent OS 7 x64
Server version: 5.6.25-log MySQL Community Server (GPL)
RAM
# free -h
total used free shared buff/cache available
Mem: 31G 1.1G 5.1G 21M 25G 29G
Swap: 15G 0B 15G
mysql> show table status;
+----------------------------------+--------+---------+------------+----------+----------------+-------------+---------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------------------------------+--------+---------+------------+----------+----------------+-------------+---------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| comments | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 3 | 2015-07-11 17:10:49 | NULL | NULL | utf8_unicode_ci | NULL | row_format=FIXED | |
| comments_ratings_log | MyISAM | 10 | Fixed | 0 | 0 | 0 | 3659174697238527 | 1024 | 0 | 1 | 2015-06-29 17:23:44 | 2015-06-29 17:23:44 | NULL | utf8_unicode_ci | NULL | | |
| comments_ratings_tmp | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2015-06-29 14:37:12 | NULL | NULL | utf8_unicode_ci | NULL | | |
| content | MyISAM | 10 | Fixed | 6560305 | 4022 | 26385546710 | 1132092356330258431 | 1345850368 | 0 | 6560406 | 2015-07-13 13:24:18 | 2015-07-13 13:29:09 | 2015-07-13 13:39:18 | utf8_unicode_ci | NULL | row_format=FIXED | |
| content_description | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2015-06-21 02:54:54 | 2015-07-09 08:25:07 | 2015-07-09 08:25:07 | utf8_unicode_ci | NULL | | |
| content_files | MyISAM | 10 | Dynamic | 2094471 | 2883 | 6039450360 | 281474976710655 | 21533696 | 0 | NULL | 2015-07-03 07:26:25 | 2015-07-08 14:24:39 | NULL | utf8_unicode_ci | NULL | row_format=FIXED | |
| content_peers_tmp | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2015-06-29 14:37:12 | NULL | NULL | utf8_unicode_ci | NULL | | |
| content_trackers | MyISAM | 10 | Fixed | 48402126 | 31 | 1500465906 | 8725724278030335 | 990002176 | 0 | 48402127 | 2015-07-11 16:45:03 | 2015-07-11 16:46:19 | 2015-07-11 16:46:40 | utf8_unicode_ci | NULL | | |
| content_votes_tmp | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2015-06-29 14:37:12 | NULL | NULL | utf8_unicode_ci | NULL | | |
| favorites | MyISAM | 10 | Fixed | 0 | 0 | 0 | 3659174697238527 | 1024 | 0 | 1 | 2015-07-03 07:49:00 | 2015-07-03 07:49:00 | NULL | utf8_unicode_ci | NULL | | |
| keywords | MyISAM | 10 | Fixed | 0 | 0 | 0 | 55732045388709887 | 1024 | 0 | NULL | 2015-06-21 02:54:55 | 2015-06-21 02:54:55 | NULL | utf8_unicode_ci | NULL | row_format=FIXED | |
| new_content_category_suggestions | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2015-06-28 13:50:00 | 2015-06-28 13:50:00 | NULL | utf8_unicode_ci | NULL | row_format=DYNAMIC | |
| push_to_cache | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2015-06-28 13:50:55 | 2015-06-28 13:50:55 | NULL | utf8_unicode_ci | NULL | | |
| reported_comments | MyISAM | 10 | Dynamic | 1 | 20 | 20 | 281474976710655 | 3072 | 0 | 2 | 2015-06-28 13:51:34 | 2015-07-11 17:18:47 | NULL | utf8_unicode_ci | NULL | row_format=DYNAMIC | |
| reported_content | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2015-06-28 13:52:02 | 2015-06-28 13:52:02 | NULL | utf8_unicode_ci | NULL | row_format=DYNAMIC | |
| rss | MyISAM | 10 | Fixed | 0 | 0 | 0 | 3659174697238527 | 1024 | 0 | 1 | 2015-06-28 13:53:02 | 2015-06-28 13:53:02 | 2015-06-28 13:53:02 | utf8_unicode_ci | NULL | | |
| trackers | MyISAM | 10 | Dynamic | 94316 | 79 | 7454704 | 281474976710655 | 8391680 | 0 | 94317 | 2015-06-22 12:47:05 | 2015-07-08 14:24:35 | NULL | utf8_unicode_ci | NULL | | |
| users | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 0 | 1001 | 2015-07-13 13:06:25 | NULL | NULL | utf8_unicode_ci | NULL | | |
+----------------------------------+--------+---------+------------+----------+----------------+-------------+---------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
18 rows in set (0.01 sec)
mysql> SHOW CREATE TABLE content;
CREATE TABLE `content` (
`hash` char(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`title` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`og_name` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`keywords` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`files_count` smallint(5) unsigned NOT NULL DEFAULT '0',
`more_files` smallint(5) unsigned NOT NULL DEFAULT '0',
`files` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`category` smallint(3) unsigned NOT NULL DEFAULT '600',
`size` bigint(19) unsigned NOT NULL DEFAULT '0',
`leechers` int(11) NOT NULL DEFAULT '0',
`completed` int(11) NOT NULL DEFAULT '0',
`seeders` int(11) NOT NULL DEFAULT '0',
`creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`upload_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`vote_up` int(11) unsigned NOT NULL DEFAULT '0',
`vote_down` int(11) unsigned NOT NULL DEFAULT '0',
`comments_count` int(11) NOT NULL DEFAULT '0',
`imdb` int(8) unsigned NOT NULL DEFAULT '0',
`video_sample` tinyint(1) NOT NULL DEFAULT '0',
`video_quality` tinyint(2) NOT NULL DEFAULT '0',
`audio_lang` varchar(127) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`subtitle_lang` varchar(127) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
`uploader` int(11) unsigned NOT NULL DEFAULT '0',
`anonymous` tinyint(1) NOT NULL DEFAULT '0',
`enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
`tfile_size` int(11) unsigned NOT NULL DEFAULT '0',
`scrape_source` tinyint(1) unsigned NOT NULL DEFAULT '0',
`record_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`record_num`),
UNIQUE KEY `hash` (`hash`),
KEY `upload_date` (`upload_date`),
KEY `seeders` (`seeders`),
KEY `leechers` (`leechers`),
KEY `vote_up` (`vote_up`),
KEY `comments_count` (`comments_count`),
KEY `tfile_size` (`tfile_size`),
KEY `e_c_d_v` (`enabled`,`category`,`upload_date`,`verified`),
KEY `e_d_v` (`enabled`,`upload_date`,`verified`),
KEY `e_c_v` (`enabled`,`category`,`verified`),
KEY `e_v` (`enabled`,`verified`),
KEY `e` (`enabled`),
KEY `e_u` (`enabled`,`uploader`)
) ENGINE=MyISAM AUTO_INCREMENT=6560406 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED |
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 1073741824 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 134217728 |
| key_buffer_size | 2411724800 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 8388608 |
| read_rnd_buffer_size | 2097152 |
| sort_buffer_size | 8388608 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
Thanks