[AzPHP] Tuning MySQL DB server

Vimal Shah vimals at sokikom.com
Fri Oct 12 16:34:26 MST 2012 

Agreed on 5.5. Debating Pecona. I did attach the cnf. I played with the
settings using the tuner and modified them accordingly. Also tried
percona's config tool output. When adding all the variables simultaneously,
mysql didn't restart. So I added them one at a time. This worked better but
was a slow process. There has to be a better way?

On Fri, Oct 12, 2012 at 4:08 PM, Eric Cope <eric.cope at gmail.com> wrote:

> Upgrading to 5.5 will help, as well has going to Percona. Did you post
> your my.cnf file? Did you modify it from what I sent?
>
> Eric
>
> On Oct 12, 2012, at 4:06 PM, Vimal Shah <vimals at sokikom.com> wrote:
>
> 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 2G B.
>>>>
>>>> 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>
>
> <my.cnf>
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>



-- 
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/ad74b912/attachment.html>

More information about the PLUG-discuss mailing list