[AzPHP] Tuning MySQL DB server

Fri Oct 12 16:06:12 MST 2012

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 at 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 at 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 at sokikom.com>
>> To: <azphp at list.azphp.org>
>> Cc: "Main PLUG discussion list" <plug-discuss at lists.plug.phoenix.az.us>
>> Subject: [AzPHP] Tuning MySQL DB server
>> Date: 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 at 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 at sokikom.com>
>>> To: <azphp at list.azphp.org>, " Main PLUG discussion list" <
>>> plug-discuss at 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 <https://tools.percona.com/> along with the mysqltunner
>>> perl script <https://github.com/rackerhacker/MySQLTuner-perl>.  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 at 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 at sokikom.com
>> Web:    www.sokikom.com
>>
>> Follow us: twitter.com/sokikom <http://www.twitter.com/sokikom>
>> Like us: facebook.com/sokikom <http://www.facebook.com/sokikom>
>>
>>
>> _______________________________________________
>> azPHP mailing list
>> azPHP at 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 at sokikom.com
> Web:    www.sokikom.com
>
> Follow us: twitter.com/sokikom <http://www.twitter.com/sokikom>
> Like us: facebook.com/sokikom <http://www.facebook.com/sokikom>
>
>

-- 
Vimal (rhymes with Kimmel) Shah
VP of Engineering
Sokikom
Mobile: (480) 752-9269
Email:   vimals at sokikom.com
Web:    www.sokikom.com

Follow us: twitter.com/sokikom <http://www.twitter.com/sokikom>
Like us: facebook.com/sokikom <http://www.facebook.com/sokikom>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20121012/28ff2348/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: my.cnf
Type: application/octet-stream
Size: 2094 bytes
Desc: not available
URL: <http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20121012/28ff2348/attachment.obj>