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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]>wrote: >> >>> Can't tell much from that. Sure it's a 64 bit build? >>> >>> Jeff Wolkove >>> >>> ----- Reply message ----- >>> From: "Vimal Shah" <[email protected]> >>> To: <[email protected]> >>> Cc: "Main PLUG discussion list" <[email protected]> >>> 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 <[email protected]>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" <[email protected]> >>>> To: <[email protected]>, " Main PLUG discussion list" < >>>> [email protected]> >>>> 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 >>>> [email protected] >>>> http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org >>>> >>>> >>> >>> >>> -- >>> Vimal (rhymes with Kimmel) Shah >>> VP of Engineering >>> Sokikom >>> Mobile: (480) 752-9269 >>> Email: [email protected] >>> 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 >>> [email protected] >>> http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org >>> >>> >> >> >> -- >> Vimal (rhymes with Kimmel) Shah >> VP of Engineering >> Sokikom >> Mobile: (480) 752-9269 >> Email: [email protected] >> 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: [email protected] > 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 - [email protected] > To subscribe, unsubscribe, or to change your mail settings: > http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss > > > --------------------------------------------------- > PLUG-discuss mailing list - [email protected] > 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: [email protected] Web: www.sokikom.com Follow us: twitter.com/sokikom <http://www.twitter.com/sokikom> Like us: facebook.com/sokikom <http://www.facebook.com/sokikom>
--------------------------------------------------- PLUG-discuss mailing list - [email protected] To subscribe, unsubscribe, or to change your mail settings: http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
