Results 1 to 9 of 9
-
08-24-2011, 08:07 PM #1New Member
- Join Date
- Aug 2011
- Posts
- 3
Help me tune the MySQL database/optimize speeds
I recently purchased a brand new VPS, and successfully (with no prior knowledge, go me!) reinstalled my website on it using cpanel's restore full backup function.
Now my site is working, I got the DNS servers working (another lesson learned) and everything is looking good on the new host.
Now I want to optimize the site speed. Someone at another forum told me to run a mysql tuner to check settings and tweak them. Here are those results:
top - 16:33:31 up 1:00, 0 users, load average: 0.12, 0.07, 0.02
Tasks: 44 total, 1 running, 43 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 1048576k total, 717480k used, 331096k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 15 0 2156 664 572 S 0.0 0.1 0:00.17 init
1174 root 19 0 22448 556 460 S 0.0 0.1 0:00.00 brcm_iscsiuio
1379 root 15 0 1812 572 480 S 0.0 0.1 0:00.00 syslogd
1382 root 18 0 1760 404 336 S 0.0 0.0 0:00.00 klogd
1414 dbus 18 0 2844 648 488 S 0.0 0.1 0:00.00 dbus-daemon
1439 named 19 0 71060 4132 2080 S 0.0 0.4 0:00.05 named
1457 root 18 0 7212 1040 636 S 0.0 0.1 0:00.00 sshd
1469 root 18 0 2832 840 668 S 0.0 0.1 0:00.00 xinetd
1481 root 18 0 2544 1152 988 S 0.0 0.1 0:00.00 mysqld_safe
1587 mysql 15 0 421m 45m 4876 S 0.0 4.5 0:01.64 mysqld
1865 mailnull 15 0 10368 1132 648 S 0.0 0.1 0:00.00 exim
1920 root 15 0 15800 13m 1364 S 0.0 1.3 0:00.32 lfd
1930 root 15 0 37620 30m 2420 S 0.0 3.0 0:01.42 spamd
1942 root 18 0 2152 704 536 S 0.0 0.1 0:00.00 dovecot
1943 root 15 0 2628 1024 832 S 0.0 0.1 0:00.00 dovecot-auth
1947 dovecot 16 0 5200 1964 1616 S 0.0 0.2 0:00.00 pop3-login
1948 dovecot 17 0 5200 1964 1616 S 0.0 0.2 0:00.00 pop3-login
1949 dovecot 15 0 5324 2016 1648 S 0.0 0.2 0:00.00 imap-login
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 78M (Tables: 310)
[--] Data in InnoDB tables: 144K (Tables: 9)
[!!] Total fragmented tables: 35
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 1m 3s (5K q [1.610 qps], 440 conn, TX: 52M, RX: 2M)
[--] Reads / Writes: 57% / 43%
[--] Total buffers: 354.0M global + 3.6M per thread (500 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.1G (208% of installed RAM)
[OK] Slow queries: 0% (1/5K)
[OK] Highest usage of available connections: 0% (3/500)
[OK] Key buffer size / total MyISAM indexes: 16.0M/19.1M
[OK] Key buffer hit rate: 96.5% (62K cached / 2K reads)
[OK] Query cache efficiency: 38.9% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 92 sorts)
[OK] Temporary tables created on disk: 22% (13 on disk / 58 total)
[OK] Thread cache hit rate: 99% (3 created / 440 connections)
[!!] Table cache hit rate: 16% (128 open / 753 opened)
[OK] Open file limit used: 10% (257/2K)
[OK] Table locks acquired immediately: 99% (5K immediate / 5K locks)
[OK] InnoDB data size / buffer pool: 144.0K/256.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
table_cache (> 128)
root@s1 [/]#
This is my first real post on this site, I was recommended here by my previous hosting provider to help setup/optimize the loading speeds.
I have apache/mysql/etc. all installed obviously, as IPboard requires those in order to run.
Thanks everyone for the help! So what commands do you recommend?
-
08-24-2011, 09:41 PM #2WHT Addict
- Join Date
- Mar 2011
- Location
- florida
- Posts
- 158
uning MySQL Performance with MySQLTuner
Run MySQLTuner to see recommendations to improve the MySQL performance.
Use these instructions
http://www.howtoforge.com/tuning-mys...ith-mysqltuner
-
08-24-2011, 09:45 PM #3WHT Addict
- Join Date
- Mar 2011
- Location
- florida
- Posts
- 158
N/M you already ran it just follow the recomendations edit the mysql config file
-
08-24-2011, 10:00 PM #4WHT Addict
- Join Date
- Mar 2011
- Location
- florida
- Posts
- 158
use this
on VPS server with very limited memory it is good to tweak mysql5. In /etc/mysql/my.cnf
# Main MySQL server options
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# No locking at all!
skip-locking
# Set internal buffers, caches and stacks very low
key_buffer = 16K
max_allowed_packet = 16K
table_cache = 1
sort_buffer_size = 16K
read_buffer_size = 16K
read_rnd_buffer_size = 1K
net_buffer_length = 1K
thread_stack = 16K
# Don't listen on a TCP/IP port at all.
# Will still work provided all access is done via localhost
skip-networking
server-id = 1
# Skip Berkley and Inno DB types
skip-bdb
skip-innodb
# Set the query cache low
query_cache_limit = 1048576
query_cache_size = 1048576
query_cache_type = 1
# Set various memory limits very low, disable memory-hogging extras
[mysqldump]
quick
max_allowed_packet = 16K
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 16K
sort_buffer_size = 16K
[myisamchk]
key_buffer = 16K
sort_buffer_size = 16K
[mysqlhotcopy]
interactive-timeout
-
08-24-2011, 10:10 PM #5Web Hosting Master
- Join Date
- Jul 2010
- Posts
- 797
seems the mysql just started less than 24 hours..
run again after 24 hours and see what is the recommendationLooking for shared or reseller or VPS Hosting ?
Try our service at https://www.sosys.net!
Singapore - Indonesia - Malaysia
-
08-24-2011, 10:20 PM #6(formerly WhichGunDotCom)
- Join Date
- Jun 2011
- Location
- Woodbridge, NJ
- Posts
- 840
A VPS with 1 GB of memory is hardly what I would call "very limited memory".
Some comments on the configuration you recommended:
Code:# No locking at all! skip-locking
Code:# Set internal buffers, caches and stacks very low key_buffer = 16K max_allowed_packet = 16K table_cache = 1 sort_buffer_size = 16K read_buffer_size = 16K read_rnd_buffer_size = 1K net_buffer_length = 1K thread_stack = 16K # Set the query cache low query_cache_limit = 1048576 query_cache_size = 1048576 query_cache_type = 1
Code:# Skip Berkley and Inno DB types skip-bdb skip-innodb
To the OP:
What software and scripts are you running on this VPS? What type of website(s) are you running? It may make sense to convert the few InnoDB tables you have to MyISAM, but we're going to need to know exactly what you're running to make that determination. You might be better off converting everything to InnoDB.
The mysqltuner script is a great tool, but you need to have the server running for some time before it can make decent suggestions.
Another similar tool is the tuning-primer script available at https://launchpad.net/mysql-tuning-primer.
As long as the site is running OK in the meantime, I would advise letting it run as-is for at least 24 hours, preferably 48. Then, run the mysqltuner and tuning-primer scripts and post the output here, along with some more details about what's running on the server.
-
08-24-2011, 10:21 PM #7New Member
- Join Date
- Aug 2011
- Posts
- 3
Ok. did those tweaks without issue. Besides the my.cnf, is there anything else I should look at to increase times?
-
08-24-2011, 10:24 PM #8New Member
- Join Date
- Aug 2011
- Posts
- 3
I'm running IPBoard on a CentOS 5.x system. (32bit). Nothing else is on the vps, its brand new.
-
08-24-2011, 10:50 PM #9(formerly WhichGunDotCom)
- Join Date
- Jun 2011
- Location
- Woodbridge, NJ
- Posts
- 840
I'm going to recommend removing those tweaks for the time being. They are settings that I would never even think of using unless it was on a really tiny (as in < 64 MB) server. They are horrible, absolutely horrible settings for a VPS with 1 GB of memory, and they will have a severely negative effect on your site's speed.
Be sure to restart the MySQL service so they take effect. On CentOS, this is done by:
Code:sudo service mysqld restart
Similar Threads
-
SQL Server 2005 DBA needed to performance tune database
By techforce in forum Systems Management RequestsReplies: 0Last Post: 01-24-2011, 08:09 PM -
Optimize All Tables In A MySQL Database
By DjiXas in forum Hosting Security and TechnologyReplies: 7Last Post: 10-30-2008, 07:47 AM -
How 2 Optimize / Tune Servers (HTTP/FTP/SSH/PHP/MYSQL)
By mouseattack in forum Hosting Security and TechnologyReplies: 12Last Post: 07-19-2005, 01:28 AM -
Do I need to optimize my mysql database to speed up my site?
By Max Renn in forum Programming DiscussionReplies: 6Last Post: 03-26-2005, 03:43 AM -
How to optimize MySQL Database?
By kencash in forum Hosting Security and TechnologyReplies: 1Last Post: 12-24-2004, 06:50 AM