MYSQL Performance Tuning for Self Hosted

Get Started | Login

Discussion Forums > Self Hosted Control Panels

Replies: 4 Views: 91

LoveSports Aspiring developer Posts: 8 Reg: Apr 19, 2013 Joondalup 80	confirm \| cancel 06/17/14 06:02 AM (10 years ago) MYSQL Performance Tuning for Self Hosted Hi, Apologies if there is a doc that I have missed but I have had a look and can't find one. I have a self hosted buzztouch instance on a bitnami LAMP image running on Amazon EC2 (t1-micro). In general, this has been great, apart from when building a list of screens in the control panel when the app has hundreds of screens; this is really slow ie minutes. I can see through "top" that mysql is consuming 99% of server resource while the build is happening and I can also see the query running in mysql (show processlist). I thought that moving from t1-micro to m1-small would do the trick. It hasn't, but I expect it will when I tweak the right mysql parameters. Can anyone give me some direction? Cheers Brendan Title Comments Hi, Apologies if there is a doc that I have missed but I have had a look and can't find one. I have a self hosted buzztouch instance on a bitnami LAMP image running on Amazon EC2 (t1-micro). In general, this has been great, apart from when building a list of screens in the control panel when the app has hundreds of screens; this is really slow ie minutes. I can see through "top" that mysql is consuming 99% of server resource while the build is happening and I can also see the query running in mysql (show processlist). I thought that moving from t1-micro to m1-small would do the trick. It hasn't, but I expect it will when I tweak the right mysql parameters. Can anyone give me some direction? Cheers Brendan

tb buzztouch Evangelist Posts: 2050 Reg: Nov 03, 2011 Oxford 32,300	06/17/14 08:26 AM (10 years ago) How have you got Self-Hosted anyway? It's for members only. Comments How have you got Self-Hosted anyway? It's for members only.

Nicks App Code is Art Posts: 426 Reg: May 21, 2014 Las Vegas 6,010	06/17/14 12:11 PM (10 years ago) I moved away from SQL about 2 years ago. It is much faster in PHP to work with Arrays themselves... And SQL servers can be slow, down for maintenance, or fussy especially if you are INSERTing. Then at the end of my script run that builds data, I serialize my data array, save it to .txt... then next time, read the .txt, unserialize the array, and work with the array again. And rename the "old" array with a timestamp, before saving the new, every time. (Using similar technique for other programming languages. Do your favorite search, on "PHP serialize"/unserialize... and/or if your C e.g. language supports serialize.) SQL always made me nervous about not saving backups. So I rename the .txt file with timestamp before I write the new one. Comments I moved away from SQL about 2 years ago. It is much faster in PHP to work with Arrays themselves... And SQL servers can be slow, down for maintenance, or fussy especially if you are INSERTing. Then at the end of my script run that builds data, I serialize my data array, save it to .txt... then next time, read the .txt, unserialize the array, and work with the array again. And rename the "old" array with a timestamp, before saving the new, every time. (Using similar technique for other programming languages. Do your favorite search, on "PHP serialize"/unserialize... and/or if your C e.g. language supports serialize.) SQL always made me nervous about not saving backups. So I rename the .txt file with timestamp before I write the new one.

LoveSports Aspiring developer Posts: 8 Reg: Apr 19, 2013 Joondalup 80	06/17/14 03:51 PM (10 years ago) My wife is a member. Thanks Nicks App for your suggestion. Comments My wife is a member. Thanks Nicks App for your suggestion.

Niraj buzztouch Evangelist Posts: 2943 Reg: Jul 11, 2012 Cerritos 37,930	06/19/14 06:39 AM (10 years ago) Make indexes, do database repairs. Backup first! Comments Make indexes, do database repairs. Backup first!

Login + Screen Name Required to Post Login to participate so you can start earning points. Once you're logged in (and have a screen name entered in your profile), you can subscribe to topics, follow users, and start learning how to make apps like the pros.