MySQL Question

[JnascECSI]

Hope this is alright to ask in here and if not please move to a appropriate section.

Anyways lately MySQL has been hanging up lately and i'm trying to figure out what would be the best way to tweak it. We currently have
36 databases running on this server most are Joomla,phpBB,smf. Oscommerce and a couple custom one's. Not being a MySQl guru i figure the DB needs some tweaking but from my googling around it seems that most information is for guru's.

Anyone have any tips or recommendations on tweaking Mysql?

This is my current my.cnf seems pretty bare to be a config file.

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[Highland]

Believe it or not, MySQL really needs quite a bit of tuning out of the box (for instance query cache is on but set to cache 0 queries). A major suggestion I have is to go get mysqltuner. It analyzes your current SQL usage and recommends config adjustments as needed.

Here's how mine looks for comparison. Your milage may vary, tho, so mysqltuner is the best place to start.

Code: Select all

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
wait_timeout=60
max_user_connections=500
max_connections=500
query_cache_type=1
query_cache_limit=1M
query_cache_size=64M
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=5
thread_cache_size=6
thread_concurrency = 4
skip-bdb
join_buffer_size=1M
table_cache=96
tmp_table_size=64M

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[scott]

mysqltuner is in the atomic repo, and it is plesk aware. You dont even have to configure anything to use it. Just install:

yum install mysqltuner

and run it

mysqltuner

[JnascECSI]

Thanks guys,

Installed it fine and this is my output, i guess to make the recommended changes this is done via the my.cnf correct?

>> MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 220M (Tables: 3508)
[--] Data in InnoDB tables: 8M (Tables: 126)
[--] Data in MEMORY tables: 0B (Tables: 5)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 128

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 16h 53m 0s (2M q [8.833 qps], 56K conn, TX: 2B, RX: 450M)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 302.7M (8% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 62% (62/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/77.8M
[OK] Key buffer hit rate: 96.7% (21M cached / 702K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 120K sorts)
[!!] Joins performed without indexes: 5636
[OK] Temporary tables created on disk: 19% (24K on disk / 124K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 233K opened)
[OK] Open file limit used: 79% (818/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 8.0M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_cache (> 64)
innodb_buffer_pool_size (>= 8M)

[scott]

yeah that query cache is a big big one, you'll see a difference with that right away

[JnascECSI]

Not sure if this is how i should do it, does this look correct?


[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_size=64M
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=5
thread_cache_size=6
skip-bdb
join_buffer_size=1M
table_cache=96
tmp_table_size=64M


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[Highland]

Looks good to me. Save it and restart mysql

Code: Select all

#service mysqld restart

Note that mysqltuner works best after at least 24 hours of normal use so you'll want to tweak it once a day and then restart and let it run.

mysqltuner is in the atomic repo, and it is plesk aware. You dont even have to configure anything to use it.

Learn something new every day

[JnascECSI]

Thanks for the Help Highland,
I'll wait til tommorrow and see what happens and run it. Hopefully those changes stop the DB crashing every once and awhile.

[JnascECSI]

Well i let it go for a week and this is my output as of this morning, the mysql crashing has stopped so that is a good thing. Only thing is now i'm not sure how much i should boost up the Table Cache or the join_buffer_size? Seems that i've almost got it but looking for some recommendations on how much more could i boost the table cache and join_buffer_size.

Am i wrong or does the table cache seem to be way to low for what it is seeing? 128 is setting but over 70k open

[root@inet3170 ~]# mysqltuner

>> MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 224M (Tables: 3524)
[--] Data in InnoDB tables: 7M (Tables: 126)
[--] Data in MEMORY tables: 0B (Tables: 5)
[!!] Total fragmented tables: 118

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 50m 50s (1M q [12.266 qps], 26K conn, TX: 962M, RX: 232M)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 266.0M global + 3.6M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.0G (57% of installed RAM)
[OK] Slow queries: 0% (2/1M)
[OK] Highest usage of available connections: 5% (26/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/79.4M
[OK] Key buffer hit rate: 97.7% (7M cached / 165K reads)
[OK] Query cache efficiency: 74.4% (648K cached / 871K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 28K sorts)
[!!] Joins performed without indexes: 2061
[OK] Temporary tables created on disk: 18% (9K on disk / 52K total)
[OK] Thread cache hit rate: 98% (379 created / 26K connections)
[!!] Table cache hit rate: 0% (128 open / 70K opened)
[OK] Open file limit used: 9% (239/2K)
[OK] Table locks acquired immediately: 99% (533K immediate / 533K locks)
[OK] InnoDB data size / buffer pool: 8.0M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)
table_cache (> 128)

[root@inet3170 ~]#

[Kalimari]

[!!] Joins performed without indexes: 2061

(I think) mysqltuner will always recommend increasing the join_buffer_size so long as you are using joins on tables without indexes (You should probably begin by looking at the custom MySQL applications - investigate any queries that use joins).

[!!] Table cache hit rate: 0%

Using moderate/heavy MySQL on a dozen websites with similar values as yours, I use:
table_cache = 2500 (Increase table_cache gradually to avoid file descriptor limits)
and:
open_files_limit = table_cache x 2 + max_connections

Obviously tweak to your own requirements.

[hostingguy]

mysqltuner is in the atomic repo, and it is plesk aware. You dont even have to configure anything to use it. Just install:

yum install mysqltuner

and run it

mysqltuner

is there a way to run it and have it automatically make the recomended changes for you ?

Edit:
Additionally I ran it and found this funny

General recommendations:
When making adjustments, make tmp_table_size/max_heap_table_size equal
Variables to adjust:
tmp_table_size (> 35M)
max_heap_table_size (> 16M)

Nice to see it didnt take its own advice :p


Also seemed to throw a lot of errors

Argument "" isn't numeric in numeric gt (>) at /usr/bin/mysqltuner line 495, <>
line 2 (#1)
(W numeric) The indicated string was fed as an argument to an operator
that expected a numeric value instead. If you're fortunate the message
will identify which operator was so unfortunate.

[OK] Total fragmented tables:
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

-------- Performance Metrics -------------------------------------------------
[--] Up for: 54d 11h 58m 14s (978M q [207.851 qps], 24M conn, TX: 2B, RX: 3B)
[--] Reads / Writes: 34% / 66%
[--] Total buffers: 8.1G global + 952.0K per thread (1910 max threads)
Use of uninitialized value in string eq at /usr/bin/mysqltuner line 647, <>
line 2 (#2)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.

To help you figure out what was undefined, perl tells you what operation
you used the undefined value in. Note, however, that perl optimizes your
program and the operation displayed in the warning may not necessarily
appear literally in your program. For example, "that $foo" is
usually optimized into "that " . $foo, and the warning will refer to
the concatenation (.) operator, even though there is no . in your
program.

[OK] Maximum possible memory usage: 9.9G (30% of installed RAM)
[OK] Slow queries: 0% (32K/978M)
[!!] Highest connection usage: 100% (1911/1910)
Argument "" isn't numeric in numeric eq (==) at /usr/bin/mysqltuner line 679,
<> line 2 (#1)
[!!] None of your MyISAM tables are indexed - add indexes immediately
[OK] Query cache efficiency: 73.2% (400M cached / 547M selects)
[!!] Query cache prunes per day: 59735
[OK] Sorts requiring temporary tables: 1% (429K temp sorts / 22M sorts)
[!!] Joins performed without indexes: 209590
[!!] Temporary tables created on disk: 44% (29M on disk / 66M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 48M opened)
[OK] Open file limit used: 6% (128/2K)
[!!] Table locks acquired immediately: 77%