Thank you to everyone, this is great feedback. I'm planning to change these before the next traffic spike.You don’t say much about performance issues other than not using all the memory. What are your actual symptoms?The symptoms were that the 3 web server's (in front of the database server) CPU was railing during high traffic times. Adding 2 more (total 5 webserver seems to have put the fire out.. )It seems that another recommendation was that a partitioning (using LVM) and filesystems (ext4 for the OS and xfs for MySQL) is needed BEFORE tuning even occurs! Is it me or does this route seem slightly overkill for a ~700MB database footprint? I completely think this is a good idea, (because it will set up the stage for a Master Slave system), but is this needed now?Lastly, Linode (defaults to ext3) recommends against going to ext4 or xfs. Should I ignore this and use these FS anyway? If not, and ext4 and xfs is absolutely required, does moving the DB server to AWS make sense? It seems that on EBS, I can install whatever I want.On Fri, Oct 12, 2012 at 6:45 PM, Jeff Wolkove <wolkove@biz-link.com> wrote:
The fact that it’s not using all the memory doesn’t really mean it can’t. Some memory is allocated dynamically for connections and released when no longer needed. A read buffer will be set when it does a sequential read but may not be needed or in use at a given point in time. Buffers may not be allocated till they’re needed. Here’s a couple of other ideas:
query_cache_limit = 16M
This is the maximum size of a query result that will be cached. Unless you have a lot of very large and repetitive queries this is too big. Consider cutting it down to 2M. Even this is a pretty big result set and may not be worth caching.
query_cache_size = 1G
This is the maximum size of the query cache. It’s probably too big. It won’t be used if there’s nothing to cache. Start at maybe 64M and increase it slowly while monitoring the cache hit rate under actual use. The query cache can be a bottleneck on busy multi-processor systems.
key_buffer = 16M
I believe the correct name for this is key_buffer_size but may have changed at some point. Probably enough since you’re not using MyISAM but it’s used also for temporary tables
#max_connections = 100
100 is the default but you may need more. Consider uncommenting and increasing this based on your web server load
sort_buffer = 64K
This is pretty small and I think the variable name is wrong. Try sort_buffer_size = 8M
innodb_buffer_pool_size = 6G
May be too big. This caches innodb indexes and frequently accessed row data but you should increase this slowly as it’s needed.
You don’t say much about performance issues other than not using all the memory. What are your actual symptoms?
Jeff Wolkove
From: azPHP [mailto:azphp-bounces@list.azphp.org] On Behalf Of Vimal Shah
Subject: Re: [AzPHP] Tuning MySQL DB server
Sent: Friday, October 12, 2012 4:06 PM
Though I attached this as well.. hopefully I took out the important things..
On Fri, Oct 12, 2012 at 3:59 PM, Vimal Shah <vimals@sokikom.com> wrote:
How can I tell this? I ran the following:
# echo '\s' | mysql
--------------
mysql Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (x86_64) using readline 6.1
Connection id: XX
Current database:
Current user: XX
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.63-0ubuntu0.10.04.1-log (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: XXX
Uptime: 1 hour 12 min 18 sec
Threads: 1 Questions: 200 Slow queries: 14 Opens: 615 Flush tables: 1 Open tables: 152 Queries per second avg: 0.46
On Fri, Oct 12, 2012 at 3:53 PM, Jeff Wolkove <wolkove@biz-link.com> wrote:
Can't tell much from that. Sure it's a 64 bit build?
Jeff Wolkove----- Reply message -----
From: "Vimal Shah" <vimals@sokikom.com>
To: <azphp@list.azphp.org>Cc: "Main PLUG discussion list" <plug-discuss@lists.plug.phoenix.az.us>
Subject: [AzPHP] Tuning MySQL DB serverDate: Fri, Oct 12, 2012 3:49 pm
Server version: 5.1.63-0ubuntu0.10.04.1-log (Ubuntu)
On Fri, Oct 12, 2012 at 3:22 PM, Jeff Wolkove <wolkove@biz-link.com> wrote:
What build & version of mySQL are you running now? How much memory is set aside for cache, etc? It may help to post your my.cnf (edited for privacy)
Jeff Wolkove
----- Reply message -----
From: "Vimal Shah" <vimals@sokikom.com>
To: <azphp@list.azphp.org>, " Main PLUG discussion list" <plug-discuss@lists.plug.phoenix.az.us>
Subject: [AzPHP] Tuning MySQL DB server
Date: Fri, Oct 12, 2012 1:44 pm
Hello all,
I recently had many teachers and students logging into my site, this is a good thing. The server infrastructure (Linode VPS = 1 load balancer => 2 webservers and 1 database (DB) server) started to show CPUs that were railing at peaks times on the Munin graphs. This was not so good. The bandaid (which I need to fix) was to add more servers, I now have 5 webservers each have 2GB of RAM and have 2.2.7 GHz CPU (4 of them on each box). This has to be overkill.. Later, realized that MySQL's system variables were not optimized for the DB server. Ran Percona's configuration tool along with the mysqltunner perl script. This led to the discovery that 32-bit version of Ubuntu will not allow MySQL to use any more that 2GB.
NEW DB server = After upgrading the DB server to 8GB and along with going to 64-bit Ubuntu 10.04, I am still unable to get to the box to use all the memory. The process I've been using is (1) use apache bench or jmeter to fling large connections (and long queries) at the DB server (2) run the tuner script to see it's recommendations to the system variables (2) update the variables, restart mysql and start over..
This has led to unsatisfactory results. I know that fixing the slow queries (which is in process) is a place to start, but I feel that the DB server should be using more RAM. Can someone point out the flaws in my process or maybe even suggest a better way to do this?
Thank you very much for you time.
First day DBA,
-Vimal
PS Thanks Eric C., for starting me down the right direction.
_______________________________________________
azPHP mailing list
azPHP@list.azphp.org
http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org
--
Vimal (rhymes with Kimmel) Shah
VP of Engineering
Sokikom
Mobile: (480) 752-9269
Email: vimals@sokikom.comWeb: www.sokikom.com
Follow us: twitter.com/sokikom
Like us: facebook.com/sokikom
_______________________________________________
azPHP mailing list
azPHP@list.azphp.org
http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org
--
Vimal (rhymes with Kimmel) Shah
VP of Engineering
Sokikom
Mobile: (480) 752-9269
Email: vimals@sokikom.comWeb: www.sokikom.com
Follow us: twitter.com/sokikom
Like us: facebook.com/sokikom
--
Vimal (rhymes with Kimmel) Shah
VP of Engineering
Sokikom
Mobile: (480) 752-9269
Email: vimals@sokikom.comWeb: www.sokikom.com
Follow us: twitter.com/sokikom
Like us: facebook.com/sokikom
_______________________________________________
azPHP mailing list
azPHP@list.azphp.org
http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org
--Vimal (rhymes with Kimmel) ShahVP of EngineeringSokikom
Mobile: (480) 752-9269
Email: vimals@sokikom.comWeb: www.sokikom.comFollow us: twitter.com/sokikomLike us: facebook.com/sokikom
---------------------------------------------------
PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss