{"id":148,"date":"2021-04-28T21:47:40","date_gmt":"2021-04-28T21:47:40","guid":{"rendered":"https:\/\/focalx.com\/blog\/?p=148"},"modified":"2021-04-28T21:52:28","modified_gmt":"2021-04-28T21:52:28","slug":"apache-mysql-optimization-for-aws-micro-instance-or-other-low-resource-vps","status":"publish","type":"post","link":"https:\/\/focalx.com\/blog\/2021\/04\/apache-mysql-optimization-for-aws-micro-instance-or-other-low-resource-vps\/","title":{"rendered":"Apache\/MySQL Optimization for AWS Micro Instance (or other low resource VPS)"},"content":{"rendered":"\n

Amazon Web Services EC2 micro instance<\/strong> is available within its first year free tier. This instance type is a good way to start building your LAMP server for website or web application. However, it is possessed very limited resources, especially in the main memory category, where even the Apache and MySQL (mariaDB) default settings might be too taxing, and it could result in occasional stall and out of memory error. Of course, micro instance usually should not be the best option to be used in production environment in most scenario. However, this does not mean we cannot use it in pre-production setup and use it even in early production stage where traffic is still slow. Here is a few tips that might help to get the most of out the micro instance before the need to opt for higher tier instance types.<\/p>\n\n\n\n

Micro instance has only 1GB memory. There isn’t normally a swap disk volume set up as well. And you probably would have it serving both the web service (Apache) and database service (MySQL\/mariaDB). For CPU, both t2 and t3 instances allowing using CPU credit for occasional burst. Therefore, CPU power usually would not be the immediate culprit, but the memory. So, the key part is to limit these 2 services memory usage and optimizing them to get most out of the limited 1GB space.<\/p>\n\n\n\n

<\/div>\n\n\n\n

Optimizing Apache Pre-fork configuration<\/h2>\n\n\n\n

Recommendation Configuration<\/h4>\n\n\n\n

In Linux, by default, Apache would be configured to use Pre-fork MPM. There are obviously pros and cons between different types of MPM. To minimizing the complexity, we stick with pre-fork setup and try to work within it.<\/p>\n\n\n\n

Here is the configuration I recommend:<\/p>\n\n\n\n

StartServers           1\nMinSpareServers        1\nMaxSpareServers        3\nMaxRequestWorkers      10\nMaxClients             10\nServerLimit            10\nMaxConnectionsPerChild 3000<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
MaxClients and ServerLimit<\/h4>\n\n\n\n
The key point is to limit the MaxClients <\/em><\/strong>and ServerLimit <\/em><\/strong>to a relative small numbers. The default values of both are at 256. However, a typical hardworking Apache process might be around 50MB to 75MB (varies between different types of web application). 10 of these processes would take 3\/4 of your 1GB memory. You also want to leave room for other processes, specifically MySQL). If you find your average memory usage of your apache process is even higher, you should even lower these 2 numbers accordingly.<\/p>\n\n\n\n
Limiting the MaxClients<\/em> <\/em><\/strong>and ServerLimit<\/em> would, by all means, limit the number of simultaneous active connections to serve the HTTP requests. But rather than you let your Apache processes to spawn out of control and eat up all your main memory, you keep them under a reasonable limit, and let the HTTP requests queuing up orderly for service. This would usually have a better server response rather than letting them hold up all your machine resources for minutes. Do remember MySQL service is also competing the memory resource on the side.<\/p>\n\n\n\n
Something worth to mention regarding Apache modules. The average memory usage of an Apache process is highly depending on the Apache modules it was configured to load. By default, Apache usually loads more modules than you need. This is especially significant for Pre-fork MPM as each process loads its own modules without ‘sharing’. However, which modules you need (or do not need) are highly depending on your web application. There is no work-for-all configuration I can recommend for you. If you really want to squeeze the last bit out of your micro instance, you should do some additional research, and disable some unused Apache modules. This definitely would help lower the average memory usage, and let you up the MaxClients<\/em> and ServerLimit<\/em> values a little more to allow your web service handling more HTTP requests simultaneously.<\/p>\n\n\n\n
<\/div>\n\n\n\n
Optmizing MySQL\/mariaDB configuration<\/h2>\n\n\n\n
Recommendation Configuration<\/h4>\n\n\n\n
I assume you are using InnoDB, as this seems to be the go-for choice these days. MyISAM has certain advantage in some scenario. This could benefit you in you scenario as well as you can simply disable InnoDB globally which saves a little bit of resources. (Note you can’t do it disable MyISAM globally).  But InnoDB does provide some features that MyISAM does not. I am not going to argue and compare the two here, which is definitely out of scope in this article. <\/p>\n\n\n\n
Here the configuration I would recommend.<\/p>\n\n\n\n
sort_buffer_size = 64K\njoin_buffer_size = 512K\nread_buffer_size = 256K\nmax_connections = 64\nkey_buffer_size = 16K\ntable_cache = 4\n\ninnodb_buffer_pool_size = 16M\ninnodb_additional_mem_pool_size = 2M\ninnodb_log_file_size = 5M\ninnodb_log_buffer_size = 8M\ninnodb_flush_log_at_trx_commit = 1\ninnodb_lock_wait_timeout = 50\n\n<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
key_buffer_size<\/h4>\n\n\n\n
As we are only using InnoDB, key_buffer_size<\/em> can be kept small as it is only used by MyISAM to cache its indexes. However, depending on the MySQL\/mariaDB version you are running, some system tables, e.g. users and permission tables, might still be in MyISAM. So, keeping key_buffer_size<\/em> to a reasonable small size is still necessary.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Total Possible Buffer Size and Max Connections<\/h4>\n\n\n\n
An important key setup here for low memory setting is to control the max_connections<\/em> and the various buffer sizes. <\/p>\n\n\n\n
Total Possible Buffer Size = Global Buffer Size + (Thread Buffer Size * Max Connections)<\/code><\/pre>\n\n\n\n
Global buffer is coming from key_buffer_size<\/em>, innodb_buffer_pool_size<\/em>, innodb_log_buffer_size<\/em>, innodb_additional_mem_pool_size<\/em>, net_buffer_size<\/em> and query_cache_size<\/em>. You can fine tune some of these values to suite your application needs. As they are global, rather than thread based, it is more flexible to tune them up and down without severely affecting the memory usage.<\/p>\n\n\n\n
On the other hand, thread buffer is coming from sort_buffer_size<\/em>, myisam_sort_buffer_size<\/em>, read_buffer_size<\/em>, join_buffer_size<\/em>, read_rnd_buffer_size<\/em>, and thread_stack<\/em>. Keeping these values reasonably low are important, as these values affecting memory usage for each thread. <\/p>\n\n\n\n
max_connections <\/em>default value is 151. This value controls the maximum number of threads running, hence theoretically the maximum thread buffer size MySQL could reached. So, max_connections could be roughly calculated like this.<\/p>\n\n\n\n
Max Connections = (Available Memory - Global Buffer Size) \/ Thread Buffer Size<\/code><\/pre>\n\n\n\n
You can see this is just a reversed calculation of the previous formula. The key point is the ‘Available Memory’. Since your Apache configuration in previous section leaves about 1\/4 of the main memory, the available memory size for MySQL would be below 1\/4 of the main memory (Note that there are other system processes running as well). I use 64 in my recommendation. You should adjust this up or down a little to accommodate your specific setup and application type of your server.<\/p>\n\n\n\n
<\/p>\n\n\n\n
table_cache<\/h4>\n\n\n\n
MySQL default table_cache<\/em> is 64. Every time you access a table, MySQL will load a reference into the table cache, and also a open file handler in the OS. Table cache would help your MySQL performance but also would use up the memory. Not hitting table cache would take some toll on the performance, but is not the end of the world. But I would not recommend to set it too low. 4 would be good start. Gradually increasing the value and see how it would affect you memory usage. The goal is to strike a balance between memory usage and the performance.<\/p>\n\n\n\n
<\/div>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Low resources in micro server post some challenge when setting up your LAMP stack. I tried to put out some general configuration in this article so that you can use it as a good start. Every server and the application you serve is different and hence the configuration, especially the MySQL configuration which known to give a lot of room for fine-tuning to suite your application needs. Tools like tuning-primer.sh<\/a>\u00a0and\u00a0mysqltuner.pl<\/a> would really help you on this aspect.<\/p>\n\n\n\n
On the other hand, if you find yourself overwhelmed by these server tune-up tasks, please do not hesitate to contact me at info@focalx.com<\/a>, and I am happy to discussion your situation further.<\/p>\n","protected":false},"excerpt":{"rendered":"
Amazon Web Services EC2 micro instance is available within its first year free tier. This instance type is a good way to start building your LAMP server for website or web application. However, it is possessed very limited resources, especially in the main memory category, where even the Apache and […]<\/p>\n","protected":false},"author":1,"featured_media":149,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[26],"tags":[3,23,29,25,28],"_links":{"self":[{"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/posts\/148"}],"collection":[{"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/comments?post=148"}],"version-history":[{"count":9,"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/posts\/148\/revisions"}],"predecessor-version":[{"id":170,"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/posts\/148\/revisions\/170"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/media\/149"}],"wp:attachment":[{"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/media?parent=148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/categories?post=148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/focalx.com\/blog\/wp-json\/wp\/v2\/tags?post=148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}