MySQL high cpu usage (200%)

[twm]

If you upload a lot of images at the same time,

MySQL's CPU usage is very high. At this time, the website will be locked

Sorry, My English is pretty basic.

My Server
i7-7700/32G/2x512G SSD/ RAID 1

CentOS 7/Nginx 1.15/MariaDB 10.3/php 7.2/


MySQL
my.conf

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

[mysqld]
binlog_cache_size = 256K
thread_stack = 512K
join_buffer_size = 8192K
query_cache_type = 1
max_heap_table_size = 2048M
port = 3306
socket = /tmp/mysql.sock
user = mysql
datadir = /www/server/data
basedir = /www/server/mysql
log_error = /www/server/data/mariadb.err
#pid-file = /www/server/data/mariadb.pid
default_storage_engine = MyISAM
#skip-external-locking
#loose-skip-innodb
key_buffer_size = 1024M
max_allowed_packet = 100G
table_open_cache = 2048
sort_buffer_size = 4096K
net_buffer_length = 8K
read_buffer_size = 4096K
read_rnd_buffer_size = 2048K
myisam_sort_buffer_size = 128M
thread_cache_size = 256
query_cache_size = 512M
tmp_table_size = 2048M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-networking
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on

default_storage_engine = InnoDB
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 8
innodb_write_io_threads = 8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 8M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

My Site

 

[Rodolfo]

First thing that you should mention is the load that your website has. Based on the processes I'm seeing normal php usage but mysql is different story.

I strongly suggest you to check your table indexes, maybe you need to rebuild these.

 

[twm]

First thing that you should mention is the load that your website has. Based on the processes I'm seeing normal php usage but mysql is different story.

I strongly suggest you to check your table indexes, maybe you need to rebuild these.

Thank you a lot,
How to rebuild table indexes?

 

[bilderupload]

How to Rebuild Indexes on MYSQL
Optimization and Tuning
MySQL database uses too much CPU < here is the key
Calculating InnoDB Buffer Pool Size for your MySQL Server


Due to high website loads sir you should maybe think about to host your mysql separately on another server optimized for it

 

[twm]

If you click on this user's album, MySQL's CPU usage will be 100%.

 

[bilderupload]

I would try to reproduce the error on the local machine with this Database, and than i would use MySQLTuner,
read this Article about how to do it sir, i hope i can help you, wish you alot luck.

 

[Rodolfo]

Show the full query causing the issue and the result of the EXPLAIN command for that query.

No one can help you at all only knowing that X user gives you issues. Your DB is unique to your website, we can't replicate the error by just knowing that.

 

[twm]

Show the full query causing the issue and the result of the EXPLAIN command for that query.

No one can help you at all only knowing that X user gives you issues. Your DB is unique to your website, we can't replicate the error by just knowing that.

Sorry, I won't use the EXPLAIN command,
I am not sure what information is needed, I can provide the test server root password. Could you check it for me?

 

[twm]

I am not sure if these are

 

[Rodolfo]

Please, the EXPLAIN command result.

 

[twm]

 

[twm]

I changed the type of chv_images to InnoDB,
maybe it's normal now.

 

[Rodolfo]

It seems to be that the problem is on the DB indexes. Test query:

SELECT SQL_NO_CACHE * FROM chv_images WHERE chv_images.image_album_id=17028 ORDER BY chv_images.image_id ASC LIMIT 0,5

^ Takes 2.3181s (Album 17028 has 18587 images)

The problem is that the system needs a composite index for ASC sorting by image_id when doing WHERE image_album_id=X. I've added a composite index (image_album_id_image_id) and now the query takes only 0.0004s.

Cheers,
Rodolfo.

 

[twm]

Thank you

 

[twm]

already fixed
thank you.

Index name: image_album_id_image_id
Index choice: INDEX
Column: image_album_id {bigint(320)]
image_id[bigint(32)]

or

ALTER TABLE `your_database`.`chv_images` ADD INDEX`image_album_id_image_id` (`image_album_id`, `image_id`);

If I need to fix this on other websites, what do I need to do?
thank you.