MySQL Optimization Help needed

[lfait]

Hello,
Dear I'm facing an issue with MYSQL optimization .

I'm running an SQL Server to process small queries

Server is
24 Proc / 24 GB Ram / NAS + Raid 10 / SSD
Server connections each 3 mins get to 2000 connection currently and will increase very soon.
MYSQL is closing connections perfectly

load average: 62.41, 50.75, 43.41
Free-m

total used free shared buffers cached
Mem: 24028 6468 17559 0 235 3553

The queries i run are small
for example

update number x - ( sent-not sent )
that is it, the queries won't take more than 64K tops


I'm trying to switch the load from CPU to Ram
My current configuration are

[mysqld]
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time=0.1
max_connections = 3000
max_user_connections= 2850
key_buffer_size = 2G
myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 1G
join_buffer_size = 256K
wait_timeout = 10
interactive_timeout = 10
connect_timeout = 15
table_cache = 1
tmp_table_size = 1024M
thread_cache_size = 1
max_heap_table_size = 2048M
table_open_cache = 102400
net_buffer_length = 4096
max_connect_errors = 1000
#thread_concurrency = 2
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 2M
query_cache_limit = 1048576
query_cache_size = 8G
query_cache_type = 1
query_prealloc_size = 1M
query_alloc_block_size = 1M
range_alloc_block_size = 4096
transaction_alloc_block_size = 1024
transaction_prealloc_size = 1024
default-storage-engine = MyISAM
max_write_lock_count = 64
innodb_buffer_pool_size = 2G
innodb_log_buffer_size= 1024M
innodb_flush_log_at_trx_commit=2
skip_name_resolve
slave_net_timeout = 90
delayed_insert_timeout = 120
innodb_flush_method = O_DIRECT

[mysqld_safe]
nice = -10
open_files_limit = 10000


[mysqldump]
quick
max_allowed_packet = 1M

[myisamchk]
sort_buffer_size = 256K
read_buffer_size = 256K
write_buffer_size = 256K

[mysqlhotcopy]
local-infile=0

I'm looking to maximize the connections to get more than 10K each 3 mins without facing such loads .
my tables are in InnoDB it's only 2 tables .


Any help is really needed here .


Regards

 

[cPanelMichael]

Hello

I recommend reviewing the following thread so you can provide us with the results of the MySQL tuner script:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[lfait]

Hello

I recommend reviewing the following thread so you can provide us with the results of the MySQL tuner script:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

I already have it sorry i didn't add it before.

[email protected] [~]# ./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 31)
[--] Data in InnoDB tables: 305M (Tables: 71)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 15

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 36m 53s (3M q [534.605 qps], 45K conn, TX: 1B, RX: 164M)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 14.0G global + 10.6M per thread (3000 max threads)
[!!] Maximum possible memory usage: 45.1G (192% of installed RAM)
[OK] Slow queries: 2% (89K/3M)
[OK] Highest usage of available connections: 35% (1066/3000)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.8M
[!!] Key buffer hit rate: 94.5% (673 cached / 37 reads)
[OK] Query cache efficiency: 95.0% (2M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 51K sorts)
[OK] Temporary tables created on disk: 0% (14 on disk / 51K total)
[!!] Thread cache hit rate: 9% (40K created / 45K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 0% (111/207K)
[OK] Table locks acquired immediately: 100% (180K immediate / 180K locks)
[OK] InnoDB data size / buffer pool: 305.2M/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
thread_cache_size (> 1)

[email protected] [~]#

I know i have max memory, but it's not using the memory and i do have much free memory .


Regards

 

[cPanelMichael]

Please ensure you let MySQL run for at least 24 hours before using the tuner script to ensure accurate results.

Thank you.

 

[thinkbot]

Adjust those:


table_cache = 1 - remove it
net_buffer_length = 4096 - remove it

add
max_allowed_packet = 20M
myisam_use_mmap=1

adjust:

tmp_table_size = 50M
max_heap_table_size = 50M

thread_cache_size = 1500 # thats the single value that would have the highest importance in your case (since many connections/threads)

table_open_cache = 500 # you have 31 MyISAM tables, so dont set it too high like 102400, since this value doesn't scale well in MySQL 5.5, 500 is more than enough in your case

read_rnd_buffer_size = 2M

# all those settigs are too high, very important to set it much lower, even if cache prunes
query_cache_limit = 1M
query_cache_size = 50M
query_cache_type = 1

adjust
innodb_log_buffer_size= 500M
add
innodb_log_file_size = 1024M (you would need to remove /var/lib/mysql/ib_logfile* before restart to apply this setting)


# comment oout or remove all of that below
query_prealloc_size = 1M
query_alloc_block_size = 1M
range_alloc_block_size = 4096
transaction_alloc_block_size = 1024
transaction_prealloc_size = 1024



To handle more connections increase
max_connections = 3000
max_user_connections= 2850


then restart, you can also clear slow log before restart
The best would be to gather review of your current log, before restart with

cd /root
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/log/mysql/mysql-slow.log > slow_before.log
later you can post slow_before.log contents for review

then remove old slow log
rm -rf /var/log/mysql/mysql-slow.log

and restart here

after a while generate slow log review from new slow log

btw. Does your queries that runs during that xxxx conenctions work on MyISAM or InnoDB tables, those are mostly updates there ?

please also run command mount, and post the result here
your mysql is on those SSD or RAID 10 (7200 or 15000 rpm) ?

 

[lfait]

Edits has been made, waiting 24 hours to update the post.

my NAS server is Raid10 SSD so i believe it's 15K RPM, my NAS load is Perfect so i doubt it's related to the NAS .

Regards

 

[lfait]

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 31)
[--] Data in InnoDB tables: 293M (Tables: 71)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 15

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 8h 12m 14s (5M q [18.436 qps], 795K conn, TX: 2B, RX: 369M)
[--] Reads / Writes: 44% / 56%
[--] Total buffers: 5.1G global + 2.9M per thread (2500 max threads)
[OK] Maximum possible memory usage: 12.2G (51% of installed RAM)
[OK] Slow queries: 0% (5/5M)
[OK] Highest usage of available connections: 7% (177/2500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.8M
[OK] Key buffer hit rate: 100.0% (117K cached / 58 reads)
[OK] Query cache efficiency: 21.0% (391K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 323 sorts)
[OK] Temporary tables created on disk: 21% (566 on disk / 2K total)
[OK] Thread cache hit rate: 99% (177 created / 795K connections)
[OK] Table cache hit rate: 89% (154 open / 172 opened)
[OK] Open file limit used: 0% (112/12K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 293.2M/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance

This is the current output .

Thanks for helping, now 1 tiny question more, regarding to fragmented tables, i expect tables to be update frequently in each 5 minutes and for sure it will get fragmented any advice's ?

Regards

 

[thinkbot]

dont worry about fragmentation, it has almost no impact in your case
you will have info about fragmented tables from mysqltuner most of the time when you run it, wherever you run it


results are very nice, what load did you have while running your scripts ?

and you can further decrease read_rnd_buffer_size = 2M
to 256K

read_rnd_buffer_size = 256K