Hello

I have created a standalone mysql server, and would be great if someone could take a look at my config file and tell me if Im way off or not.

The server does not run anything like apache or nginx, just mysql-server-5.5 . The server is used against one django site on another linode.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = mysql

key_buffer              = 256M #Old setting 16M
max_allowed_packet      = 1M
thread_stack            = 64K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 150
table_cache            = 800 #Old setting: 32
thread_concurrency     = 16


sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M


query_cache_limit       = 250M
query_cache_size        = 4M

log_error = /var/log/mysql/error.log

log_slow_queries       = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

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

!includedir /etc/mysql/conf.d/

Here is my mysqltuner log, but It seem like the mysql need to run a few days, because when I restart mysql the log seem fine, but now it does not so much.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 838K (Tables: 16)
[--] Data in InnoDB tables: 3M (Tables: 47)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 47

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8h 33m 13s (141K q [4.588 qps], 2K conn, TX: 150M, RX: 35M)
[--] Reads / Writes: 25% / 75%
[--] Total buffers: 420.0M global + 6.2M per thread (150 max threads)
[!!] Maximum possible memory usage: 1.3G (279% of installed RAM)
[OK] Slow queries: 0% (0/141K)
[OK] Highest usage of available connections: 38% (58/150)
[OK] Key buffer size / total MyISAM indexes: 256.0M/2.6M
[!!] Key buffer hit rate: 46.8% (9K cached / 5K reads)
[OK] Query cache efficiency: 93.2% (77K cached / 83K selects)
[!!] Query cache prunes per day: 2407
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[OK] Temporary tables created on disk: 18% (619 on disk / 3K total)
[OK] Thread cache hit rate: 82% (350 created / 2K connections)
[!!] Table cache hit rate: 12% (107 open / 838 opened)
[OK] Open file limit used: 4% (80/1K)
[OK] Table locks acquired immediately: 100% (22K immediate / 22K locks)
[OK] InnoDB data size / buffer pool: 3.3M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    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 ***
    query_cache_size (> 4M)
    table_cache (> 800)

1) If you have any tables used by Django in MyISAM for some reason, move them to InnoDB. MyISAM is bad.

2) Return key_buffer to its original value. It's only used by MyISAM. You have it set to 256 MB for some reason.

3) Set innodb_buffer_pool_size to a smaller value. Right now, it needs to be 4 MB, given your data. If your database plans to grow, you'll want to keep this a little larger than your database (but no larger than your memory!).

4) 90% of the tuning guides published are for MyISAM, which is the old database engine that should not be used for your applications' databases, and so they won't do anything but eat your RAM. Good news is that InnoDB tuning is pretty simple: see #3 above.

Get your maximum RAM usage down through any means necessary. Note that you had, at some point, 58 connections to the database server, each of which will take 6.2 MB (for a total of ~360 MB). Did all 58 of those connections need to exist, or were some just sitting idle? Can you use connections from your application servers more efficiently, and drop max_connections to decrease maximum memory usage? If so, do it.

If, however, you've got 58 active workers pumping data to users, you might need more RAM for your database server to keep up with it. (Or ditch this whole MySQL thing and use PostgreSQL, which Django prefers anyway...)

_________________

/* TODO: need to add signature to posts */

Great! Thank you!

I'll use these suggestions for a quick fix for mysql and then later convert to PostreSQL.