MySQL Forums
Forum List  »  Performance

Utilize maximum system resources for better performance
Posted by: Ronald Brown
Date: July 14, 2015 01:54AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Utilize maximum system resources for better performance
2699
July 14, 2015 01:54AM


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.