mysqltuner says that 355 out of 355 of my tables are fragmented.
I optimized all tables via `mysqlcheck --optimize -A -user=mdluser -p`
mysqltuner still says 355 out of 355 are fragmented.
I checked for errors via `mysqlcheck -c moodledb -u mdluser -p` and no errors were found.
What am I doing wrong? See attached.
Thanks in advance.
While Moodle can use MySQL, this issue isn't really a code question, but more a DB Admin question. Might want to consult MySQL official docs. http://dev.mysql.com/doc/ or this one:
http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb.html or this one:
http://dev.mysql.com/doc/refman/5.6/en/mysqlcheck.html
However, will answer briefly here (am not a DB admin) ... tuner is not run against a certain database. mysqlcheck doesn't work with InnoDB tables. However, if you noticed, mysqlcheck is trying to recreate + analyze.
'spirit of sharing', Ken
Thanks for thoughts.
I thought -A was for all databases.
According to http://dba.stackexchange.com/questions/28876/mysqlcheck-to-optimize-innodb-tables-performance-scheduling it's likely unnecessary to optimize an innodb table. Wish I knew... Should we have used a different database format?
Yes, -A is for all DB's, but is that a swtch for tuner (the mysqltuner.pl script)?
I think tuner is seeing other DB's.
No, innodb is required going forward with Moodle - so don't change tables
This might give you some other tools for optimizing MySQL:
http://www.askapache.com/mysql/performance-tuning-mysql.html
like this one:
https://launchpad.net/mysql-tuning-primer
'spirit of sharing' ... and wanna be [NOT] db admin, Ken
Hmm, maybe I need to consider upgrading to mysql 5.6.20 from 5.5.29 -- any known Moodle issues with upgrading? Might be worth a try to see if it boosts performance. Switching to the wiki map takes 3-4 seconds on an intranet connection so we can still use some improvements...
Hi Nathan,
Before embarking into that upgrade (from which I doubt you'll notice any major improvements) I would recommend you to check the Moodel performance documentation page:
Thanks for the tips. Good to know that upgrading mysql won't probably change much. I have already looked at the performance links you mentioned and tried to understand what I could. That's why I was trying to optimize, because the docs say we need to optimize after upgrading, I was seeing 355 fragmented tables listed, and we upgraded to 2.6.4+
The reason I'm trying to optimize is we were having huge lags when employees were doing quizzes such that they lost connection. After changing my.cnf settings based on mysqltuner, I haven't heard any complaints, but we are far from "instant" page loads (sometimes 3-4 second lags between pages which seems quite slow for an intranet site).
I have some general training in writing queries but no experience (until now) with Ubuntu, performance settings, or server administration, so getting thrown into Ubuntu intranet server admin has been a challenge.
Hi Nathan,
The 355 tables reported must be all of the Moodle tables and some of another database you may have.
What settings did you change? Yes, the time could be relatively slow for an Intranet, but of course there are a lof of factors to consider, starting with the network itself, etc. I'd assume you have started your optimizations there.
Regarding the MySQL 5.6 upgrade, check the following:
http://www.mysqlperformanceblog.com/2013/02/18/is-mysql-5-6-slower-than-mysql-5-5/
Yes, it's a year and a half old article (2013/02/18) and so latest releases might have improved, but I think you would go into a lot of work only to get a minimal positive difference, if at all.
Excluding all the related hardware, I think it would be best to start with the web server and the PHP settings (e.g. memory, time). Also, If you haven't, you could consider using a PHP accelerator; you can read a bit about my own experience (very simple tests in my own computer; nothing critical) here:
https://moodle.org/mod/forum/discuss.php?d=233716#p1016987
Many new things to learn, that's for sure!
Cheers!
my.cnf Settings changes so far:
php.ini changes
Installed Workbench to be better able to monitor load etc. Worked a while at trying to get mod_status but never managed to get the permissions settings right for access, but workbench probably gives me all that info anyway.
Installed XHProf and ran some tests but they don't mean a whole lot to me. Not too many red boxes
Considered reducing the number of temporary tables saved to disk : tmp_table_size value. Our value is 6.6% which is over the recommended 5%.
percona recommends (may try after research...)
FYI, It's only a single processor virtual machine... should I ask if we can change that?
Hi Nathan,
I guess that the 2 -> 4 GB refers to physical memory and that the database is not on a dedicate database server. Maybe you could assign a machine to act as a database server. One of the best rules is to throw into the solution as much memory and speed (processor/disk) as possible. So any upgrade in hardware will certainly be good (regarding the single processor VM).
I'd say the biggest factor in your results was your increasing the innodb_buffer_pool_size value.
Maybe you could go through the performance documentation pages with a friend that has experience in this areas.
Yes, I stuck the RAM note in the wrong spot. The server is dedicated to Moodle, but includes both the Moodle data files and database files. With a small database like we have, you still think it would be worth splitting up? I noticed one time after our settings changes where the Load went over 1.0 but that generally isn't the case. This morning it's hanging around 0.2 to 0.5. What utility do you recommend that would enable me to see a graph of load over time? Strangely, that number doesn't correspond to anything in our vSphere performance logs.
I'm wondering if we need to just get help from a professional to fine-tune our server. How long do you think it might take to do a tune-up that would make a difference, and any suggestions on a vendor?
Hmmmm the link provided was about Percona MySQL ... not same as the freebie am using. ;)
A tuner run ...
[OK] Currently running supported MySQL version 5.5.37
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 48M (Tables: 1082)
[--] Data in InnoDB tables: 113M (Tables: 2615)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 2658
Fragmented tables doesn't add up, does it?
MySQL upgraded recently - right? Anyone know of Ubuntu does an automatic
mysql_upgrade? CentOS does not so after upgrading MySQL have to run it.
That does run mysqlcheck (with default connection arguments)
Have discovered a --force switch exist for mysql_upgrade and running it does all DB's.
'spirit of sharing', Ken
(still don't wanna be DB admin!)
Hello Ken, AKA "don't wanna be DB admin"
Most probably the 2658 reported tables are all of the InnoDB plus 43 of the MyISAM.
Now just have to figure out what DB and which tables in those DB's, huh? Learning something didn't know before:
"Do not convert MySQL system tables in the mysql
database (such as user or
host) to the InnoDB type.
This is an unsupported operation. The system tables must always be
of the MyISAM type."
http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html
'spirit of sharing', Ken
Hi Ken,
Indeed! One shouldn't alter tables from MySQL own databases (i.e. information_schema, mysql --and performance_schema) as they keep info from user databases, etc.
To get the info you asked for run the following SQL statement:
SELECT table_schema,
table_name,
engine,
data_free
FROM information_schema.tables
WHERE table_schema NOT IN ( 'information_schema', 'mysql' )
AND data_free > 0
AND NOT engine = 'MEMORY';
Many thanks!
'spirit of receiving!!!', Ken
You're very welcome!
5.5.x to 5.6.x ... well, here's one research page - back in 2013/02
Would certainly hope that such an upgrade would result in better performance ... but that may not always be the case. Much depends upon code also. There are some items (like admin login) that simply have more data involved. Quizzes/test have always hit DB big time. Think I've seen folks in these forums recommend reducing the number of questions per page on a quiz/test.
As with code, if you do upgrade the DB, backup what you have first! ;)
If you do give it a go, please report back your findings!
'spirit of sharing', Ken
Thanks for the input, Ken.
Preliminary reports are that everything is working much better after my tweaks, so I maybe won't worry about upgrading. I think based on this I should get rid of the checklist if there are issues in the future... That was a test during checking a quiz answer, so it appears the checklist feature is taking down performance while taking a quiz.
Hello Nathan
You are working with a 64 bit machine and worring about 159 MB of data? As mentioned in the stackexchange thread you referenced, you really needn't optimize (worry) when using the InnoDB engine (as compared to MyISAM), specially with such a small database.
At first instance this whole optimizing idea may seem simple enough (first diagnose and then correct), however, [InnoDB] databases are quite another beast as they do not work like a hard disk; as a matter of fact, and I won't go into much detail about this, if you have all your data stored in the ibdata1 file, optimizing will actually do the opposite, that is, it will make the file bigger. If you are actually interested I would recommend you to start reading as much as you can about MySQL and InnoDB --just start with simple concepts.
Users are reporting more disconnects today. Any suggestions for determining if this is the fault of our network connection into the server vs too much load on server? If the load isn't over 1.0 and we're getting disconnects, does that point to another issue?
What does your apache config look like?
What is your normal user load, active clients?
Since you state moodledata is on the MySQL Server how is that being served up? NFS?
Where are you storing your session information?
Any of the above configured incorrectly with low resources and a high load can cause disconnects either on the web server or the sql server.
JASON
I'm getting blocked, perhaps due to trying to include config files? Let's try again... I'll try to attach config files.
We have up to 300 users, yesterday a log of all events over 24 hours produced approx 1340 results.
No idea how moodledate is being served. man nfs yields "no manual entry for nfs"
We don't have the store session info in database option checked.
I noticed this statement: "If table_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable." but I don't know how to tell if we're running out of file descriptors. I do have some "file does not exist: ..._vti_bin" errors in apache error log.
Will also attach sqltuner and qcache results in next post as I think that shows we still have config issues also.
I'm showing 41% memory usage and 7% swap usage. Don't know why we're starting to see swap usage now... hadn't had that problem until very recently. 139 processes, 55.5% HD usage.
Thanks in advance for any tips you can offer.
First, suggest that this last posting be one of it's own ... think we're off into other issues from optimization and fragmentation. Maybe forum person could do that.
Yes, more swap space usage is not a good thing ... one way to find out what is using the SWAP space is via top command. On CentOS (might be different if another OS), launch top, then press the 'F' key, followed by 'p'. That should 're-sort' the top output to show what is using the swap space ... and how much. Example of (server has 16+gig memory total):
Mem: 16429128k total, 15676408k used, 752720k free, 693700k buffers
Swap: 18481144k total, 0k used, 18481144k free, 12293700k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ SWAP COMMAND
15151 mysql 15 0 14.3g 1.0g 5584 S 0.0 6.7 0:21.84 13g mysqld
Moodle data might not be on an NFS.
What does 'df' command show?
If a remote file system is being used for either backups or moodledata the output of df might show something like this (the example below shows a Windows share that is being used for backups - not for Moodledata):
[root@serverhost ktask]# df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda6 898976160 687299804 166011028 81% /
tmpfs 32986884 0 32986884 0% /dev/shm
/dev/sda1 1032088 91848 887812 10% /boot
/dev/sda2 103212320 192116 97777324 1% /sql
/dev/sda3 51606140 1088564 47896136 3% /var/log
\\nisddiscovery1\DDrive
9437050876 6750725964 2686324912 72% /mnt/win
Might also check the settings in your MySQL for max connections.
max_connections
mysql> show variables like "max_connections";
or
fgrep max_connections /etc/my.cnf
IF your mysql config file is in /etc/ and named my.cnf.
'spirit of sharing', Ken
Yeah, Wish I could change the thread name but can't.
max_connections = 151
Maybe I need a server restart after all my config changes...?
151 is default for MySQL. Despite other advice, think I'd increase it anyway. And, yes, just like php changes requiring a restart of Apache, any change to my.cnf would require a restart of the mysqld service.
Noticed by the top output that mysql-workbench is eating up the memory. mysqld didn't even show in the list?!!! Using workbench you could be fooling yourself. Ok, fine, use workbench when you are working with MySQL, but shut it down when not. It shows workbench has been running for days!
And another thing with an under-powerd (memory wise) ... the GUI desktop just gets in the way ... and it does eat up memory as well. Notice XOrg is second in the list of top.
One can have the server boot at command line and then, if one ever needs it, one could launch the GUI desktop IF logged on in a terminal session ON the server. BTW, I normally use ssh shells remotely from a workstation. That uses little resources so when I look at top, etc. am seeing things as 'clients' might see them via browser.
On a web server using a resource hungry app like Moodle 2, one needs to shutdown/not use anything except what is necessary ... ie, anything that takes away from PHP, MySQL, or Apache ... the purpose of the server. Of course one leaves alone those things related to the functioning of file system, network, etc..
Yes, increase the memory via VM. Restart the guest OS. Ubuntu should pick up on the new memory and begin to manage memory differently and all by itself.
However, the size of the swap will not increase automatically - but, if you give it more memory and configure apache/mysqld to use it appropriately, one should NOT be needing SWAP space.
Good advice has already been given you concerning apache.
When it comes to a server that is to be the filestorage, the DB, and the web server for Moodle it's a balancing act between the config of the DB and Apache ... both need more than defaults ... even with small Moodle sites due to some activities (like test/quiz) being resource hungry.
'spirit of sharing', Ken
php .ini info
engine = On
short_open_tag = On
asp_tags = Off
precision = 14
y2k_compliance = On
output_buffering = 4096
zlib.output_compression = Off
implicit_flush = Off
unserialize_callback_func =
serialize_precision = 17
allow_call_time_pass_reference = Off
safe_mode = Off
safe_mode_gid = Off
safe_mode_include_dir =
safe_mode_exec_dir =
safe_mode_allowed_env_vars = PHP_
safe_mode_protected_env_vars = LD_LIBRARY_PATH
disable_functions = pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,
disable_classes =
zend.enable_gc = On
expose_php = On
max_execution_time = 600
max_input_time = 60
memory_limit = 128M
error_reporting = E_ALL & ~E_DEPRECATED
display_errors = Off
display_startup_errors = Off
log_errors = On
log_errors_max_len = 1024
ignore_repeated_errors = Off
ignore_repeated_source = Off
report_memleaks = On
track_errors = Off
html_errors = Off
variables_order = "GPCS"
request_order = "GP"
register_globals = Off
register_long_arrays = Off
register_argc_argv = Off
auto_globals_jit = On
post_max_size = 110M
magic_quotes_gpc = Off
magic_quotes_runtime = Off
magic_quotes_sybase = Off
auto_prepend_file =
auto_append_file =
default_mimetype = "text/html"
doc_root =
user_dir =
enable_dl = Off
file_uploads = On
upload_max_filesize = 110M
max_file_uploads = 40
allow_url_fopen = On
allow_url_include = Off
default_socket_timeout = 60
[Date]
[filter]
[iconv]
[intl]
[sqlite]
[sqlite3]
[Pcre]
[Pdo]
[Pdo_mysql]
pdo_mysql.cache_size = 2000
pdo_mysql.default_socket=
[Phar]
[Syslog]
define_syslog_variables = Off
[mail function]
SMTP = localhost
smtp_port = 25
mail.add_x_header = On
[SQL]
sql.safe_mode = Off
[ODBC]
odbc.allow_persistent = On
odbc.check_persistent = On
odbc.max_persistent = -1
odbc.max_links = -1
odbc.defaultlrl = 4096
odbc.defaultbinmode = 1
[Interbase]
ibase.allow_persistent = 1
ibase.max_persistent = -1
ibase.max_links = -1
ibase.timestampformat = "%Y-%m-%d %H:%M:%S"
ibase.dateformat = "%Y-%m-%d"
ibase.timeformat = "%H:%M:%S"
[MySQL]
mysql.allow_local_infile = On
mysql.allow_persistent = On
mysql.cache_size = 2000
mysql.max_persistent = -1
mysql.max_links = -1
mysql.default_port =
mysql.default_socket =
mysql.default_host =
mysql.default_user =
mysql.default_password =
mysql.connect_timeout = 60
mysql.trace_mode = Off
[MySQLi]
mysqli.max_persistent = -1
mysqli.allow_persistent = On
mysqli.max_links = -1
mysqli.cache_size = 2000
mysqli.default_port = 3306
mysqli.default_socket =
mysqli.default_host =
mysqli.default_user =
mysqli.default_pw =
mysqli.reconnect = Off
[mysqlnd]
mysqlnd.collect_statistics = On
mysqlnd.collect_memory_statistics = Off
[OCI8]
[PostgreSQL]
pgsql.allow_persistent = On
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = -1
pgsql.max_links = -1
pgsql.ignore_notice = 0
pgsql.log_notice = 0
[Sybase-CT]
sybct.allow_persistent = On
sybct.max_persistent = -1
sybct.max_links = -1
sybct.min_server_severity = 10
sybct.min_client_severity = 10
[bcmath]
bcmath.scale = 0
[browscap]
[Session]
session.save_handler = files
session.use_cookies = 1
session.use_only_cookies = 1
session.name = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.cookie_httponly =
session.serialize_handler = php
session.gc_probability = 0
session.gc_divisor = 1000
session.gc_maxlifetime = 1440
session.bug_compat_42 = Off
session.bug_compat_warn = Off
session.referer_check =
session.entropy_length = 0
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
session.hash_function = 0
session.hash_bits_per_character = 5
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=fakeentry"
[MSSQL]
mssql.allow_persistent = On
mssql.max_persistent = -1
mssql.max_links = -1
mssql.min_error_severity = 10
mssql.min_message_severity = 10
mssql.compatability_mode = Off
mssql.secure_connection = Off
[Assertion]
[COM]
[mbstring]
[gd]
[exif]
[Tidy]
tidy.clean_output = Off
[soap]
soap.wsdl_cache_enabled=1
soap.wsdl_cache_dir="/tmp"
soap.wsdl_cache_ttl=86400
soap.wsdl_cache_limit = 5
[sysvshm]
[ldap]
ldap.max_links = -1
[mcrypt]
[dba]
[xsl]
my. cnf info
[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 = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8
join_buffer_size = 256K
table_open_cache = 450
query_cache_min_res_unit = 2K
myisam-recover = BACKUP
query_cache_limit = 2M
query_cache_size = 32M
log_error = /var/log/mysql/error.log
log_slow_queries = /var/log/mysql/mysql-slow.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size=1G
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
I am thinking this is a single server then? Confirm, you have apache and mysql on the same server? I was assuming when you said moodledata you just meant the actual moodledata and not the moodle application.
Assuming you have a single box, 4Gb of RAM, Single vCPU.
Since this is a single server do you have APC installed? If not,
sudo apt-get install php-apc
You would need to restart apache after, sudo service apache2 restart.
That is it for APC, nothing else needs to be configured and all of the PHP scripts will then be cached in memory. We could allocate more memory to APC but you are already running low on that so I will not post on how to do that.
I am going to take a guess and just assume for now that you are running out of resources with 300 active clients on a single server, 4GB of RAM. Calculating that maybe of those 300 you had an average of 25-50 concurrent. Apache would need at the least 2-5GB of RAM (being 2 for the 25 concurrent and 5 for the 50 concurrent) and lets just say the total of MySQLs memory is around 2GB which is including the innodb buffer, maybe a little less. Your system would be out of resources if you are running a quiz with the average of 25-50 concurrent requests. This is probably why you are seeing swap usage now.
The table cache directly relates to max clients which I didn't see in the config so it is using the default of 200. This variable uses additional memory which I stated above for the total memory usage of MySQL.
Don't worry about the vti_bin, it is microsoft office/internet explorer related. Users that use Internet Explorer and have office installed will produce this error in apache. You should really get people off Internet Explorer, it takes 30-50% longer to load a page in Moodle VS Firefox or Chrome.
Can you post values that are in /etc/apache2/apache2.conf for the following:
Timeout
KeepAlive
KeepAliveTimeuout
StartServers
MinSpareServers
MaxSpareServers
MaxClients
MaxRequestsPerChild
ThreadLimit
ThreadsPerChild
Long post so I am going to wait for your reply.
Thanks so much for your help!
I've installed APC.
From what I read, I should use table_open_cache instead of table_cache. That's 450, which could be part of the memory issue?
I don't understand why when looking at RAM usage on VM and on server task manager it is under 50%, but we seem to be seeing low memory / swapping.
Wish we could use Chrome/FF - IE is our hospital standard and required for other programs.
Timeout 300
KeepAlive On
KeepAliveTimeout 5
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 255
MaxRequestsPerChild 0
ThreadLimit 64
ThreadsPerChild 25
We could have 25 concurrent quizzes going but I think that would be an exception to the rule. When the hospital isn't busy it definitely could happen though (time to take inservice tests). I'm still worried about the checklist feature... the xhprof showed it had lots of requests all through the cycle when simply selecting an answer, and when updating 300+ people for each answer, it could get costly. Maybe I'll have to just look at removing that and living with the complaints...
APC should speed up your page loads no doubt.
For table_cache and table_open_cache they are the same, table_cache is the old name and table_open_cache is the new name since 5.1.3. You should be using table_open_cache so yes you should change it. Now as for the setting, you could increase or decrease performance depending on your factors like available RAM. Start with a low number 128 and gradually increase, you should use sysbench during some downtime and adjust accordingly.
I can understand the IE thing coming from a hospital system many years ago.
Your MaxClients is way too high for your server specs, you would need around 20-25GB of RAM for that number, you should be around 20-25 MaxClients for the available memory that apache can use, around 2gb because MySQL is using the rest. You can see that with 25 you can only process 25 concurrent requests, all other will be dropped or queued. This would be the reasoning for your disconnect issues.
You can also increase MaxRequestsPerChild you can add up to 500-1000, even though Moodle docs show a low number 20-30 we have seen better memory management in Ubuntu Server with ours set to 1000. Each process would then use 1000 requests then restart/cache the child process. I think set at 0 could negatively impact performance, it means each process would never die out and just consume memory.
We do not use the checklist mod but you could disable it and see how it goes, I would defiantly recommend that you change the apache directives to what I specified though.
Our VMware Vsphere performance reports come now where close to what our Network Managment system reports, I am not sure how vmware gets those stats but it is always off.
Increase your memory then you could support more than 25 concurrent processes.
JASON
Is max_clients linked to connections? MySQL Workbench rarely goes over 8 and hovers around 4. I never understood Max_clients because I was thinking increasing max clients might increase performance and thus prevent a timeout. We have 4GB or RAM = 4000 MB / 100 = 400 max users, so I didn't think 255 would be an issue... but if we're swapping I do read that that could be from too high a number. I'll try reducing to 25 and see if that affects swapping.
Will let you know results after above changes have a little time to settle. Thanks again!
I could possibly add more RAM (with VM it's pretty simple) if that would help, but still don't understand why my numbers aren't showing full usage.
Wait! That is MaxClients for Apache config! Don't add that too MySQL config and it has nothing to do with MySQL. MaxClients in Apache is how many clients your web server can serve concurrently or each www-data process. For each client in apache during a quiz you could see anywhere from 50-100mb of RAM for client. So using the high end 100mb * 25 clients = 2.5GB of RAM
In workbench you will never get a true number for current connections unless you had near realtime status, a connection to mysql maybe only lasts a few milliseconds.
I have never understood VMware's charts, I am even VCP5-DCV certified and I never grasped the concept in the courses.
I forgot to mention also since you are on Ubuntu Server, you can add
innodb_flush_method=O_DIRECT
to you mysql config, using direct would prevent the "double buffer" where Ubuntu Server would be caching in memory what MySQL has already cached in memory.
JASON
Thanks for all the explanations. Helpful!
MaxClients is there three times, one under mpm_prefork_module, one under event MPM and one under worker. Do they all get changed to 25?
mpm_prefork is the default for Ubuntu Server so use those settings.
I forgot to mention that you cannot calculate off the 4gb of RAM because MySQL is reserving 2 of those GBs so you can only calculate maxclient from the remaining 2GB. Apache will not reserve the memory unlike how MySQL does. You will always see your system at or around 50% RAM usage because the buffer pool will consume the amount you specify at start. A 1GB buffer pool will not only reserve the 1gb but additional memory as well, more like 12-1300mb of RAM then you have to take into account all the other settings like the other buffers and caches for MySQL so that is why you end up using 2GB for MySQL.
JASON
Our intranet does seem to be more snappy after apc change.
I added innodb_flush_method=O_DIRECT, thanks.
After changes and restarting apache service, running top shows I'm still using 64620k swap, or 3%, so it is improved but not totally resolved.
After changes except the last innodb change, mysqltuner says to adjust query_cache_size > 32M, table_cache (table_open_cache) > 450, join_buffer_size > 256K. After the innodb change and restarting mysql service only the join_buffer_size item remains; not sure if it will return.
I'm guessing table_open_cache shouldn't be made higher. I've read join_buffer_size should be 256K tops, and not sure about query_cache_size.
If InnoDB data size / buffer pool from mysqltuner shows 275M/1.0G, should I reduce to 500M or so to free up memory?
Does disabling checklist plugin remove the user data, or could I remove it for a little while just to see the difference?
BTW, the mySQL workbench does seem to have realtime status for connections; it changes on the fly (unless we're talking about different types of connections)
Jason, do you do work for hire if I run into memory issues in the future? ;)
To see the memory changes you would need to reboot the server. Ubuntu will not release the cache and I am sure that you still have old stuff cache'd into memory.
For your data size, I think 512mb would be a good starting point to grow on. 512mb + it will reserve memory for other items.
You can adjust as mysqltuner recommends, you can also run the tuning primer script, same as MySQL Tuner, https://launchpad.net/mysql-tuning-primer
Just a tip also, after rebooting your server from all these changes made, wait at least 24-48 hours of good activity usage before re-running the script so it will have enough information to go on.
I wouldn't remove the plugin, just disable it, click the little eyeball under plugin administration. It will not delete user data that way and I think it still would not delete any if you removed it. Easier to disable though.
Since you are on a single server, I would leave the query cache and type to what moodle recommends. Percona will always recommend to disable because their XtraDB Cluster engine does not support query cache.
Also leave your MySQL connections at 200, anything higher than that will use more ram, not much but it will cache every used connection and you will see your memory used/cache'd going higher and will probably never get released until you reboot.
Slow query log is really for developers unless you are willing to go in and change some of the code that Moodle uses. Actually we had the slow query log on for a while and the only thing that ever showed up was the xtrabackup and mysqldump queries. It is back off now.
Again, after all these changes reboot the server, you should see swap usage go away or at least down to a very minimal. Like i stated before just because you specified 1GB for innodb it is actually probably using close to 2GB after all the other cache settings.
I don't do hire work but I do help out, I have helped many around the US remotely, free of course and because I love doing what I do.
JASON
Wow, free help, that's impressive.
I'm looking at the Moodle performance page regarding thread_cache_size and they give this formula, but not sure how to use it to determine the desired number.You are good with those values which are default for MySQL, You really don't need to go over the default of 151 which I forgot was the default when I stated 200. You will never hit that limit anyways.
They key_buffer/read/writes are MyISAM specific so have no affect for Moodle since it uses the innodb storage engine.
You can look at MySQL Workbench and click on Status and System variables for those values. You can look at max_used_connections and see what your highest value has been connection wise. Each connection to mysql is very fast, you are more than likely to see a number less than 10.
JASON
12 created / 12 connections = 1 x 100 = 100% so good there, thanks.
After having the weekend to run after restart, mysqltuner shows an increase in query cache efficiency (22% - 32%), decrease in query cache prunes (1008908 to 364280). No one is complaining of disconnects so hopefully we are good for the present.
What are the top things to watch for when checking performance in the future?
I wouldn't worry about MySQL too much now that you got it configured, you only had at max 12 concurrent connections. Just keep an eye on your database size, as it grows in the future you will need to increase your innodb buffer accordingly since you are at the moment set at 512mb.
If you can, maybe someone can recommend, keep an eye on your apache's connection/client usage and see if you can log it so that you know if you need to increase RAM to support more concurrent connections. This is where you will more than likely see issues if you go over 25 concurrent connections since your RAM is limited.
We use a full enterprise monitoring suite so for you as a single server it would be overkill and costly, I am pretty sure there is some open source software out there that could aid you in this process.
JASON
Thanks, So you're saying monitoring could be keeping track of Database size and monitoring max number of connections? Sounds like a plan. And of course I'd guess I should verify that I'm not swapping also (which after reboot we are not) and the normal system load and mem usage.
Yeah so, since your innodb buffer is now at 512 and your data size grows close or beyond that than you would increase the buffer. It depends too on how fast it can grow, for us, we keep all logs for 365 days and keep all grade history.
Your connections for Apache, monitor those, if you hit the limit of 25 concurrent then you will see drops so it would be best if you could log that on a daily basis somehow.
You can probably use mod_status for apache along with log_server_status so that it logs to a file on a daily basis using Cron.
http://httpd.apache.org/docs/2.0/mod/mod_status.html
http://httpd.apache.org/docs/trunk/en/programs/log_server_status.html
JASON
"Set the maximum number of connections so that your users will not see a "Too many connections" message. Be careful that this may have an impact on the total memory used. MySQL connections usually last for milliseconds, so it is unusual even for a heavily loaded server for this value to be over 200."
https://docs.moodle.org/27/en/Performance_recommendations#MySQL_performance
So given an entity that has a 1:1 (one computer per student) and all teachers encouraged/have a course in a Moodle and the number of students is in the 1000's .... think I'd set it to at least 200.
'spirit of sharing', Ken
Also, these settings were recommended by percona, which is supposed to be a good tool (my setting to percona setting). Especially wondering about open_files_limit... questioning query_cache_size entry.
key_buffer = 16M to 32M
myisam-recover = BACKUP to FORCE,BACKUP
# SAFETY #
max-allowed-packet = 32M to 16M
max-connect-errors = N/A to 1000000
# CACHES AND LIMITS #
tmp-table-size = N/A to 32M
max-heap-table-size = N/A to 32M
query-cache-type = N/A to 0
query-cache-size = 32M to 0
max-connections = N/A to 500
thread-cache-size = 8 to 50
open-files-limit = N/A to 65535
table-definition-cache = 4096
slow-query-log = 1
Yes, percona is good, however ... logging turned on could eat up a lot of space in a hurry.
Suggest one could turn logging on if needed and only temporary:
mysql -e 'set global log = 1' -u root -p'[password]'
mysql -e 'set global log_slow_queries = 1' -u root -p'[password]'
mysql -e 'set global slow_query_log = 1' -u root -p'[password]'
my.cnf would have to have references to the log files and their locations.
Then, using multitail, which you could install:
multitail -i /var/log/mysql/mysql-general.log -i /var/log/mysql/mysql-slow.log
one could watch those logs in realtime.
The above could be put into a simple bash shell script and run. Wouldn't eat up much memory either since multitail is curses based shell.
# to turn off:
mysql -e 'set global log = 0' -u root -p'[password]'
mysql -e 'set global log_slow_queries = 0' -u root -p'[password]'
mysql -e 'set global slow_query_log = 0' -u root -p'[password]'
If the goal here is to get a server configured and 'perfect' (ie, never having to touch it again), might need to re-think that. Moodles, if used, only grow, they don't get smaller. *Might* get a server configured 'perfectly' for a year, but at the end of that year, a review is needed (more than likely before that). The goal should be pro-active ... not re-active config such that they never have to call you in a crunch. And I know that's what you are trying to do, but, from personal experience, know one might not be able to anticipate future usage.
My 2 cents of course!
'spirit of thinking', Ken
Thanks! I'll probably set up a bi-yearly review of performance. The problem will be knowing all the things to check to make sure it's the way it needs to be...
