basic performance tuning

basic performance tuning

by Fabienne Neveu -
Number of replies: 7
Hi all,

unfortunately our server is swapping.

root@ourserver:~/DOKU# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 92260 85772 258060 1142292 1 1 1232 719 3 7 3 1 92 5 0
root@ourserver:~/DOKU#

So I looked up which process are responsible here and found
a the following ranking.

mysqld 50932 kB
sshd 4376 kB
rsyslogd 1324 kB
sshd 736 kB
bash 608 kB
rpc.statd 592 kB
sshd 556 kB
vmtoolsd 540 kB
bash 476 kB
ntpd 448 kB
bash 448 kB


Because we run Moodle (2.9.8) only on this server, I looked here for database tuning
for Moodle:
https://docs.moodle.org/29/en/Performance_recommendations#MySQL_performance

Then the MySQLTuner gave me a bunch of recommendations.

To cut a long story short I think a central configuration is concerned with
caching (table_cache, query_cache etc.). So I’d like to ask you a more general question.
I have to dig through it all. But what could be a likely culprit according to your
experiences ? We have only a small project (100 or so users) but often times with
simultaneous access to the db. What could be the best way to monitor database activity

in order to calculate the right cache size? 

Thanks in advance for you answers!

Best,

Fabienne

Average of ratings: -
In reply to Fabienne Neveu

Re: basic performance tuning

by William Lu -
Picture of Particularly helpful Moodlers

Hi Fabienne,

It is a good question and should be in the Performance forum.

Average of ratings: -
In reply to Fabienne Neveu

Re: basic performance tuning

by Ken Task -
Picture of Particularly helpful Moodlers

Can moderators move this to the proper forum?

MySQLtuner recommendations are just that ... recommendations.  Best to use mysql (if that's what you are running)  where it can use as much memory as it can without hogging it all ... assume you have an all in one site ... web server is also on same box.  Hard to read the numbers ... is

258060  total memory availalbe and 1142292 ???

What does top show?

What does MySQLTuner say for

Performance Metrics

Maximum reached memory usage:
Maximum possible memory usage:
Overall possible memory usage with other process

In InnoDB Metrics

[OK] InnoDB buffer pool / data size:
[OK] InnoDB buffer pool instances:

Found that not only mysql site but a company blog for Percona (they specialize in DB) to have some pretty good explanations and suggestions ... just be careful though ... what things you see in at their site for recommenations might pertain to their own Percona MySQL or a version you are not running. ;)

'spirit of sharing', Ken


Average of ratings: -
In reply to Fabienne Neveu

Re: basic performance tuning

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I'm struggling to follow your question.

What is the specification of you server?

Why do you assume it's the database - it's not that difficult to constrain the amount of memory MySQL uses. Have you done that?

Average of ratings: -
In reply to Howard Miller

Re: basic performance tuning

by Fabienne Neveu -

Hi all,


I'm sorry I'm late.
Great thanks for all your hints.

I started up with the top command which delivers the following:

top
I was a bit worried because of KiB Swap and cached Mem. Maybe it isn't justified.

The (inno-DB-related)output of mysqltuner.pl is:

...

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[!!] InnoDB buffer pool / data size: 128.0M/1.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 98.09% (359236922 hits/ 366215045 total)
[OK] InnoDB Write log efficiency: 95.44% (7448865 hits/ 7804745 total)
[!!] InnoDB log waits: 0.00% (11 waits / 355880 writes)
...

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4d 15h 23m 0s (3M q [9.854 qps], 13K conn, TX: 5G, RX: 776M)
[--] Reads / Writes: 82% / 18%
[--] Binary logging is disabled
[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 617.8M
[--] Other process memory: 60.5M
[--] Total buffers: 212.0M global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 244.2M (12.15% of installed RAM)
[OK] Maximum possible memory usage: 617.8M (30.73% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Aborted connections: 0.00%  (0/13299)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 12.8% (420K cached / 3M selects)
[!!] Query cache prunes per day: 213412
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 73K sorts)
[!!] Joins performed without indexes: 2632
[!!] Temporary tables created on disk: 55% (27K on disk / 50K total)
[OK] Thread cache hit rate: 99% (20 created / 13K connections)
[!!] Table cache hit rate: 5% (509 open / 10K opened)
[OK] Open file limit used: 4% (48/1K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
...

Variables to adjust:
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    query_cache_size (> 36M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 512)
    performance_schema = OFF disable PFS
    innodb_file_per_table=ON
    innodb_buffer_pool_size (>= 1G) if possible.
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=64M) if possible.
    innodb_log_buffer_size (>= 8M)
 
...


The version of my MySql Server is:
Your MySQL connection id is 13356
Server version: 5.5.54-0+deb8u1 (Debian)


I assume it's the database because i typed the

following on the shell:

for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -n -r | less

which delivered this ranking

mysqld 30936 kB
munin-node 7792 kB
apache2 2696 kB
apache2 1860 kB
apache2 1860 kB
apache2 1844 kB
apache2 1772 kB
apache2 1700 kB
apache2 1700 kB
apache2 1700 kB
apache2 1692 kB
apache2 1688 kB
apache2 1672 kB


So I thought, that the database would use swap space.
Thanks for targeted information. I will follow that.

If you have furter recommendations (how to monitor database activity over time ?  (Percona) links ?)), please let me know.

Best,

Fabienne






Average of ratings: -
In reply to Fabienne Neveu

Re: basic performance tuning

by Ken Task -
Picture of Particularly helpful Moodlers

Am sorta involved in another project right now that prevents me from diving into details of what you've shared back concerning your system.  My project is grinding away right now so have time for this quickie response:

Percona Links:

https://www.google.com/search?q=percona+blog&ie=utf-8&oe=utf-8

Word of caution when using Percona blog, etc.   They sell their own version of MySQL which is not your MySQL.   This to say, don't take things/recommendations at face value.

Also ... we have more than one 'advisor' in here ... will defer to whatever Howard says as he does have a different perspective than I. ;)

'spirit of sharing', Ken

Average of ratings: -
In reply to Fabienne Neveu

Re: basic performance tuning

by Ken Task -
Picture of Particularly helpful Moodlers

Ok, have time now ... this could easily spill over into 'information overload' ... so bear that in mind with items below.

Top:
First ... 2 Gig memory *may not* be enough - other factors determine.

This is just me:  have found the 'sweet spot' for servers that are standalone ... web/DB on same server to be 16 Gig.   Can work with 8Gig if site not that heavily used.  Lower one goes, however, and, depending upon usage, performance suffers.

SWAP ... when server was initial installed, linux generally looks at
total memory and then recommends SWAP space to be equal to memory OR
twice as much.  So 2Gig mem = 2Gig SWAP or 4Gig SWAP.   Yours did equal.  IF this were in a Virtualized environment, like VMWare/other, no biggy to increase memory ... next reboot of server the guest OS recognizes the extra memory.  SWAP space and anything related to disk, even if allocated by VMWare/other, operating system doesn't know about and won't use it until configured to do so.   Those sorts of operations can get into hardcore command line things ... and one better do them right ... or risk loosing what you have.

SWAP means reads/writes to disk ... DB's run best IF one can get as much of the DB's being served in memory ... that makes sense, doesn't it?

Using NO SWAP space right now ... is good.
But ... see there isn't much left free Mem ... which could mean if  the moodle gets busy, SWAP will eventually be used.  Try as you might, with 2Gig memory, one might have to live with some SWAP.

This comes into play with the balancing act between apache and mysql.

The Performance Metrics output - what you have available:

[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 617.8M

Note the second item above.

[OK] Maximum reached memory usage: 244.2M (12.15% of installed RAM)
[OK] Maximum possible memory usage: 617.8M (30.73% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

% of installed RAM ... have read, Persona, that if one could be DB to use 80% of memory, best.  Have never been able to, however, best, 60%.

Since InnoDB is required now ... 3.2+> then anything related to InnoDB config more important now than before:

[!!] InnoDB File per table is not activated
Activate file per table.

[!!] InnoDB buffer pool / data size: 128.0M/1.0G

Buffer pool too low (128M) when compared to the actual data size: 1 Gig.
(remember, however, your mem is 2 Gig!)

You can see in recommendations, MySQL Tuner does make a couple
recommendations to settings that relate to InnoDB

innodb_file_per_table=ON
innodb_buffer_pool_size (>= 1G) if possible.

NOTE the 'if possible'.

Remember, you have 2 Gig to work with in real RAM .... and only 2 Gig of space for SWAP.

Bottom Line: get more true Memory ... Moodle is a 'memory hog' ... does more ... requires more.

Will stop at this point .... am suffering brain cramp just talking about it! smile

'spirit of sharing', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: basic performance tuning

by Fabienne Neveu -

Hi Ken,


So basic things are not calculated well.

Have thanks for your help! I think have to reboot

with totally different parameters first.

Hope I can come back to you, if things get

complicated.


Cheers,

Fabienne

Average of ratings: -