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