Mysql optimization for moodle my.cnf empty

goober21

Registered
Feb 20, 2012
2
0
51
cPanel Access Level
Root Administrator
I'm in the process of optimizing my moodle installation/server it has about 7 separate sites of varying usage. Reports and other tasks seem to be timing. looked at my.cnf and noticed it was empty. /etc/my.cnf

I did a locate and all others i've found haven't had the standard or much info either.

server specs:
12gb memory
x2 xeon 3.47 ghz
raid 5 10k drives

I realize my mysqltuner is only for a couple days. i'll certainly post another at the end of this week if it helps get a better baseline of what kind of config would make sense to better utilize the servers possible performance from a sql standpoint.
thanks much!


here are my mysqltuner.pl results.
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> 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.92-community
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 1831)
[--] Data in InnoDB tables: 4M (Tables: 33)
[!!] Total fragmented tables: 231

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 20h 40m 21s (4M q [14.949 qps], 206K conn, TX: 13B, RX: 536M)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (2% of installed RAM)
[OK] Slow queries: 0% (11/4M)
[OK] Highest usage of available connections: 20% (20/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.0G
[OK] Key buffer hit rate: 98.9% (221M cached / 2M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 192K sorts)
[!!] Joins performed without indexes: 5120
[!!] Temporary tables created on disk: 45% (475K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 173K opened)
[OK] Open file limit used: 12% (124/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 4.6M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
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)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
Well,

First start off with some reasonable settings, and than re-run another report after 24 hours of running. Just make sure to restart mysql after making changes to the my.cnf

Try something like
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

max_connections = 100
max_allowed_packet = 16M

### Query cache settings ###
query_cache_type=1
query_cache_limit=4M
query_cache_size=64M

### tmp table cache settings ###
tmp_table_size = 64M
max_heap_table_size = 64M

### Buffer Size Settings ###
sort_buffer_size = 256K
read_buffer_size = 256K
join_buffer_size = 256K

### Key Buffer Settings ###
key_buffer_size = 1224M

thread_cache_size = 16

### Table cache ###
table_cache = 8192

### Log slow queries ###
slow-query-log = 1
slow-query-log-file = /var/log/mysql-slow-queries.log #Make sure this file exists and is owned by mysql.
long_query_time = 2

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

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
 
Last edited:

goober21

Registered
Feb 20, 2012
2
0
51
cPanel Access Level
Root Administrator
I inputted the config you provided exactly into /etc/my.cnf and restarted mysql service.. wouldn't come up.

Sorry for my lack of knowledge on this front. Would there be a place i can check to see why it didnt' start?
I did create the /var/log/mysql-slow-queries.log #Make sure this file exists and is owned by mysql.
and chown mysql:mysql to confirm it is owned by mysql user.

thanks so much for your help with this. so strange the config files are all empty i have 3 cpanel servers on centos and all have the empty config.
 

Infopro

Well-Known Member
May 20, 2003
17,076
524
613
Pennsylvania
cPanel Access Level
Root Administrator
Twitter
so strange the config files are all empty i have 3 cpanel servers on centos and all have the empty config.
You could use one of the Preconfigured Option Files as needed for a base configuration.

MySQL provides a number of preconfigured option files that can be used as a basis for tuning the MySQL server. Look for files such as my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which are sample option files for small, medium, large, and very large systems.
...
You can rename a copy of a sample file and place it in the appropriate location for use as a base configuration file.
These are not set or configured by the system, it's up to the Server Administrator.
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
They start out empty mysql has default settings. So it's not so unusual. :)

are you getting any errors?

Make sure that this line

slow-query-log-file = /var/log/mysql-slow-queries.log #Make sure this file exists and is owned by mysql.

is 1 line and not two lines.

Shawn