High-capacity MySQL random notes

my.conf mysql max connections

grep -h oom-killer /var/log/kern.log{.0,} | cut -f1, -d: | sed 's/$/:xx UTC/' | uniq -c

aptitude install mytop

mytop -u wsf2008 -d wsf2008 -p pASsWoRd

This is just too funny.

http://weblog.mediatemple.net/weblog/2007/01/19/anatomy-of-mysql-on-the-grid

Our new offering quickly became a refuge for sites that were kicked off their old hosting company; a common industry practice. Because of their high database load “requirements” and need of resources, these site owners were shut down immediately and told to leave other hosts. Many of these “orphaned” users had applications, code, and query instructions that were grossly inefficient for even a massive dedicated server. A number of these users came running to (mt) Media Temple with the promise that their applications, despite all of their deficiencies, would be accepted and not turned off. These users are radically different, by orders of magnitude, from anything we had previously analyzed or benchmarked.

So, in keeping with our original promise to sustain our customer’s ability to keep sites up regardless, we needlessly taxed our system, asked no one to leave and relied on our GPU billing model to account for unimaginable database usage. We continued to add hardware, analyze data, add more hardware, analyze data, and add more hardware yet again. In some situations were still unable to sustain these users. No matter what load-leveling we did, these systematically pathological applications overloaded every database we put them on – no matter how big or resourceful.

The solution:

a new container system which will give each GRID customer their own dedicated MySQL server. This is great news for our customers because they will now have their own unshared copy of MySQL with their own dedicated RAM and CPU space which allows for a more stable, predictable, understandable MySQL application environment.

sweet, a hosting company called media temple has a public knowledge base: http://kb.mediatemple.net/article.php?id=129

(backing up and restoring a database... actually the same information as on Agaric's own page about the same stuff)

mysqldump --add-drop-table -u Username -p dbname > dbname.sql

gunzip < dbname.gz | mysql -u Username -p dbname

Resolution