[AzPHP] Tuning MySQL DB server

Fri Oct 12 17:07:47 MST 2012

Am 12. Oct, 2012 schwätzte Eric Cope so:

moin moin,

> 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?

Yes, use 5.5 if you can. I talked to Oracle's MySQL team at SCaLE earlier
in the year and they said a lot of effort was put into improving InnoDB
speed and data compression.

Consider Percona or MariaDB.

If you only have 600MB of data you don't need 8GB of RAM.

Set innodb_file_format=barracuda.

Set innodb_file_per_table. If you don't already have that in place, you'll
need to convert the tables.

Can you move the DB to bare metal rather than a VPS?

You want your innodb buffer pool to be big enough, but not too big.

Would you benefit from partitioning? That made a huge difference for us,
but we're dealing with hundreds of GB of data and a few tables with
billions of rows.

ciao,

der.hans

> 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 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 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
>> Like us: 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
>> Like us: 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
>> Like us: 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
>

-- 
#  http://www.LuftHans.com/        http://www.LuftHans.com/Classes/
#  Strangers are friends just waiting to happen!