MariaDB crazy at 500% cpu time

GLOBAL STATUS

MariaDB [(none)]> SHOW GLOBAL STATUS;
+---------------------------------------------+---------------------------------------------+
| Variable_name                                                | Value                                            |
+---------------------------------------------+---------------------------------------------+
| Aborted_clients                                              | 10                                               |
| Aborted_connects                                             | 17                                               |
| Access_denied_errors                                         | 2808                                             |
| Acl_column_grants                                            | 0                                                |
| Acl_database_grants                                          | 255                                              |
| Acl_function_grants                                          | 0                                                |
| Acl_procedure_grants                                         | 0                                                |
| Acl_proxy_users                                              | 1                                                |
| Acl_role_grants                                              | 0                                                |
| Acl_roles                                                    | 0                                                |
| Acl_table_grants                                             | 2                                                |
| Acl_users                                                    | 253                                              |
| Aria_pagecache_blocks_not_flushed                            | 0                                                |
| Aria_pagecache_blocks_unused                                 | 15706                                            |
| Aria_pagecache_blocks_used                                   | 40                                               |
| Aria_pagecache_read_requests                                 | 287044                                           |
| Aria_pagecache_reads                                         | 20253                                            |
| Aria_pagecache_write_requests                                | 36614                                            |
| Aria_pagecache_writes                                        | 36593                                            |
| Aria_transaction_log_syncs                                   | 1                                                |
| Binlog_commits                                               | 0                                                |
| Binlog_group_commits                                         | 0                                                |
| Binlog_group_commit_trigger_count                            | 0                                                |
| Binlog_group_commit_trigger_lock_wait                        | 0                                                |
| Binlog_group_commit_trigger_timeout                          | 0                                                |
| Binlog_snapshot_file                                         |                                                  |
| Binlog_snapshot_position                                     | 0                                                |
| Binlog_bytes_written                                         | 0                                                |
| Binlog_cache_disk_use                                        | 0                                                |
| Binlog_cache_use                                             | 0                                                |
| Binlog_stmt_cache_disk_use                                   | 0                                                |
| Binlog_stmt_cache_use                                        | 0                                                |
| Busy_time                                                    | 0.000000                                         |
| Bytes_received                                               | 243371501                                        |
| Bytes_sent                                                   | 2355355672                                       |
| Com_admin_commands                                           | 2293                                             |
| Com_alter_db                                                 | 0                                                |
| Com_alter_db_upgrade                                         | 0                                                |
| Com_alter_event                                              | 0                                                |
| Com_alter_function                                           | 0                                                |
| Com_alter_procedure                                          | 0                                                |
| Com_alter_server                                             | 0                                                |
| Com_alter_table                                              | 0                                                |
| Com_alter_tablespace                                         | 0                                                |
| Com_alter_user                                               | 0                                                |
| Com_analyze                                                  | 0                                                |
| Com_assign_to_keycache                                       | 0                                                |
| Com_begin                                                    | 0                                                |
| Com_binlog                                                   | 0                                                |
| Com_call_procedure                                           | 0                                                |
| Com_change_db                                                | 92                                               |
| Com_change_master                                            | 0                                                |
| Com_check                                                    | 0                                                |
| Com_checksum                                                 | 0                                                |
| Com_commit                                                   | 0                                                |
| Com_compound_sql                                             | 0                                                |
| Com_create_db                                                | 0                                                |
| Com_create_event                                             | 0                                                |
| Com_create_function                                          | 0                                                |
| Com_create_index                                             | 0                                                |
| Com_create_procedure                                         | 0                                                |
| Com_create_role                                              | 0                                                |
| Com_create_server                                            | 0                                                |
| Com_create_table                                             | 0                                                |
| Com_create_temporary_table                                   | 0                                                |
| Com_create_trigger                                           | 0                                                |
| Com_create_udf                                               | 0                                                |
| Com_create_user                                              | 0                                                |
| Com_create_view                                              | 0                                                |
| Com_dealloc_sql                                              | 0                                                |
| Com_delete                                                   | 8354                                             |
| Com_delete_multi                                             | 0                                                |
| Com_do                                                       | 0                                                |
| Com_drop_db                                                  | 0                                                |
| Com_drop_event                                               | 0                                                |
| Com_drop_function                                            | 0                                                |
| Com_drop_index                                               | 0                                                |
| Com_drop_procedure                                           | 0                                                |
| Com_drop_role                                                | 0                                                |
| Com_drop_server                                              | 0                                                |
| Com_drop_table                                               | 0                                                |
| Com_drop_temporary_table                                     | 0                                                |
| Com_drop_trigger                                             | 0                                                |
| Com_drop_user                                                | 0                                                |
| Com_drop_view                                                | 0                                                |
| Com_empty_query                                              | 0                                                |
| Com_execute_immediate                                        | 0                                                |
| Com_execute_sql                                              | 0                                                |
| Com_flush                                                    | 0                                                |
| Com_get_diagnostics                                          | 0                                                |
| Com_grant                                                    | 0                                                |

488 rows in set (0.00 sec)

MariaDB [(none)]>

my.cnf

[root@host ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
log-error=/var/lib/mysql/s102.halabtech.net.err
performance-schema=0
innodb_file_per_table=1
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=40000
skip-name-resolve

sql_mode=''

local-infile=0
connect_timeout=25
wait_timeout=30
interactive_timeout=30
slow-query-log=1
long_query_time=5
slow_query_log_file="/var/log/mysql-slow.log"

key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 2G

Server specs:

Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz

Memory: 4107488k/68927488k available (7792k kernel code, 1900528k absent, 1353716k reserved, 5950k data, 1984k init)

Filesystem      Size  Used Avail Use% Mounted on
devtmpfs         32G     0   32G   0% /dev
tmpfs            32G     0   32G   0% /dev/shm
tmpfs            32G   28M   32G   1% /run
tmpfs            32G     0   32G   0% /sys/fs/cgroup
/dev/md2        437G  273G  142G  66% /
/dev/md1        488M  401M   62M  87% /boot
tmpfs           6.3G     0  6.3G   0% /run/user/0
tmpfs           6.3G     0  6.3G   0% /run/user/987
tmpfs           6.3G     0  6.3G   0% /run/user/1034

UPDATE

MYSQL SLOW QUERY DUMP: (Database real names changed)

Reading mysql slow query log from /var/log/mysql-slow.log
Count: 2  Time=20.58s (41s)  Lock=0.00s (0s)  Rows_sent=4089261.5 (8178523), Rows_examined=4089261.5 (8178523), Rows_affected=0.0 (0), root[root]@localhost
  SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

Count: 3  Time=15.38s (46s)  Lock=0.00s (0s)  Rows_sent=81.0 (243), Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

Count: 1  Time=8.68s (8s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4090482.0 (4090482), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
  UPDATE hb_udownloads SET `upackage_id` = 0 WHERE  upackage_id = 403775

Count: 1  Time=5.64s (5s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4088258.0 (4088258), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
  SELECT  udownload_id AS retval FROM hb_udownloads WHERE  user_id = 415064  AND file_id = 78499  LIMIT 1

Count: 1  Time=5.58s (5s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4088256.0 (4088256), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
  SELECT  udownload_id AS retval FROM hb_udownloads WHERE  user_id = 208286  AND file_id = 202629  LIMIT 1

Count: 1  Time=5.35s (5s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4088255.0 (4088255), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
  SELECT  udownload_id AS retval FROM hb_udownloads WHERE  user_id = 235082  AND file_id = 473624  LIMIT 1

Count: 1  Time=5.21s (5s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4088254.0 (4088254), Rows_affected=0.0 (0), support***_res[supporth***_res]@localhost
  SELECT  udownload_id AS retval FROM hb_udownloads WHERE  user_id = 61350  AND file_id = 493488  LIMIT 1

Count: 1  Time=5.17s (5s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4088259.0 (4088259), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
  SELECT  udownload_id AS retval FROM hb_udownloads WHERE  user_id = 338554  AND file_id = 439150  LIMIT 1

Kindly please advise why I am having really big spikes sometimes to 500% and what should be done to solve the problem since I am getting lots of 50x errors. Please forgive my bad English too. Thank you in advance.

High CPU usage usually means lots of in-memory table-scanning.

Looking at one of your slow queries:

Time=5.64s (5s)  Rows_sent=0.0 (0)  Rows_examined=4088258.0 (4088258)
SELECT  udownload_id AS retval 
FROM hb_udownloads 
WHERE  user_id = 415064  
AND file_id = 78499  
LIMIT 1

The query is reading the entire 4M rows table but returning at most one of them (and, in this case, none at all).
You need indexes on this table to support this query. A composite one on user_id and file_id would be a good start.

Another performance killer here:

Time=20.58s (41s)  Rows_sent=4089261.5 (8178523)  Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ * 
FROM `hb_udownloads`

Never use "select *" in Production code.
Always specify the columns you want explicitly. I would guess that this table has been "growing" (gaining columns) lately.
OK, without a where clause, this is going to table-scan anyway, but that begs the question - what is the poor client [application] going to do with the 4M rows that this query "throws" at it?

Some of these queries look like they might be in need of optimization.

For example:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

This query is scanning 30000000 rows.

Its possible you could change this one, for example to two queries:

SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

Since the query itself involves a useless join and two full table scans that could be halved.

Much of what you've shown points to issues with query optimization and I'd be looking at that slow query log to determine what the best way to retrieve that data would be with the least amount of table scans.

You'll also probably want to index a lot of that data too for the same purpose.

** This should make the first one a lot faster:

SELECT  k.*, s.sold, s.used
    FROM ( SELECT group_id,
                  SUM(used = 0) AS sold,
                  SUM(used = 1) AS used
               FROM tbl_swkey
         ) AS s
    JOIN tbl_keygroup AS k  ON s.group_id = k.id
    ORDER BY displayorder

And have

tbl_swkey:    INDEX(group_id, used)
tbl_keygroup: INDEX(displayorder)

If you need to discuss this query further, please provide SHOW CREATE TABLE for both tables, the table sizes, and EXPLAIN SELECT ....

** This

SELECT  udownload_id AS retval
    FROM  hb_udownloads
    WHERE  user_id = 415064
      AND  file_id = 78499
    LIMIT  1 

needs a 3-column composite index to be a lot faster:

INDEX(user_id, file_id, udownload_id)

You don't care which matching row you get? Or should you add an ORDER BY? If you add an ORDER BY, my index advice may need to change.

** Did this come from mysqldump?

SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

If you are not happy with how invasive dumps are, there are several discussions on dba.stackexchange.com about that topic.