Skip to main content

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 [QUOTE] 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 [QUOTE][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

Comments

7 comments

Date Votes
  • 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.
    0
  • lfait
    [quote="cPanelMichael, post: 1507712">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. [QUOTE]root@sql [~]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at
    0
  • cPanelMichael
    Please ensure you let MySQL run for at least 24 hours before using the tuner script to ensure accurate results. Thank you.
    0
  • 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
    0
  • 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
    0
  • 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
    0
  • 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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post