Reducing MySQL memory usage on Ubuntu / Debian Linux

If you are running your services on a low end virtual hosting every byte of memory you can save is important. The memory is often the limiting factor of how many applications you can run on VPS: CPUs are shared, memory not, on the same physical host.

  • Low-end VPS come with 512 MB memory or less
  • Front front-end server Apache / Nginx / Varnish takes > 100 MB +  min. 20 MB for each child process
  • Memecached takes its toll
  • MySQL takes 200 – 400 MB
  • Each Python / PHP process takes at least 15 MB and you need parallel processes for paraller HTTP requests (FCGI, pre-fork, others… )
  • Operating system processes need some memory (SSH, cron, sendmail)

As you can see it gets very crowded in 512 MB.

It’s especially troublesome since the memory is allocated lazily and the memory usage builds up slowly. In some point caches are no longer caches, but swapped to a disk – virtual memory usage grows beyond available RAM. To keep the server response, everything time critical should fit to RAM once and if the processes themselves don’t know how to release memory in this situation you need to tune a memory cap for them.

1. MySQL memory consumption

MySQL can be a greedy bastard what comes to memory consumption. Here on this server MySQL seems to take 417M virtual memory which seems to be little excessive for just running two WordPress instances and one Django / Python application:

1310 mysql     20   0  417M 21100  2776 S  0.0  1.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v

After some tuning I was able to bring it down a bit

3354 mysql     20   0  276m  19m 2848 S    0  1.2   3:41.19 mysqld

A reduction of 130 MB, or 1/4 of the server total memory. Not bad.

Use mtop to monitor running MySQL, its querieries, etc. so you know what’s going on. As you can see this MySQL has very good cache rate meaning that basically it is keeping everything in memory. If the content of the sites is less than 10 MBytes total, 400 MB contains plenty of space to cache the content:

load average: 0.05, 0.08, 0.16 mysqld 5.0.51a-3ubuntu5.8-log up 1 day(s), 19:47 hrs                                                             
2 threads: 1 running, 6 cached. Queries/slow: 187.1K/0 Cache Hit: 99.39%

2. What eats memory

I am not an expert on MySQL, so I hope someone with more insight could post comments regarding how to tune MySQL for low memory situations and how it is expected to behave.

Some ideas I run through my head

  • MySQL default cache settings are not too tight on Ubuntu/Debian, making it suitable for moderate loads, not low loads. If you don’t have much content, everything is just kept in memory (even if not needed)
  • MySQL uses round robin for connections and if there is 100 max connections it will allocate a thread stack for each connection (someone please confirm this – I found contracting infos).

3. Configuring MySQL

Here are listed some methods how to reduce the memory usage. This is what I done on this little box

MySQL is mostly configured in /etc/mysql/my.cnf on Ubuntu / Debian.

The final adjustments

key_buffer              = 8M
max_connections         = 30
query_cache_size        = 8M
query_cache_limit       = 512K
thread_stack            = 128K

4. More info

Send in more tips please! Is 32-bit better than 64-bit for low end VPS, how much this affects MySQL?

\"\" Subscribe to RSS feed Follow me on Twitter Follow me on Facebook Follow me Google+

Copy/move phpBB3 forum from a server to another computer (Ubuntu/Linux)

Here are short instructions what you need to do in order to move / copy phpBB3 forum.

1. Prerequisites

What you need in order to benefit from these instructions

  • Basic UNIX command-line knowledge
  • SSH access to the server
  • MySQL access to the database
  • LAMP stack ready on the new server

These instructions have been tested on Ubuntu/Debian/Linux but they should work in other environments too.

2. Write down database access information

Get password from config.php file on the old server:

cd /var/www/phpBB3
cat config.php

Write down database name, username and password.

3. Copy files

Use rsync to remotely copy forum files to a new computer. On new computer, in /var/www folder

rsync -av --compress-level=9 user@oldserver.com:/var/www/phpBB3 .

4. Dump and copy database

Execute the following command on the new server. It takes SSH connection to the old server and dumps phpBB3 database to the new server over the SSH connection.

ssh user@oldserver.com -C -o CompressionLevel=9 mysqldump -u databaseuser --password=databasepassword --skip-lock-tables --add-drop-table databasename > phpbb3.sql

5. Create a new database

Use the old access information from config.php to create a database with identical access information on the new server. You need a MySQL root access to create new databases.

mysql -uroot -p

Create database and grant access to phpBB3 user for it.

mysql> create database databasename;
mysql> GRANT ALL ON databasename.* TO 'databaseuser'@'localhost' identified by 'databasepassword';

Load the database on the new server from the dump file:

mysql> connect databasename;
mysql> source phpbb3.sql

6. Configure Apache virtualhost for the new server

The last step is to set-up Apache virtual host on the new server, so you can access the phpBB3 using a domain name. Note that this doesn’t need to be a real domain name, but you can spoof the domain name using /etc/hosts file on your local workstation.

Add file /etc/apache2/sites-enabled/phpbb3.conf (or pick a filename based on forum name if you host multiple forums)

<VirtualHost *>
 ServerName yourdomainname.com

 DocumentRoot /var/www/phpBB3
 <Directory />
   Options FollowSymLinks
   AllowOverride None
 </Directory>

</VirtualHost>

Note that <virtualhost *> may change depending on how Apache has been set up to listen IP addresses and ports. Also if you are using a shared hosting package or VPS you might need to use the server control panel (cPanel) to do this step.

Then check if your new config file is ok and restart Apache:

apache2ctl configtest
apache2ctl graceful

7. Hosts spoofing trick

If you are not having a DNS server of your own which you can use for the copy you can always use /etc/hosts file trick to spoof domain names. This way you can make Apache to serve the forum from the server even if the forum is not connected to any real domain name yet.

 

 

 

 

 

\"\" Subscribe to RSS feed Follow me on Twitter Follow me on Facebook Follow me Google+

When Python sucks: how you call a function and document it

Though maybe written tongue-in-cheek, this Python Makes Me Nervous article has some excellent points.

  • Because of duck-typing, you should rigorously document how methods should be called (try epytext and its fields).
  • Most open source Python projects do the exact opposite
  • Even Python standard library is poorly documented and sets a very bad example (missing manual ???)
  • Thus, programming in Python becomes nightmare of grepping through source code (the implementation) or stepping into it in pdb just to figure out how APIs should work (Plone/Zope, anyone?)

Should Python community stop in some point to focus on delivering better documentation instead of focusing on new features and goodies (like the syntax moratorium which was recently lifted)?

From my personal experience

  • The best, and the only, person to document the code adequately is the person how originally wrote it
  • Because the author already knows how to use the code he doesn’t need to care about the fact how to enable the code for other users.  Many libraries and projects are driven by “scratching your own need” mentality, not by “let’s make this a happy community” mentality. The exception is something like Facebook or Google whose sole purpose is to attract new users their platform bringing in new €€€.
  • If you are developing a framework or community project make the documentation a requirement for deliverable and stick with it. If you let one person to skip one hour of writing documentation you are making 10 persons spending one hour figuring out how to use the damn thing.
  • Doctests are not documentation. They are tests. They are extremely unreadable way to say “how I should use this thing”, because doctests are often executed in the context of test stubs which do not reflect connections to the other parts of the framework or real contexts.
  • “Buy a book – it tells you everything” business model is not feasible in long run. Books get old. Books are not searchable. People don’t buy books.

The good documentation is a way to differentiate, and win, in the situation where there are competing frameworks. I believe the success of Django was mostly driven by its good documentation.

This points could be applied to other duck-typed, open source driven programming languages (PHP anyone?). With good documentation we can reduce the need of Valium recipes for everyone of us.

 

\"\" Subscribe to RSS feed Follow me on Twitter Follow me on Facebook Follow me Google+

QuasselDroid: Quassel for Android – cross-platform mobile IRC client

IRC (Internet Relay Chat, the description of IRC) is a chat protocol used by many open source and hacktivism projects for real-time discussion and support chat. I myself participate to Python and Plone discussion and now recently to Android chat on Freenode and IRCNet networks.

Quassel is a distributed IRC client. Distributed in the sense that it keeps your chat sessions on the server side: even if you get disconnected you won’t lose any chat messages. This is a nice feature for people who need to be constantly in touch with the communities.  For IRC veterans, Quassel is like the good ol’ screen + irssi combo, but with an user interface which does not require a degree in Perl scripting. This is a short tutorial for getting or building  the mobile version of Quassel, Quasseldroid for Android.

Everyone how has tried it knows that irssi or any terminal software suits very badly for mobile screens. QuasselDroid fixes this: it provides an user interface which is suitable even for touch screens. Also, with Quassel core on the server side, you do not need to worry about disconnections which often happen with mobile networks, With desktop Quassel and mobile QuasselDroid you should be able to seamlessly leave your desktop, go traveling whilst continuing the on-going dispute of the next most important internet thing with your on-line friends and foes.

This tutorial show how to get binary or source from Github and compile the project for your phone. Note that QuasselDroid is still in very early developing phase and the main purpose of this is to drive more development into QuasselDroid. Do not expect it to be ready for your specific use cases yet! Also, these instructions are tested only on Samsung Galaxy S with Android 2.2.

Quassel is originally based on Qt C++ libraries and uses Qt based serialization protocol to communicate between the server (your IRC session) and client (your phone). QuasselDroid implements Qt protocol from scratch in Java and does not use any Qt libraries on Android. There also existed a prior version which required a proxy server, but this has been fixed for now.

1. Getting and building QuasselDroid

Before proceeding you need

  • A server which allows you to run a background process with more than hundred megabytes of memory usage
  • Quassel core installed on the server side (this maintains your IRC session). Core itself comes with decent command-line help. If you get stuck ask help on #quassel channel @ freenode IRC network.
  • Preferable Quassal desktop client (Windows, OSX, Linux) for ircing from the desktop

1. Downloading pre-built binary

Note: there exists a pre-built Android binaries for non-Android hackers. Download QuasselDroid Android binary from here. You still need to set-up the server side core yourself.

1. Prerequisites for building and running QuasselDroid

This is the recommend method of installing QuasselDroid if you are looking for helping in QuasselDroid development. It is not that difficult, as Android tools are quite friendly for newcomers.

1. Compiling and running

Install Android SDK, Eclipse  Android plug-ins for Eclipse. Note this is several hundreds of megabytes worth of software, so be patient and entertain yourself with some cool music whilst downloading.

Install Quassel client, Quassel core and get yourself familiar with the noveau IRC experience.

Get code from Github:

git clone https://github.com/sandsmark/QuasselDroid

Open project in Eclipse. Start Eclipse, choose or create any empty directory as workspace of just choose the default one. Then choose File -> Import -> General -> Existing projects into Workspace. In Select Root Directory you need to choose a directory above Github checkout directory. Select QuasselDroid in Projects list (which is now refreshed after selecting root directory).

In this point I had to select the project, choose Project -> Clean from Eclipse menu (there were some errors popping up in Eclipse internal console).

Right click Project -> choose Run as -> Android application.

  • If your phone is connected via USB Eclipse will automatically build and install APK directly to your phone
  • If you do not have phone connected QuasselDroid will start in the emulator

2. Installing QuasselDroid

2.

2. Creating a core entry

When QuasselDroid is run for the first time. press Android Menu key on the start-up screen(this was little hard to discover) and choose Manage cores. Now you need to configure quassel core for the server – for this process you should already have experience with Quassel desktop client.

2. SSL

Also, you might need to disable SSL support if you are running a core without SSL compiled in. Again, press Android Menu key, choose Preferences and uncheck SSL.

3. Debugging issues

When you run an issue and you can repeat it reliably with necessary log traces available, file a bug report on Github project page.

Android has a logging tool called LogCat, showing log output from emulator or device. You will see Android log output in Eclipse in LogCat view when

  • Emulator is running
  • A device is connected via USB, debug mode enabled (from system Settings menu)

Use Eclipse Window -> Show -> Debug perspective to see LogCat window if it’s hidden.

If you launch the application in Eclipse using Debug run (the bug icon) Eclipse will stop on the line where an uncaught Java exception is thrown. This is very useful for debugging crashing bugs (Android pops up force close dialog).

On the server side, you can see quasselcore output in stdout and see how the core reacts to client connection attempts.

\"\" Subscribe to RSS feed Follow me on Twitter Follow me on Facebook Follow me Google+