[AzPHP] Tuning MySQL DB server

[Vimal Shah]

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>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20121012/0708d693/attachment.html>