MySQL Help Wanted: Current my.cnf file

[Michael Morris]

I know at least a couple users on here use mysql. As of 2:37AM this morning this is the mysql.cnf settings the machine seems happy with - but I won't know for sure until the morning and noon rushes work. As it stands I'm getting page replies within half a second at the moment.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /var/run/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket        = /var/run/mysql/mysql.sock
skip-locking
key_buffer = 384M
#max_allowed_packet = 1M
max_allowed_packet = 2M
table_cache = 512
#sort_buffer_size = 2M
sort_buffer_size = 4M
#read_buffer_size = 2M
read_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
#max_connections = 650
max_connections = 2048
# 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

# Replication Master Server (default)
# binary logging is required for replication
log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir        = /tmp/        
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2: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 = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 32M
write_buffer = 32M

[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

Incidentally, if I comment out the log-bin line in this file will that stop the binary logs from clogging up the machine and can it be safely done?

[Mustrum_Ridcully]

I don't really have an idea how to help you, but I am reminded of this story:
http://thedailywtf.com/Articles/Yes,...ill-There.aspx

[miscreationist]

I'm not sure what table types you are using. I don't know much about vBulletin, but I did do a cursory search about the subject and the default (at one point at least) is MyISAM. If this is the case, then those settings seem reasonable assuming that you have 8GB of memory and the database is all that machine does (I am certain for a site of this size that a dedicated machine is necessary).

If you are using InnoDB, then you need to tune the settings on InnoDB tables:

See: http://forums.mysql.com/read.php?34,216358,216358 and http://dev.mysql.com/doc/refman/5.0/...arameters.html

Many of those options depend on the amount of memory on the machine, whether or not the machine is dedicated to serving database requests, and the size of tables and indexes. Here are some good places to start:

http://www.databasejournal.com/featu...le.php/3367871
http://dev.mysql.com/doc/refman/5.0/...arameters.html

A perl script designed to provide recommendations for settings based on the machine it is running on (be sure to wrap the contents in <html><body><pre> tags): http://www.pythian.com/blogs/437/mys...ofile-script-2

As for log-bin, if up to the minute of crash restoration is necessary, keep using it. If not, do periodic full backups. Keep in mind that unless you are using InnoDB tables that the data in the backup could have changed during the dump.

Anyway, I hope that can be of some use.

[Thakazum]

Michael, did you change anything since you posted this? It seems to be not doing so well. I really suggest that you look at your slow query log (turn it on if you haven't) and DESCRIBE on those queries to find some good indexes. "High Performance MySQL" is a good book that has some indexing and tuning suggestions.

miscreationist - he's not using InnoDB by the looks of the cnf file.

[Michael Morris]

All tables in use are myISAM. We have 8GB on the board and 4 processor cores. I'm beginning to think the culprit is httpd.conf though since the slow loading is occuring even on the pages that don't require a database to be instatiated to be seen, such as Claudio Pozas' site.

[Thakazum]

Seems zippy at the moment

[Quartz]

I know next to nothing about SQL, but being affected by the slowness, I have to ask, why do you think the problem is with SQL? Have you looked at the bandwidth logs, disk queue statistics, etc? Is the host server a real machine or a virtual machine?

[Nifft]

Originally Posted by Michael Morris

[mysqld]
# 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

Question: is MySQL running on the same box as httpd? If so, why not do the connections through a non-network pipe?

IMXP the tcp/ip queue shouldn't be a bottleneck under normal conditions, but I've never run a website the size of ENW.

Cheers, -- N

[Michael Morris]

I think I'll try that later tonight - we'll know when I restart the server and the site goes totally down.

[Reveille]

Originally Posted by Michael Morris

I think I'll try that later tonight - we'll know when I restart the server and the site goes totally down.

Just don't forget to announce the downtime.

[Schmoe]

I think it's unlikely that MySQL is the problem, assuming you have a reasonable dedicated machine. There are a couple things to look for, though:

Connections usage - If connections are maintained for too long, or if a single query connects multiple times, depending on traffic, you could be exceeding the maximum connections, which will cause further queries to time out. The best place to start with this is to review how connections are used.

SQL - The actual SQL used can have a huge impact on query response times. Try to tune the queries the best you can.

Indexing - Could be the tables aren't indexed properly. In combination with the SQL, this is the biggest bang-for-your-buck and the most likely to improve performance. If you've already worked out indexes appropriate for the tables (maybe carried over from previous DB implementations), it could be something as simple as forgetting to create the indices. I don't know if it will help at all, but MySQL allows you to index only a prefix of a column value, which is useful for wide columns, such as text.

If you are certain that the database performance is the problem, you may want to try partitioning the tables. The latest version of MySQL provides a number of partitioning options, which can greatly enhance query performance.

I apologize if some of this seems elementary and redundant. I'm coming late to the party, so I'm not sure what's been discussed before.

[Schmoe]

Also, it would be very helpful if you run

$mysqladmin variables

And include the display in a post here.

[jmucchiello]

Do you have the slow query logging turned on? Does it contain slow queries?