Problem with MYSQL Load CPU

[wasaade]

I have a problem with a site on my server. The site takes a long time to read large tables (with 200,000 records), and does not save as well.

Cloud Digital Ocean
8 GB RAM
4 CPU
SSD DISK

my.cnf file

[mysqld]
performance-schema=0

join_buffer_size = 6900M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

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

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=25165824
max_allowed_packet=524288000
open_files_limit=80000
default-storage-engine=MyISAM
#innodb_file_per_table=4
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
key_buffer_size = 256M
query_cache_size = 256M
tmp_table_size = 512M
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 256M
max_connections = 250
sort_buffer_size = 8M
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 5M
performance_schema = ON
max_heap_table_size = 512M
innodb_buffer_pool_instances = 2

mysqladmin pr|awk -F "|" '{ print $3 }'|grep _|sort|uniq -c|sort -n
     30  riscozero_us

[[email protected] ~]# mysqladmin proc status
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id   | User         | Host      | db           | Command | Time | State        | Info  |
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 546  | riscozero_us | localhost | riscozero_db | Sleep   | 106  |              |  |
| 548  | riscozero_us | localhost | riscozero_db | Sleep   | 921  |              |  |
| 551  | riscozero_us | localhost | riscozero_db | Sleep   | 115  |              |  |
| 553  | riscozero_us | localhost | riscozero_db | Sleep   | 1    |              |  |
| 555  | riscozero_us | localhost | riscozero_db | Sleep   | 1    |              |  |
| 558  | riscozero_us | localhost | riscozero_db | Sleep   | 236  |              |  |
| 560  | riscozero_us | localhost | riscozero_db | Sleep   | 879  |              |  |
| 563  | riscozero_us | localhost | riscozero_db | Sleep   | 162  |              |  |
| 566  | riscozero_us | localhost | riscozero_db | Sleep   | 607  |              |  |
| 567  | riscozero_us | localhost | riscozero_db | Sleep   | 541  |              |  |
| 569  | riscozero_us | localhost | riscozero_db | Sleep   | 568  |              |  |
| 575  | riscozero_us | localhost | riscozero_db | Sleep   | 928  |              |  |
| 576  | riscozero_us | localhost | riscozero_db | Sleep   | 0    |              |  |
| 583  | riscozero_us | localhost | riscozero_db | Sleep   | 550  |              |  |
| 584  | riscozero_us | localhost | riscozero_db | Sleep   | 570  |              |  |
| 652  | riscozero_us | localhost | riscozero_db | Sleep   | 1    |              |  |
| 665  | riscozero_us | localhost | riscozero_db | Query   | 2    | Sending data | SELECT * FROM cadastros, inscricoes where inscricoes.evento='829' and inscricoes.cpf=cadastros.cpfa |
| 666  | riscozero_us | localhost | riscozero_db | Sleep   | 2    |              |  |
| 668  | riscozero_us | localhost | riscozero_db | Sleep   | 886  |              |  |
| 670  | riscozero_us | localhost | riscozero_db | Sleep   | 134  |              |  |
| 684  | riscozero_us | localhost | riscozero_db | Sleep   | 537  |              |  |
| 692  | riscozero_us | localhost | riscozero_db | Sleep   | 72   |              |  |
| 693  | riscozero_us | localhost | riscozero_db | Sleep   | 1    |              |  |
| 697  | riscozero_us | localhost | riscozero_db | Sleep   | 491  |              |  |
| 701  | riscozero_us | localhost | riscozero_db | Sleep   | 121  |              |  |
| 1095 | riscozero_us | localhost | riscozero_db | Query   | 0    | Sending data | select * from cadastros where cpf='084.233.239-19' limit 1  |
| 1109 | riscozero_us | localhost | riscozero_db | Query   | 0    | Sending data | SELECT * FROM cadastros, inscricoes where inscricoes.evento='829' and inscricoes.cpf=cadastros.cpfa |
| 1114 | riscozero_us | localhost | riscozero_db | Query   | 0    | Sending data | SELECT * FROM cadastros, inscricoes where inscricoes.evento='578' and inscricoes.cpf=cadastros.cpfa |
| 1118 | riscozero_us | localhost | riscozero_db | Query   | 0    | Sending data | SELECT * FROM cadastros, inscricoes where inscricoes.evento='578' and inscricoes.cpf=cadastros.cpfa |
| 1124 | riscozero_us | localhost | riscozero_db | Sleep   | 2    |              |  |
| 1129 | riscozero_us | localhost | riscozero_db | Query   | 0    | Sending data | SELECT * FROM cadastros, inscricoes where inscricoes.evento='578' and inscricoes.cpf=cadastros.cpfa |
| 1131 | root         | localhost |              | Query   | 0    | starting     | show processlist  |
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
Uptime: 2111  Threads: 32  Questions: 212525  Slow queries: 0  Opens: 2432  Flush tables: 1  Open tables: 1412  Queries per second avg: 100.675

Load do servidor

Pid    Owner    Priority    CPU %    Memory %    Command
1718 (Trace) (Kill)    mysql    0      259.98      7.98    /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

 

[cPanelLauren]

I'd suggest going over the following: Tutorial - Troubleshooting high server loads on Linux servers

I'd also suggest enlisting the assistance of a qualified system administrator. If you don't have one you might find one here: System Administration Services