my.cnf and apc optimization

[jair101]

Again few questions from me. To start, this is my VPS: 1GB Ram, 2CPU cores. My community is about 1700 uniques per day, top activity 100 online including bots. There is another wordpress blog on the server, but it is not very active and I have W3Total Cache, which I hope helps. Anyway, back to my IPS community.

First, the APC cache. Everything under fcgid works good, unfortunately due to the fact that it need a separate cache for each process I am starting to run out of memory. I need about 80MB of RAM for APC, is that good or bad, how it is compared to you? I have quite a few extra apps - content, gallery, tutorials, video, faq. I started with 64MB, but noticed that "cache full count" was quite high, increased it to 80MB, now its much better (value of 3 for 10 hours). However, normally my website runs at 3 php processes, which means 240MB or 25% of my RAM go to APC cache. It will be even worse if another process is spawned. DSO is better with cache, but it is more memory intensive, so I don't know if switching will help. Basically I just want to know what is your APC cache size, if 80MB is abnormal or typical for website with many apps like mine?

Second is mysql. This is a bit of a dark matter to me, here are the results from mysqltuner:

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0ubuntu0.12.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 178M (Tables: 238)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 23


-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 0h 24m 10s (1M q [5.892 qps], 51K conn, TX: 14B, RX: 331M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 32.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 277.4M (27% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/58.0M
[OK] Key buffer hit rate: 99.3% (25M cached / 179K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (51 temp sorts / 48K sorts)
[OK] Temporary tables created on disk: 17% (13K on disk / 79K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 24K opened)
[OK] Open file limit used: 12% (128/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    table_cache (> 64)

I understand the recommendations, but reading on this forum I noticed that enabling query_cache might not be a good idea for complex applications such as IPS (I think Kirito mentioned this). High table cache is also not recommended, but should I disable it completely? Thread cache is completely unknown concept to me so far. Can you recommend me some useful values that will hopefully help my mysql, but won't break my RAM bank?

This is the relevant part of my.cnf:

key_buffer = 16M
max_allowed_packet = 1M
myisam-recover         = BACKUP
query_cache_limit       = 1M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M
myisam_sort_buffer_size = 8M
read_rnd_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
sort_buffer_size = 512K
table_cache = 64

So basically, I am trying to perfect my setup, but without runnung out of resources. It seems that high speed is not inversely proportional to low load. Upgrading the VPS is out of question for this year for various reasons, but it shouldn't be needed if my website is properly optimized I think. There are times when my server runs at just 150MB free RAM and that makes me nervous.

[Grumpy]

which means 240MB or 25% of my RAM go to APC cache.

I'm not really understanding why this is being stated like a bad thing. Can you provide your top output? How much ram are you left with?

I need about 80MB of RAM for APC, is that good or bad, how it is compared to you?I need about 80MB of RAM for APC, is that good or bad, how it is compared to you?

Well, my guess was that you'd use 50MB at most. Well, wasn't too far off... lol But can't really compare to mine. Since I reprogrammed some caching modules in ipb for mine... and my only active forum size isn't really comparable either (I get millions of views/day).


You might want to leave aside mysql tuning until you got a good sense of how much ram you can manage. Also, feel free to turn on caching and experimenting. It's not like you turn it on and gets broken. You can always just switch stuff back. Experimenting is the best teacher to tuning.

[jair101]

I'm not really understanding why this is being stated like a bad thing. Can you provide your top output? How much ram are you left with?

At peak times I have 150-160MB left and I fear if an aggresive scrapper hits my site, which happened once in a while on the shared hosting, it will kill it completely.

Thanks for the reply though, will try playing with mysql settings in the weekend.

[Makoto]

First thing, switch to PHP-FPM.

PHP-FPM does not suffer from FCGID's many limitations, such as needing a separate cache for every single PHP process spawned.

I have APC limited to a 128MB shared memory pool size. I "use" almost all of that with a 99.9% cache hit rate. However, tuning APC's SHM size usually involves a bit of trial and error if you want to squeeze as much memory as you can out of your configuration while still getting the best performance possible. At the default limitation of 64MB, my cache hit rate was only ~65% if memory serves me well. I doubled this to 128MB, and now my cache hit rate is essentially 100%. This means that I could probably safely lower this to maybe 96MB and still be able to maintain that ~100% cache hit rate, but I have plenty of memory to spare on my server, so I try and give everything a little breathing room to scale.

APC offers an extremely useful diagnostics script to help you tune these settings appropriately. If you're not already using it, you can get it by downloading the latest stable release of APC and uploading the "apc.php" file to your server.

http://pecl.php.net/package/APC

[jair101]

Kirito, the web panel I am using - Virtualmin does not support PHP-FPM and to install it apparently is a bit more advanced then my abilities.

Yeah, I am following the statistics in apc.php closely. Thats where I noticed that 64MB is not enough, 80 is much better, but still occasionall cache full pruning. I will try to bump it to 100.

Another weird issue, I suppose directly related to APC caching. Sometimes the number of members that joined the community varies between each refresh of the index page. So it says: 1100 total member, last one is Member3. Refresh the page and it says: 1105 total members, last one is Member 2. Another refresh: 1096 total members, last one is Member 1. Etc. Could this be because it depends on which fcgid process serves the index page in the sense that they have different caches? While this is not a big deal, I suppose such differences could cause problems in more sensitive areas of the forum. Is it possible that some people won't see the latest replies because of that? From what I understand opcode cache just caches the php output, so maybe user cache is responsible for that.

[Makoto]

Another weird issue, I suppose directly related to APC caching. Sometimes the number of members that joined the community varies between each refresh of the index page. So it says: 1100 total member, last one is Member3. Refresh the page and it says: 1105 total members, last one is Member 2. Another refresh: 1096 total members, last one is Member 1. Etc. Could this be because it depends on which fcgid process serves the index page in the sense that they have different caches? While this is not a big deal, I suppose such differences could cause problems in more sensitive areas of the forum. Is it possible that some people won't see the latest replies because of that? From what I understand opcode cache just caches the php output, so maybe user cache is responsible for that.

I'm actually not sure if any significant problems could be caused by this, but using fcgid with APC is very likely responsible for what you're seeing. APC isn't meant to be split for the same website like that. It's called a shared memory pool for a reason, after all. This also means cache has to be independently generated and maintained for every single pool you have. When you have a lower traffic website that only requires a few PHP processes at most, this isn't terrible, but it's still pretty bad and terribly inefficient.

That's why I so strongly recommend trying to opt for using PHP-FPM if you at all can. It's worlds better than having to rely on the old fcgid method. I'd love to recommend you outright abandon using web panels so you never have to worry about these restrictions again, but taking that leap and learning enough to become a "true" systems administrator can require a lot of time, patience and effort. If this website is just a side hobby for you, it probably won't be worth your time. But if you're "geeky" and patient enough to really want to learn, it can pay off for you in the long run. Being that you already manage your own VPS to some extent and know enough to tune services like MySQL and APC, I have no doubt you're capable of learning how to set up a web server from scratch, you just have to be willing.

Yeah, I am following the statistics in apc.php closely. Thats where I noticed that 64MB is not enough, 80 is much better, but still occasionall cache full pruning. I will try to bump it to 100.

Just keep in mind your cache hit rate is what's really important, not the amount of free memory APC has available. The SHM pool can fill free space up with data that is rarely accessed when it has the memory to spare, but this won't offer you much if any benefit. What you really want is to maintain a high cache hit rate above all else. It's okay if the SHM pool fills up, it probably will inevitably unless you give an excessive amount of memory.

[jair101]

Thanks. I think my next course of action will be to switch to mod_php and see how it goes with the resources. At least the multiple cache issue won't exist. Maybe I will buy another VPS for 1-2 months just for experiments to do everything from scratch, but don't want to do my tests on my live community for obvious reasons :)

I am willing to learn, but I just don't have the time right now. I thought people use these web panels for ease of use, turned out they create way more headaches.

99.2% hit rate, I suppose thats fine.

[Makoto]

A 99.2% hit rate should be perfectly acceptable. Maybe monitor the value here and there and make adjustments if necessary, but it seems like you may have a good balance right now.

[Makoto]

On another note (sorry for the double post, just want to make sure you read this), mod_php will resolve your APC issues, but it also does introduce its own problems with efficiency.

The major problem with mod_php is the fact that PHP has to be loaded with every single request made to your web server, even simple requests to small static resources. So if you're constrained on memory, you just have to be sure you don't allow Apache to handle too many concurrent connections, else mod_php could quickly consume all your servers available memory (especially in the case of a DoS attack).

Otherwise, I'd say it still is a better solution than using fcgid if you want to take advantage of APC caching.

[jair101]

Yeah, Grumpy said the same about mod_php and resources in my previous topic. I guess I will just try and see how it goes. Unfortunately Virtualmin does not support Nginx+apache, so that requires a bit of hacking too if I want to use it to somehow help the load.

[Makoto]

If you wanted to opt for using Nginx, I would recommend just using straight up Nginx and PHP-FPM. The two work very well together.

Nginx is an overall spectacular very high performance, high efficiency web server, and PHP-FPM is great for managing PHP applications. It's actually very easy to set up once you get the gist of it. You just tune a few settings in PHP-FPM, start the daemon, and tell Nginx to pass all PHP page requests to PHP-FPM. It's not really much different from using fcgid, but it is so much better.

[handsoffsam]

+1 on the nginx+php-fpm combination. Very flexible. Separation of duties is almost always a good thing.

robert

[Grumpy]

If apc+fcgi is creating problems for you in the user data part, I have a separate suggestion for you that should be fairly simple.

Install memcached (the daemon) and memcache (the php plugin. or memcached, newer php plugin... but i'll write memcache b/c it's completely confusing enough as is). I highlight that these are two separate things as people often miss them when installing for the first time. Then set your IPB user caching to memcache. There's no separate setting in IPB for user/op cache. The one in ipb setting is just for user cache. This should allow you to use a single point storage for user data all the while using apc for your op cache. This should lower your ram requirements as well as stat separation.

As to your question of if cache separation is creating any major problems... I would hope that ipb dev team did a good enough job that it wouldn't create major problems (like invisible latest for example). Especially since they support multiple memcached installations. Though... you never really know.

[jair101]

I just read about memcached, memcache and memcached, it is indeed confusing naming, but the explanation is simple enough :smile: However, it seems that memcache(d) is better for multiple servers and really lacks on single VPS. On top of that the memory footprint of my user cache with APC is not that big:

User Cache Information

Cached Variables 54 ( 1.1 MBytes) Hits 125187 Misses 1291

I'll keep monitoring for other weird issues that might be related to multiple APC user caches, but so far I don't think it is worth it to switch.

But I am convinced that my next job will be to setup a server from ground up to nginx+php-fpm. These control panels seem to give more hassle then help if you really want to fine tune your server.

[Grumpy]

These control panels seem to give more hassle then help if you really want to fine tune your server.

That is precisely why any fine tuned server doesn't have a control panel. As nice as they are, adherence to two separate requirements is doubly hard.