MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Ronald Brown
Date: July 30, 2015 07:22AM

Rick James Wrote:
-------------------------------------------------------
> Memory allocation:
> http://mysql.rjweb.org/doc.php/memory
>
> For MyISAM, _Most_ of the RAM should be left for
> caching the _data_ of MyISAM tables. So,
> 29GB/32GB free is about right.
> key_buffer_size is about half what it should be.
>
> > KEY `e_v` (`enabled`,`verified`),
> > KEY `e` (`enabled`),
>
> The latter index is redundant with the former, and
> can be DROPped.
>
> > `hash` char(40) COLLATE utf8_unicode_ci NOT NULL
> DEFAULT '',
>
> Is the "hash" a hex string? UUID? You are
> currently allocating 120 bytes (per row) for
> `hash`.
> For an MD5, CHAR(32) CHARACTER SET ascii -- 32
> bytes
> For a UUID, CHAR(36) CHARACTER SET ascii -- 36
> bytes
>
> > ROW_FORMAT=FIXED
>
> Don't use that; it's an old wives tale that there
> is any benefit from it.
>
> > WHERE enabled = 1 ORDER BY upload_date DESC
>
> would benefit from INDEX(enabled, upload_date)
>
> > LIMIT 6514850,25
>
> Get real! Under what condition do you need to
> jump to row number 6514850?? The query _must_
> scan 6514850 rows before finding the 25 you want.
>
> > log_queries_not_using_indexes = 1
>
> That clutters the slowlog, without providing
> useful info.
>
> > will switching to innodb improve overall read
> performance ?
>
> Not for _this_ query. But InnoDB is better in
> general.


Thanks for your reply.

dropped KEY `e` (`enabled`),

"hash" is hex string with 40 length

write queries are increased significantly and while writing the read queries took around 1.xx seconds , so i have converted contents table to InnoDB.


removed log_queries_not_using_indexes = 1 from my.cnf

new my.cnf

[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

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 1

innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

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-queries1.log
long_query_time = 1

#log_queries_not_using_indexes = 1

max_allowed_packet=16M

key_buffer_size=100M

query_cache_type = 1
query_cache_limit = 2M
query_cache_min_res_unit = 2k
query_cache_size = 80M


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 | InnoDB | 10 | Compact | 6023497 | 1366 | 8232370176 | 0 | 1935327232 | 7340032 | 6695491 | 2015-07-30 09:37:40 | NULL | NULL | 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 | 2122663 | 2891 | 6136973672 | 281474976710655 | 21824512 | 0 | NULL | 2015-07-03 07:26:25 | 2015-07-30 12:56:50 | 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 | InnoDB | 10 | Compact | 43365909 | 53 | 2339373056 | 0 | 1573912576 | 4194304 | 49433801 | 2015-07-30 07:38:02 | NULL | NULL | 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 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2015-07-30 07:07:46 | NULL | 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 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2015-07-30 07:07:32 | NULL | NULL | utf8_unicode_ci | NULL | | |
| trackers | InnoDB | 10 | Compact | 97419 | 102 | 9977856 | 0 | 14270464 | 4194304 | 103802 | 2015-07-30 07:07:14 | NULL | 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.00 sec)


mysql> SHOW CREATE TABLE content;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | 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_u` (`enabled`,`uploader`)
) ENGINE=InnoDB AUTO_INCREMENT=6695491 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 | 25769803776 |
| 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 | 104857600 |
| 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 |
+-------------------------------------+----------------+
22 rows in set (0.00 sec)


Issue 1):

I need to update all rows (6mil+) once a day , and while update is running the simple queries time goes from 0.00 to 1.xx seconds ,
how can i fix it ?

it was same with the myisam engine., how can i find where is the issue ?
i thought changing to innodb will improve read performance whie writing as innodb offers row level locking.


Thnkas

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1293
July 30, 2015 07:22AM


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.