About

mFabrik Blog is about mobile and web software development, open source and Linux. We tell exciting tales where business, technology, web and mobile convergence.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License.

Reducing MySQL memory usage on Ubuntu / Debian Linux

Posted on March 31, 2011

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.

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%

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).

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

More info

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

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Setting up Apache virtual hosts behind Varnish

Posted on March 4, 2011

Varnish is a very fast front end cache server. You might want to use it at the front of Apache to speed up loading of your static pages and static media, for example for your WordPress blog. You can also use Varnish backends to multiplex the requests between Plone and Apache based PHP software running on the same server using different backend directives.

However if you wish to use Apache virtual hosts with Varnish there is a trick in it.

We use the following setup

  • Varnish listens to port 80, HTTP
  • Apache listens to port 81
  • Varnish uses Apache as a backend

The related varnish.vcl is

backend backend_apache {
.host = "127.0.0.1";
.port = "81";
}

sub vcl_recv {
 ...
 elsif (req.http.host ~ "^blog.mfabrik.com(:[0-9]+)?$") {
    set req.backend = backend_apache;
 }
 ...
}

Note that the backend IP is 127.0.0.1 (localhost). By default, with Debian or Ubuntu Linux, Apache configuration does not do virtual hosting for this.

So if /etc/apache2/sites-enabled/blog.mfabrik.com looks like:

<VirtualHost *:81>

 ServerName blog.mfabrik.com
 ...
 LogFormat       combined
 TransferLog     /var/log/apache2/blog.mfabrik.com.log

 ...

 ExpiresActive On
 ExpiresByType image/gif A3600
 ExpiresByType image/png A3600
 ExpiresByType image/image/vnd.microsoft.icon A3600
 ExpiresByType image/jpeg A3600
 ExpiresByType text/css A3600
 ExpiresByType text/javascript A3600
 ExpiresByType application/x-javascript A3600
</VirtualHost>

And now the trick – you need to add the following to /etc/apache2/httpd.conf

NameVirtualHost *:81

Unless you do all this, Apache will just pick the first virtualhost file in /etc/apache2/sites-enabled and use it for all requests.

Also you need to edit ports.conf and change Apache to listen to port 81:

Listen 81

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Native mobile application development with Plone, WordPress and Python

Posted on February 9, 2011

We have just released two mobile applications backed by Plone, WordPress and Python middleware code. In this blog post I’ll tell some background information what we have learnt with mobile applicationand Python development.

mFabrik News – download now for iPhone and Android

Why create a mobile application?

The first question is why one rather create a mobile application when the same task can be accomplished with a mobile site? Most people even prefer mobile sites over applications. From a pure engineering viewpoint, mobile applications are usually just glorified RSS readers that embed Webkit and add some native user interface bling bling over it. With an app, you are limiting your target audience, because an application is limited to one platform. Maintaining application(s) and application developers is more expensive compared to a mobile site which few (cheap) PHP junkies can throw together.

But is not always technology or price which matters. Mobile applications have prestige value – having or showing success, rank, wealth, etc. If you have a high quality brand, you probably want to have a mobile application too. When you see the brand logo swinging forth and back in an iPhone application with smooth animation running 60 frames per second, you see that it is a proper placement for the brand logo. The output is more luxury, more carefully planned, and does not look like it was thrown together by few cheap web developers.

There are even rationale reason for going after applications. First, you are in a business of making money. It is a lot of easier when the platform itself is offering you a payment solution without a monthly fees (iTunes payment). Other good reason is that there exists interaction between the application and your content beyond the browser window.  You can push messages or do things even if the user is not on your site (see more information about the push solution we implemented below).

Mobile application development and Python

As most of this post readers are probably fellow Python developers, here are some thoughts specifically aimed for them. Python itself is not a very good alternative what comes to mobile application development. Though, the application itself may not contain Python code, Python still shines on the backend side of the things. For example, we’ll hope to publish an example application using Google App Engine in the near future.

The only future proof platform where Python is 1st class citizen for building applications, is Nokia’s Meego with its Pyside and Qt bindings. Unfortunately Meego doesn’t have any shipped handsets and looks like it never will.

Android has script bindings, but they are not good enough for real application development, as interaction with the native platform happens over TCP/IP sockets. However, Android has seen some recent exciting development from PyPy project, possibly enabling native Android development for Python in the future.

iOS with Python could be a go, now when Apple has lift ban on interpreted languages. I haven’t heard anybody doing it yet, though. CTypes had some problems long time ago regarding run-time generated code for Python bindings.

Python has also a port for Series 60 (Symbian) – don’t go there if you are not prototyping. It is good platform for students for  playing around, but unfortunately it has never been considered as serious development environment by the handset manufacturer. You have tons of headaches if you actually want to release a product version of your application.  Nokia N900, soon supported. is better prototyping platform for Python than Series 60 as you get full Debian userland.

Mobile application development and wrappers

There exist various wrapper technologies which help you to wrap your HTML5 application to a native application shell. With simplistic APIs provided through Javascript bindings, you can access a limited subset of native platform APIs. Wrapper technologies are mostly aimed for web developers, who do not have any experience on application development and they might want to skip the learn experience of native development.

Wrapper technologies do their job and produce decent apps. But if you are a Python developer I recommend you skip the wrapper step and build your own native user interface and embed Webkit yourself. Designing an user interface is much is easier with Apple’s Interface Builder or Google’s  Android tools than with half-baked Javascript bindings. The fact that you are actually able to insert a real breakpoint into your code is itself worth of skipping wrappers. If you already are a Python developer you already know at least one real programming language and mastering Objective-C or Java should be an easy task for you.

Webkit itself has bugs. You will regularly hit obscrure bugs when the amount of  Javascript and CSS code grows. In the worst cases Webkit just dies under your application without a way to debug the problem – sometimes without a workaround available for the problem. This means dead end for your lovely application. You don’t want to end up to this situation. So, just to have more low level control, using native tools is good.

mFabrik News application

mFabrik News mobile application allows you to follow the latest news of mobile and web development, produced by our hacking team. The applications source the news from our Plone based web site and WordPress blog (which you are currently reading). It uses special RSS streams prepared with our Web and Mobile multichannel publishing solution: news images are optimized for mobile device screens using a handset database (Wurfl) and some other HTML preproessing is done to make the posts look better in embedded WebKit. Processing is done using mobile.sniffer and mobile.htmlprocessing Python packages which are generic Python packages and should be usable in various environments, including App Engine.

iOS mFabrik News application has push notification support. Android doesn’t yet implement push solution, but it is coming for Android 2.2 handsets.  Please see the earlier blog post how we use Apple Push Notifications with Python.

Download, give the apps a spin and report any feedback! (direct links at the beginning of the post)

We may or may not release the source code of the applications, depending if anybody thinks they actually would find it useful.

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Apple push notifications (APN) with Python

Posted on January 29, 2011

We have created a middleware service which inputs RSS feeds and outputs Apple Push Notification. This allows integrate push notification support for your existing content management system easily. This blog post should give you some ideas if you are planning to create similar services.

To have the über-experience of customer engagement with mobile push notifications you need

  • A mobile application (iOS, Android 2.2)
  • RSS feed to notifications middleware server (our solution)
  • RSS feeds themselves
  • Windows/UNIX server running the middleware

How it is put together

Tornado web server is used to handle incoming HTTP requests in scalable manner.

feedparser library fetches RSS feeds and processes them to client notifications.

BitReader (post, source) library is used to create messages to interact with Apple push notification service (APNs). The protocol is bit based protocol running directly over TCP/IP. Apple service has been designed to handle high volumes of traffic – it does not use anything like stateless HTTP to waste bandwidth.

Django models are used to store the state of each individual subscriber. Django’s ORM abstraction allows us to use the same middleware for small distributions (< 1000 clients, SQLite database) or big ones (millions of clients, MySQL database). The stored state information includes the subscriber id and the current badge number – the red circle on the app icon showing the count unread posts. When the application is launched, it can decrease its badge number by doing a HTTP call to the server.

Django settings are used to put together required certificates and whether the application is run in sandbox mode.

Walkthrough

There is a core IO loop, running in a separate process, called stream observer. This loops updates fetches RSS feeds’ status and passes updates to Tornado server over HTTP.  With this arrangement, any HTTP capable client can send push notifications.

Tornado handles incoming updates, updates the related subscribe status – how many unread notifications, etc. through exposed Django views. The notification is formatted according to the variables available on the subscriber mobile platform. In Apple’s case, the notification message gets title, badge, sound and a launch image. Payload is checked against hard 256 byte limit.

Then the payload is pushed to Apple servers over TCP/IP protocol. SSL certificaties needed.

A subscriber is registered  when the mobile application is launched. The application asks a subscriber id from Apple servers. Then, this subscriber id is delivered to our middleware over normal HTTP call.

The middleware also handles feedback service which gives you list of devices which have unsubscribed from your service. This way you can cut off notifications from unsubscribed clients. This is also done using BitReader and TCP/IP.

Future

The architecture is built so that different push backends can be included in the service. Android support is on the roadmap and we probably will have Blackberry and Meego support (when/if Nokia announces such a service).

We have currently tested this solution with RSS streams from WordPress and Plone.

We may release source code when it’s ready.

More info

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter