Server Troubleshooting Tips

#1

SCENARIO: SERVER DOWN

Troubleshooting Tips:

Sometimes it may appear that a server is down when it's anything but. Use these steps to fully verify if your server is up or down:

STEP 1 - verifying you are currently up to date on payment

A. Log into the billing system to ensure that you are not past due on your account. Billing gives a 5 day grace period
for all accounts which are past due. If an account is past due, the billing system will automatically send an email
to you, notifying you of this.

B. Verify that your contact information is up to date and fully correct.

C. Check the email address that is on file for emails sent from the billing system notifying you of a past due balance.
If you are using an unreliable free email service such as gmail, Yahoo, or Hotmail, you may need to check your
junk, spam, or bulk email folders. This is a good idea even if you aren't using a free email provider.

STEP 2 - verifying you can ping your server

If you are not currently past due on your account, and you are still experiencing issues, try to ping
your VDS by IP address.

A. If you are using Windows, do these steps to ping your VDS via IP address:

- click Start
- click Run
- in the box that opens, type: cmd

* A black DOS window should now be open on your screen

- type: ping 1.2.3.4 (where "1.2.3.4" is the IP address of your VDS. If you don't know the IP address
of your VDS, you may find it in your Welcome email).

If you get ping replies, then your server is not down.

B. Now try to ping your VDS by domain name:

- type: ping example.com (where "example.com" is a domain hosted on your VDS)

If you get an error stating something similar to "can't find example.com: Server failed",
then perhaps named (the DNS service on your VDS) is not running.

Log into your VDS via ssh and verify named is running:
# /sbin/service named status

If you see this, then named is not running:



rndc: connect failed: connection refused

If named is not running but should be, then you will need to restart named:
# /sbin/service named restart

which should display this:



Starting named:                                            [  OK  ]

If named was down and you had to restart it, then this is likely why you were
unable to reach your sites, despite your VDS returning ping replies.

SCENARIO: WEBSITES ARE NOT LOADING

Troubleshooting Tips:

STEP 1 - log in and verify your webserver, database, and DNS services are running

A. Log into your VDS via ssh.

Information on ssh can be found here:

Basic Linux Systems Administration
http://forum.myriadnetwork.com/showthread.php?t=157

B. Check that the webserver (Apache) is running:
# /sbin/service httpd status

If you're unsure by the output if httpd is running or not (the output can differ from minimal
to verbose depending on how Apache is set up), then check the process list:

# ps aux | grep httpd



root     17746  0.1  0.0  7224 2888 ?        Ss   20:09   0:00 /usr/local/apache/bin/httpd -k graceful -DSSL

nobody   17747  0.0  0.0  7356 2604 ?        S    20:09   0:00 /usr/local/apache/bin/httpd -k graceful -DSSL

nobody   17748  0.0  0.0  7356 2604 ?        S    20:09   0:00 /usr/local/apache/bin/httpd -k graceful -DSSL

nobody   17749  0.0  0.0  7224 2016 ?        S    20:09   0:00 /usr/local/apache/bin/httpd -k graceful -DSSL

nobody   17750  0.0  0.0  7224 2012 ?        S    20:09   0:00 /usr/local/apache/bin/httpd -k graceful -DSSL

nobody   17751  0.0  0.0  7224 2012 ?        S    20:09   0:00 /usr/local/apache/bin/httpd -k graceful -DSSL

The output above shows that httpd is running. If it is not, restart it:
# /sbin/service httpd start

C. Check that MySQL is running (if you're utilizing it) -
# ps aux | grep mysql



root      8028  0.0  0.0  2152 1124 ?        S    Nov23   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/host.example.com.pid

mysql     8055  0.0  0.1 118196 17048 ?      Sl   Nov23   0:04 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.example.com.pid --skip-external-locking --socket=/tmp/mysql.sock

If it's not running, restart it:
# /sbin/service mysql start



Starting MySQL SUCCESS!

Note: if you are using a cPanel VDS, you can utilize the "Service Manager" in WHM for automatically
checking your services, and restarting them if they are down. The services include the following:



exim - SMTP Server

ftpd - Ftp Server

httpd - Web Server

imap - IMAP Server

mysql - MySQL Database Server

named - Berkely Name Server Daemon (BIND)

pop - POP3 Server

syslogd - System Logger daemon

Exim on another port - Run another copy of Exim on port

Q. How often will chkservd (the Service Manager feature described above) check my services?
A. About every 8.5 minutes, and seen in /var/log/chkservd.log:



[Fri Nov 30 20:07:49 2007] Service check ...cpsrvd [+]...exim [+]...httpd [+]...mysql [+]...named [+]...Done

[Fri Nov 30 20:16:14 2007] Service check ...cpsrvd [+]...exim [+]...httpd [+]...mysql [+]...named [+]...Done

SCENARIO: RANDOM ISSUES THAT OCCUR AT RANDOM TIMES

STEP 1 check /proc/user_beancounters

Q. What is /proc/user_beancounters ?
A. user_beancounters is a file that exists in the /proc directory which will give you invaluable information
as to any issues occuring on your VDS. To view it, type this, as root:

# cat /proc/user_beancounters

I will explain a few basic things here about the output, but the best information
on the Internet about user_beancounters can be found here:
http://wiki.openvz.org/UBC

What you will see are a few things under these columns:

resource           held    maxheld    barrier      limit    failcnt

The "resource" field is the name of the server resource allocated to your server.
This includes things such as memory allocation limits (kmemsize, physpages, vmguarpages,
privvmpages), maximum number of concurrent process limits (numproc), maximum number of
concurrent TCP connections (numtcpsock), maximum number of open files (numfile), maximum
number of iptables entries (numiptent), and more.

The column you will want to note is the "failcnt" column. If your server has attempted to exceed
a particular resource, the failcnt column will increase each this time happens. If you exceed your
memory allocation, some times services will fail. This includes named, and MySQL. Thus, it is important
to monitor your server and to know how to restart your services.

It is also a good idea to know how to investigate any issues that occur. If you log into your VDS
and see that you have failures in /proc/user_beancounters for a certain resource, this can be difficult
to investigate since there isn't any information about what caused the issues. However, the more familiar
you are with your server, and the more you learn about the server's environment, the easier it is to
locate issues.

This brings us to log files.

STEP 2 - Checking your log files

Q. Where are my logs? What does each log pertain to? How do I view them?
A. Your log files are in the locations listed below, and can be viewed via the "less" command:
less /path/to/log

syslogs
/var/log/messages (logins, logouts, service restarts)
/var/log/secure (user adds, user deletes, logins, some cPanel logs)

cron logs
/var/log/cron

Apache domain logs
/usr/local/apache/domlogs/example.com (requests to the domain)

Apache server logs
/usr/local/apache/logs/error_log (Apache restarts, shutdowns, failed starts)
/usr/local/apache/logs/access_log (various logs of webserver traffic)

MySQL
/var/lib/mysql/host.example.com.err (MySQL startups and shutdowns, corrupt tables)

Email
/var/log/maillog (SpamAssassin info, logins, logouts)
/var/log/exim_mainlog (main mailserver log - email sent/delivered/failed)
/var/log/exim_rejectlog (failed attempts to relay email through the server, SpamAssassin messages)
/var/log/exim_paniclog (mailserver config file ( /etc/exim.conf ) errors, failure to start messages)

cPanel
/usr/local/cpanel/logs/access_log (cPanel account activity)
/usr/local/cpanel/logs/error_log (cPanel errors)
/usr/local/cpanel/logs/login_log (failed cPanel logins)

/var/cpanel/updatelogs/update.nnnnnnnnnn.log (cPanel update log)
/var/cpanel/updatelogs/update.nnnnnnnnnn.postinstall.log (cPanel post update log)

Those are many of the logs that exist on a cPanel VDS. There are others in various locations, but for
the sake of this tutorial, those are the main ones that can help in troubleshooting many issues of
various types.

If you'd like to watch a log in real time, just use tail -f, like so:
# tail -f /var/log/exim_mainlog

tailing a log file can be particulary useful in the event that you try to start a service,
but it fails. Open 2 connections to your server via ssh. In one window, tail -f /path/to/log,
and then in the other, try to restart the service. Just about any service will log a failure
message in the event that something is not working or configured properly.

Logs files can contain some interesting information, and they do take some time to learn
since they all speak their own language of sorts. Often times you'll find that googling part
of a log will yield a large number of results. Other times you will find that consulting
the documentation of the service will provide the best information.

If all else fails, you can follow the instructions here for rebooting your VDS:

Reboot Your VDS
http://forum.myriadnetwork.com/showthread.php?t=362

Please keep in mind that we do not monitor each invidual's VDS or the services that run on it
(with the exception of managed VDS customers, whos servers are monitored continually). If something
is not working properly and you need assistance, it is ultimately up to you to bring this to our attention.
We will likely not know that there is an issue otherwise unless there is an overall server or network issue,
which we would - as we always have - post about in the "Server & Network Status" section of these forums.

All of the information contained within this thread is very important to know for administering a server.
If you have any questions about anything contained within this tutorial, feel free to ask here,
or submit a ticket (such as if you'd like to submit sensitive log information for our review and advice).

Re: Server Troubleshooting Tips

#2

uid resource held maxheld barrier limit failcnt
552: kmemsize 10492116 10509828 23317184 25317184 82451776
lockedpages 0 0 3686 4096 0
privvmpages 136587 136626 256000 286720 27926
shmpages 4756 4756 133120 133120 0
dummy 0 0 0 0 0
numproc 147 147 300 300 207220
physpages 69499 69502 0 2147483647 0
vmguarpages 0 0 102400 2147483647 0
oomguarpages 70468 70471 102400 2147483647 0
numtcpsock 30 30 500 500 0
numflock 8 8 200 200 12
numpty 1 1 16 16 0
numsiginfo 0 0 512 512 0
tcpsndbuf 593836 593836 5120000 10240000 23055
tcprcvbuf 491520 491520 5120000 10240000 242
othersockbuf 51016 51016 2304000 3807232 372
dgramrcvbuf 0 0 1843200 2048000 0
numothersock 51 51 500 500 5623
dcachesize 0 0 4194304 4317184 0
numfile 3904 3913 8192 8192 17774
dummy 0 0 0 0 0
dummy 0 0 0 0 0
dummy 0 0 0 0 0
numiptent 77 77 3000 3000 0

Re: Server Troubleshooting Tips

#3

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.45-community i686

Uptime = 1 days 22 hrs 32 min 58 sec
Avg. qps = 19
Total Questions = 3234187
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 10 sec.
You have 9 out of 3234205 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 62
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 175
Current threads_connected = 2
Historic max_used_connections = 148
The number of used connections is 84% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 755 M
Configured Max Per-thread Buffers : 775 M
Configured Max Global Buffers : 100 M
Configured Max Memory Limit : 875 M
Physical Memory : 7.91 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 56 M
Current key_buffer_size = 42 M
Key cache miss rate is 1 : 1121
Key buffer fill ratio = 24.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 48 M
Current query_cache_used = 27 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 57.68 %
Current query_cache_min_res_unit = 4 K
Query Cache is 25 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 9321 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 300 tables
You have a total of 394 tables
You have 300 open tables.
Current table_cache hit rate is 8%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 190912 temp tables, 55% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 503 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 168
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Re: Server Troubleshooting Tips

#4

sh /etc/tuning-primer.sh
cat /proc/user_beancounters

Re: Server Troubleshooting Tips

#5

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.45-community i686

Uptime = 2 days 12 hrs 11 min 51 sec
Avg. qps = 17
Total Questions = 3730625
Threads Connected = 4

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 10 sec.
You have 19 out of 3730662 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 59
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 175
Current threads_connected = 5
Historic max_used_connections = 157
The number of used connections is 89% of the configured maximum.
You should raise max_connections

MEMORY USAGE
Max Memory Ever Allocated : 795 M
Configured Max Per-thread Buffers : 775 M
Configured Max Global Buffers : 100 M
Configured Max Memory Limit : 875 M
Physical Memory : 7.91 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 55 M
Current key_buffer_size = 42 M
Key cache miss rate is 1 : 1568
Key buffer fill ratio = 39.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 48 M
Current query_cache_used = 23 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 48.29 %
Current query_cache_min_res_unit = 4 K
Query Cache is 23 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 4342 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 300 tables
You have a total of 394 tables
You have 300 open tables.
Current table_cache hit rate is 9%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 172217 temp tables, 49% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 492 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 168
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Re: Server Troubleshooting Tips

#6

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.45-community i686

Uptime = 3 days 12 hrs 50 min 46 sec
Avg. qps = 15
Total Questions = 4714500
Threads Connected = 9

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 10 sec.
You have 29 out of 4714523 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 56
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 175
Current threads_connected = 8
Historic max_used_connections = 157
The number of used connections is 89% of the configured maximum.
You should raise max_connections

MEMORY USAGE
Max Memory Ever Allocated : 795 M
Configured Max Per-thread Buffers : 775 M
Configured Max Global Buffers : 100 M
Configured Max Memory Limit : 875 M
Physical Memory : 7.91 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 56 M
Current key_buffer_size = 42 M
Key cache miss rate is 1 : 1504
Key buffer fill ratio = 34.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 48 M
Current query_cache_used = 28 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 60.19 %
Current query_cache_min_res_unit = 4 K
Query Cache is 23 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 5337 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 300 tables
You have a total of 394 tables
You have 300 open tables.
Current table_cache hit rate is 7%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 239250 temp tables, 50% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 503 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 174
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Re: Server Troubleshooting Tips

#7

(stuff in () are for 128KB systems)

# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients

#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M (16M)
max_allowed_packet = 1M
table_cache = 256 (64)
sort_buffer_size = 1M (512K)
(net_buffer_length = 8K)
read_buffer_size = 1M (256K)
read_rnd_buffer_size = 4M (512K)
myisam_sort_buffer_size = 64M (8M)
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M (16M)
#innodb_additional_mem_pool_size = 20M (2M)
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M (5M)
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

quick
max_allowed_packet = 16M

no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

key_buffer = 128M (20M)
sort_buffer_size = 128M (20M)
read_buffer = 2M
write_buffer = 2M

key_buffer = 128M (20M)
sort_buffer_size = 128M (20M)
read_buffer = 2M
write_buffer = 2M

interactive-timeout

Re: Server Troubleshooting Tips

#8

Feb 1 my.cnf setup

skip-locking
skip-bdb
skip-ndbcluster
safe-show-database
log-queries-not-using-indexes
log-slow-queries
long_query_time = 5
max_connections = 225
key_buffer_size = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 500
thread_cache_size = 64
interactive_timeout = 20
wait_timeout = 20
connect_timeout = 15
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 48M
query_cache_type = 1
tmp_table_size = 20M

innodb_flush_method=O_DIRECT

[mysqld_safe]
open_files_limit = 8192

quick
max_allowed_packet = 16M

key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M

old_passwords = 1

Re: Server Troubleshooting Tips

#9

Feb 1 UPDATED var/lib/mysql/my.cnf

port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-bdb
skip-ndbcluster
safe-show-database
log-queries-not-using-indexes
log-slow-queries
long_query_time = 5

max_connections = 225

key_buffer_size = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M

table_cache = 500
thread_cache_size = 8
interactive_timeout = 20
wait_timeout = 20
connect_timeout = 15
thread_concurrency = 8

max_allowed_packet = 16M
max_connect_errors = 10

query_cache_limit = 1M
query_cache_size = 48M
query_cache_type = 1

tmp_table_size = 20M

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 8M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 15M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT

[mysqld_safe]
open_files_limit = 8192

quick
max_allowed_packet = 16M

no-auto-rehash

key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M

key_buffer = 32M
sort_buffer = 32M
read_buffer = 2M
write_buffer = 2M

old_passwords = 1

Re: Server Troubleshooting Tips

#10

server crashed. reading after 6 hours

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.45-community-log i686

Uptime = 0 days 6 hrs 23 min 22 sec
Avg. qps = 42
Total Questions = 982953
Threads Connected = 12

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 5 sec.
You have 25587 out of 983017 that take longer than 5 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 52
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 225
Current threads_connected = 13
Historic max_used_connections = 65
The number of used connections is 28% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 361 M
Configured Max Per-thread Buffers : 996 M
Configured Max Global Buffers : 74 M
Configured Max Memory Limit : 1 G
Physical Memory : 7.91 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 107 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 1370
Key buffer fill ratio = 53.00 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 48 M
Current query_cache_used = 26 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 55.67 %
Current query_cache_min_res_unit = 4 K
Query Cache is 16 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 1485 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1235 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
Current table_cache value = 500 tables
You have a total of 466 tables
You have 500 open tables.
Current table_cache hit rate is 80%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 20 M
Of 45500 temp tables, 52% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 277 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 285
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Re: Server Troubleshooting Tips

#11

port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-bdb
skip-innodb
skip-ndbcluster
safe-show-database
log-queries-not-using-indexes = var/lib/mysql/queries-no-index
log-slow-queries
long_query_time = 5

max_connections = 200
open_files_limit = 1500

key_buffer_size = 16M
myisam_sort_buffer_size = 20M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M

table_cache = 600
thread_cache_size = 8
interactive_timeout = 20
wait_timeout = 20
connect_timeout = 15
thread_concurrency = 4

max_allowed_packet = 16M
max_connect_errors = 10

query_cache_limit = 1M
query_cache_size = 48M
query_cache_type = 1

tmp_table_size = 20M

[mysqld_safe]
open_files_limit = 8192

quick
max_allowed_packet = 16M

no-auto-rehash

key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M

key_buffer = 32M
sort_buffer = 32M
read_buffer = 2M
write_buffer = 2M

old_passwords = 1

Re: Server Troubleshooting Tips

#12

query_cache_size = 16M