Skip to main content

How to Optimize MySQL

joe94500
Hello. This is my first post. I recently upgraded to my first vps. I notice my server is a bit slow. my.config is as follows: [mysqld] default-storage-engine=MyISAM interactive_timeout=300 key_cache_block_size=4096 max_heap_table_size=32M max_join_size=1000000000 max_allowed_packet=32M open_files_limit=4096 query_cache_size=32M thread_cache_size=100 tmp_table_size=32M wait_timeout=7800 max_user_connections=5000 myisam_recover_options=FORCE innodb_file_per_table=1 innodb_flush_log_at_trx_commit=0 innodb_purge_threads=1 innodb_support_xa=0 innodb_thread_concurrency=8 pid-file=/var/lib/mysql/mysqld.pid
How can it be configured for maximum performance? Thank you.

Comments

3 comments

Date Votes
  • cPanelMichael
    Hello :) Please review the following thread in order to review the information needed to provide you with optimization assistance: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    0
  • thinkbot
    run mysqltuner.pl
    0
  • joe94500
    [quote="thinkbot, post: 1566761">run mysqltuner.pl
    Hello, thanks cPanelMichael and thinkbot for answering my question and pointing me in the right direction. I I have a table with more than a million rows. When trying to go to one of the bottom rows, I get this error: Error writing file '/tmp/MYOV5w52' (Errcode: 28). server summary Filesystem Size Used Avail Use% Mounted on /dev/vda1 30G 21G 7.5G 74% / tmpfs 939M 0 939M 0% /dev/shm /usr/tmpDSK 3.0G 176M 2.6G 7% /tmp my.cnf (This is current mycnf) [mysqld] tmpdir=/tmp innodb_file_per_table=1 open_files_limit=50000 myisam_use_mmap=1 max_connections = 200 max_user_connections = 75 join_buffer_size=2M sort_buffer_size=2M table_open_cache = 8000 table_definition_cache = 6000 max_allowed_packet = 1G thread_cache_size = 384 query_cache_type = 1 query_cache_size = 100M query_cache_limit = 1M max_heap_table_size = 50M tmp_table_size = 300M key_buffer_size = 2500M innodb_buffer_pool_size = 3G innodb_log_buffer_size = 32M innodb_old_blocks_time=1000 innodb_concurrency_tickets=5000 innodb_autoextend_increment=64M innodb_open_files=2000 innodb_stats_on_metadata=0 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1
    mysqltuner result - General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 6G (Tables: 293) [--] Data in InnoDB tables: 224K (Tables: 14) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 45 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 37m 24s (909 q [0.405 qps], 127 conn, TX: 608K, RX: 75K) [--] Reads / Writes: 68% / 32% [--] Total buffers: 5.6G global + 4.6M per thread (200 max threads) [!!] Maximum possible memory usage: 6.5G (356% of installed RAM) [OK] Slow queries: 4% (39/909) [OK] Highest usage of available connections: 6% (13/200) [OK] Key buffer size / total MyISAM indexes: 2.4G/2.4G [!!] Key buffer hit rate: 85.3% (685 cached / 101 reads) [OK] Query cache efficiency: 47.4% (225 cached / 475 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 12 sorts) [OK] Temporary tables created on disk: 11% (2 on disk / 17 total) [OK] Thread cache hit rate: 89% (13 created / 127 connections) [OK] Table cache hit rate: 98% (379 open / 386 opened) [OK] Open file limit used: 1% (666/50K) [!!] Table locks acquired immediately: 93% [OK] InnoDB data size / buffer pool: 224.0K/3.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 Optimize queries and/or use InnoDB to reduce lock wait
    What is the best way to proceed to optimize the mysql? NB: I don't understand plenty of this stuff.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post