speeding up mysql queries

[GregT]

# The MySQL database server configuration file.

#

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients

# It has been reported that passwords should be enclosed with ticks/quotes

# escpecially if they contain "#" chars...

# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

[client]

port		 = 23456

socket		 = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]

socket		 = /var/run/mysqld/mysqld.sock

nice		 = 0


[mysqld]

# * Basic Settings

user		 = mysql

pid-file	 = /var/run/mysqld/mysqld.pid

socket		 = /var/run/mysqld/mysqld.sock

port		 = 23456

basedir		 = /usr

datadir		 = /home/webraid/mysql

tmpdir		 = /tmp

language	 = /usr/share/mysql/english

skip-external-locking

bind-address		 = 127.0.0.1

skip-name-resolve

# * Fine Tuning

key_buffer			 = 16M

key_buffer_size		 = 128M

max_allowed_packet	 = 16M

thread_stack		 = 192K

thread_cache_size	 = 8

myisam-recover		 = BACKUP

max_connections		 = 100

table_cache			 = 250

#thread_concurrency	 = 10

query_cache_type	 = 1

query_cache_limit	 = 1M

query_cache_size	 = 32M

expire_logs_days	 = 10

max_binlog_size		 = 100M


[mysqldump]

quick

quote-names

max_allowed_packet	 = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]

key_buffer			 = 16M

#

# * IMPORTANT: Additional settings that can override those from this file!

# The files must end with '.cnf', otherwise they'll be ignored.

#

!includedir /etc/mysql/conf.d/

This is a dedicated database machine, IPB is probably the most active thing so it will be using the most memory & cpu power

it has 8GB of memory, can up to 16 for like $40

any tips for things? I'm also curious as what most of these do, I've read around but a lot of poor descriptions as to why something should be set to whatever.. or no description at all

[Grumpy]

Having sql settings only does not give a full picture as to what's recommended. You need to provide a LOT more stuff for a detailed recommendation to exist. Rather, better to hire someone for it.

But, to make things simple, start off with something like this.

key_buffer = 128M
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M

and then follow mysql tuner's advice (see sticky).
do note that when following mysqltuner, that if you have a lot of IP.C, you will ALWAYS have a high temporary tables on disk. You will have to find out for yourself the point where IP.B is hitting tables on disk vs IP.C is hitting. (You'll reach a plateau you cannot pass)


Having more ram will help even if you don't optimize your mysql specifically for it because there's automatic disk caching by the OS. But this assumes that your database(s) is that big to begin with.

[Gary.]

Hello,

Advise only and you do not have to do this, Plus always backup the working mysql but you need to start with basic functions and ones that can help you. It's probably 100% impossible that we can fix any issues as for myself to give 100% results I would need to see queries, Times and such for any optimization.

You could start by adding and changing the following values.

query_cache_limit=1M

query_cache_size=96M

query_cache_type=1

max_connections=150

interactive_timeout=100

wait_timeout=100

connect_timeout=100

thread_cache_size=16

key_buffer=256M

join_buffer=2M

max_allowed_packet=16M

table_cache=512

tmp_table_size=96M

myisam_sort_buffer_size=96M

max_heap_table_size=96M

Again, Its 100% impossible to give perfect results without looking at the box.

Regards.

Gary

[Elly]

Settings will be shooting in the dark. It's dependent on knowing what your slow queries are, cache ratio and database size etc. However I don't see any InnoDB settings. Are your tables MyISAM? Best to get someone to take a look at your server :)

[GregT]

All my queries are less than a second, I'm just trying to optimize it so it's running perfetly

I'm not sure what information you need? I'm running ip.board it has about 150k posts and 10-20 members active at random times

Having sql settings only does not give a full picture as to what's recommended. You need to provide a LOT more stuff for a detailed recommendation to exist. Rather, better to hire someone for it.

But, to make things simple, start off with something like this.

key_buffer = 128M

table_cache = 8192

max_heap_table_size = 256M

query_cache_limit = 4M

query_cache_size = 512M

and then follow mysql tuner's advice (see sticky).

do note that when following mysqltuner, that if you have a lot of IP.C, you will ALWAYS have a high temporary tables on disk. You will have to find out for yourself the point where IP.B is hitting tables on disk vs IP.C is hitting. (You'll reach a plateau you cannot pass)

Having more ram will help even if you don't optimize your mysql specifically for it because there's automatic disk caching by the OS. But this assumes that your database(s) is that big to begin with.

yea my memory isn't getting close to cap. I was reading about it and decided to increase the cache so it gets used more.. I was seeing the disk being read quite a bit, so I moved that table_cache up so it can load all of the tables
why is yours at 8192? I don't have anywhere close to even 200 tables O_O

[Grumpy]

table cache isn't a count of how many tables you have.

from mysql docs

table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

In other words, when ever you make a query, mysql stores the table in its cache so that it can operate on it. Each operation, since it is a different action gets it own separate instance. So, when you have a query that joins 10 tables, you need to access 10 tables. If you do that 100 times simultaneously, you need room for 1000 table cache. While the docs does say the maximum, that plans for the worst case scenario. What are the chances the most joined queries are going to run all the time? How many concurrent connections do you usually have / at peak? To what amount of standard deviation are you willing to sacrifice and dedicate your resources to support it? All these factors in which are a derivative of how many people you have concurrently and the profile of how your users interact with your website. You can see how data is lacking to even suggest one value in mysql.

Stats like "top", "iostat", "sar", external active connections, active connections to mysql, and mysql stats (which mysqltuner reads and makes recs.) at various intervals in time are only tip of the stats for optimizing mysql.

But once again, to make things simple, you can try to bootstrap starting values from one I posted (or Gary's) and then follow the advice from mysqltuner -- while reading what each one does. It occasionally does make dumb suggestions.

[GregT]

Which is exactly what I said, the only part you added was the same tables can be added twice? In other words, if I query table A and B, and then I query A and C, A will then be cached twice with its +B output along with its +C output?

In other words, filling my memory with table cache would be the best start to speeding the system up to maximum ram/cpu speed while hoping the hard drives never get touched .. because if it gets overloaded, it will drop out of mem and a different table would be added.

If my first statement is true, then this would be all the info I need to maximize speed for mysql myself, thanks. I have also made mysql's "/tmp disk space" as a ram file system

[Grumpy]

Which is exactly what I said, the only part you added was the same tables can be added twice? In other words, if I query table A and B, and then I query A and C, A will then be cached twice with its +B output along with its +C output?

I kind of fail to see where you said that before...
The simple answer to your question is "yes", but is open to misinterpretation.
If I query table A and then query table A again, it's possible that A is loaded twice in table cache.

In other words, filling my memory with table cache would be the best start to speeding the system up to maximum ram/cpu speed while hoping the hard drives never get touched .. because if it gets overloaded, it will drop out of mem and a different table would be added.

I'm not sure how you came to the conclusion that maximizing table cache would be the best "start"... Are you having issues with table caches?
Also, the definition of "overloaded" is when it goes past your settings. Not past your ramdisk size. If your ramdisk is miscalculated to be smaller than your mysql settings, it will crash once it reaches that point.

If my first statement is true, then this would be all the info I need to maximize speed for mysql myself, thanks. I have also made mysql's "/tmp disk space" as a ram file system

There's more to optimization than one variable...