Hi Andi,
While MYSQL tuning is something of a black art (and thus only taught in places
like Hogwarts), there are a few things you can do to help yourself.
Be careful raising the query_cache value. Too high and you will shoot yourself
in the foot.
See here http://www.percona.com/blog/2007/03/23/beware-large-query_cache-sizes/
for details.
I would not raise it over 64M.
Actually, read that whole site.
It will tell you about sizing the innodb buffer pool
size:http://www.percona.com/blog/2007/11/03/choosing-innodb_buffer_pool_size/
See here too for the collective wisdom on sizing innodb buffer pool size:
http://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size
You have plenty of memory available. Use it.
Don't be afraid to raise the table_open_cache and innodb_log_buffer_size to
something sensible (256 and 8M).
In my experience though, the database itself is rarely the bottleneck.
Consider how you authenticate users (Active Directory ntlm_auth is slower than
LDAP for instance), consider whether or not you really need radius accounting
and make sure the storage backing the database itself is fast enough to handle
the writes if you do.
Regards,
--
Louis Munro
[email protected] :: www.inverse.ca
+1.514.447.4918 x125 :: +1 (866) 353-6153 x125
Inverse inc. :: Leaders behind SOGo (www.sogo.nu) and PacketFence
(www.packetfence.org)
On 2014-10-20, at 7:45 , "Morris, Andi" <[email protected]> wrote:
> Monday morning mistake of the day. Apologies and thanks for replying.
>
> I’m still curious about the mysql tuning however. I don’t want to add in all
> of the recommended changes just in case it has any knock on effect for
> PacketFence.
>
> Cheers,
> Andi
>
> From: Tim DeNike [mailto:[email protected]]
> Sent: 20 October 2014 12:19
> To: [email protected]
> Subject: Re: [PacketFence-users] High memory usage and mysql tuning questions
>
> 22g of ram is cache. You are mucho ok.
>
> Sent from my iPhone
>
> On Oct 20, 2014, at 5:37 AM, Morris, Andi <[email protected]> wrote:
>
> Hi all,
> I’m finding that my memory usage is always running at around 90% or higher on
> my 4.2.1 server. The top output doesn’t really show anything hogging it
> particularly so I’m wondering what is using this up? I’d like to be certain
> that this server is going to cope with at least double the amount of
> connections it currently has, as we’re shortly going to be migrating all our
> staff users over to the eduroam service as the main SSID, and so packetfence
> will get hit by another several thousand devices.
>
> Output of top is:
> top - 10:26:38 up 5 days, 26 min, 1 user, load average: 0.57, 0.57, 0.62
> Tasks: 236 total, 1 running, 235 sleeping, 0 stopped, 0 zombie
> Cpu0 : 51.0%us, 3.7%sy, 0.0%ni, 43.7%id, 1.3%wa, 0.0%hi, 0.3%si, 0.0%st
> Cpu1 : 28.3%us, 4.0%sy, 0.0%ni, 64.0%id, 3.3%wa, 0.0%hi, 0.3%si, 0.0%st
> Cpu2 : 44.0%us, 4.7%sy, 0.0%ni, 47.7%id, 3.4%wa, 0.0%hi, 0.3%si, 0.0%st
> Mem: 32880976k total, 30432860k used, 2448116k free, 302516k buffers
> Swap: 5242876k total, 3684k used, 5239192k free, 22318392k cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 2560 mysql 20 0 3978m 2.3g 7164 S 105.7 7.3 3419:05 mysqld
> 27017 pf 20 0 1942m 277m 3600 S 5.0 0.9 24:44.28 radiusd
> 2865 root 20 0 236m 76m 5572 S 2.7 0.2 107:47.73 pfdhcplistener
> 2866 root 20 0 234m 76m 5424 S 2.3 0.2 72:30.03 pfdhcplistener
> 2876 root 20 0 208m 68m 2028 S 1.7 0.2 26:28.88 pfdns
> 10975 pf 20 0 302m 84m 3548 S 0.7 0.3 0:04.45 httpd
> 17455 pf 20 0 484m 125m 5192 S 0.7 0.4 0:00.33 httpd
> 17496 pf 20 0 472m 114m 5000 S 0.7 0.4 0:00.13 httpd
> 17583 pf 20 0 472m 114m 5028 S 0.7 0.4 0:00.14 httpd
> 328 root 20 0 0 0 0 S 0.3 0.0 2:41.51 jbd2/dm-0-8
> 1263 root 20 0 192m 6204 5548 S 0.3 0.0 5:41.02 winbindd
> 1306 root 20 0 0 0 0 S 0.3 0.0 12:34.52 drbd_w_mysql
> 1309 root 20 0 0 0 0 S 0.3 0.0 12:00.54 drbd_r_mysql
> 2665 root -3 0 0 0 0 S 0.3 0.0 19:56.91 drbd_a_mysql
> 8038 pf 20 0 302m 84m 3584 S 0.3 0.3 0:06.07 httpd
> 8856 pf 20 0 302m 84m 3540 S 0.3 0.3 0:05.66 httpd
> 10925 pf 20 0 302m 84m 3540 S 0.3 0.3 0:04.42 httpd
> 10971 pf 20 0 302m 84m 3504 S 0.3 0.3 0:04.42 httpd
> 10976 pf 20 0 302m 84m 3548 S 0.3 0.3 0:04.44 httpd
> 11750 pf 20 0 302m 84m 3504 S 0.3 0.3 0:04.18 httpd
> 11758 pf 20 0 302m 84m 3584 S 0.3 0.3 0:04.15 httpd
> 12699 pf 20 0 472m 115m 5100 S 0.3 0.4 0:00.55 httpd
> 17695 pf 20 0 374m 111m 3548 S 0.3 0.3 0:00.04 httpd
> 17938 pf 20 0 472m 114m 5004 S 0.3 0.4 0:00.14 httpd
> 17952 pf 20 0 373m 111m 3548 S 0.3 0.3 0:00.03 httpd
> 17956 pf 20 0 373m 109m 1848 S 0.3 0.3 0:00.02 httpd
> 1 root 20 0 19232 1408 1224 S 0.0 0.0 0:00.91 init
>
>
> Running the mysqltuner.pl tool I am given the following advice:
> Variables to adjust:
> query_cache_size (> 32M)
> table_open_cache (> 64)
> innodb_buffer_pool_size (>= 3G)
> innodb_log_buffer_size (>= 1M)
>
> my.cnf is currently set as:
>
> [mysqld]
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> user=mysql
> max_connections = 1000
> # Disabling symbolic-links is recommended to prevent assorted security risks
> symbolic-links=0
> query_cache_size=32M
> thread_cache_size=32
> innodb_buffer_pool_size=2G
> max_allowed_packet=16M
>
>
> [mysqld_safe]
> log-error=/var/log/mysqld.log
> pid-file=/var/run/mysqld/mysqld.pid
>
> Should I be putting all of the recommended values from the tuning script into
> the my.cnf, or just modifying the ones that are currently in there?
>
> Cheers,
> Andi
> -------------------------------------
> Andi Morris
> IT Security Officer
> Cardiff Metropolitan University
> T: 02920 205720
> E: [email protected]
> --------------------------------------
>
> ------------------------------------------------------------------------------
> Comprehensive Server Monitoring with Site24x7.
> Monitor 10 servers for $9/Month.
> Get alerted through email, SMS, voice calls or mobile push notifications.
> Take corrective actions from your mobile device.
> http://p.sf.net/sfu/Zoho
> _______________________________________________
> PacketFence-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/packetfence-users
> ------------------------------------------------------------------------------
> Comprehensive Server Monitoring with Site24x7.
> Monitor 10 servers for $9/Month.
> Get alerted through email, SMS, voice calls or mobile push notifications.
> Take corrective actions from your mobile device.
> http://p.sf.net/sfu/Zoho_______________________________________________
> PacketFence-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/packetfence-users
------------------------------------------------------------------------------
Comprehensive Server Monitoring with Site24x7.
Monitor 10 servers for $9/Month.
Get alerted through email, SMS, voice calls or mobile push notifications.
Take corrective actions from your mobile device.
http://p.sf.net/sfu/Zoho
_______________________________________________
PacketFence-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/packetfence-users
