17

I am tweaking my homepage for performance, currently it handles about 200 requests/second on 3.14.by which eats 6 SQL queries, and 20 req/second on 3.14.by/forum which is phpBB forum.

Strangely enough, numbers are about the same on some VPS and dedicated Atom 330 server.

Server software is the following: Apache2+mod_php prefork 4 childs (tried different numbers here), php5, APC, nginx, memcached for PHP sessions storage.

MySQL is configured to eat about 30% of available RAM (~150Mb on VPS, 700Mb on dedicated server)

This looks like there is a bottleneck somewhere not allowing me to go higher, any suggestions? (i.e. I know that doing less than 6 SQL's would make it faster, but this does not look like a limiting factor, as sqld eats no more than a few % in top due to cached queries)

Has anyone tested that kicking preforked apache2 and leaving just nginx+php is much faster?

Some more benchmarks

Small 40-byte static file: 1484 r/s via nginx+apache2, 2452 if we talk to apache2 directly. Small "Hello world" php script: 458 r/s via ngin+apache2.

Update: It appears bottleneck is MySQL performance on cached data. Page with single SQL shows 354req/sec, with 6 SQL's - 180 req/sec. What do you think I can tweak here? (I can fork out 100-200Mb for MySQL)

[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] default-character-set=cp1251 collation-server=cp1251_general_cs skip-character-set-client-handshake user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking bind-address = 127.0.0.1 key_buffer = 16M max_allowed_packet = 8M thread_stack = 64K thread_cache_size = 16 sort_buffer_size = 8M read_buffer_size = 1M myisam-recover = BACKUP max_connections = 650 table_cache = 256 thread_concurrency = 10 query_cache_limit = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 8M [mysql] [isamchk] key_buffer = 8M !includedir /etc/mysql/conf.d/
Improve this question
5
  • Why are you using both Apache and nginx? Commented Feb 18, 2010 at 0:54
  • That is common configuration, Apache2 to PHP and various apps requiring apache infrastructure, nginx to reduce apache2 memory footprint on load. Commented Feb 18, 2010 at 0:57
  • Actually, I'm not understanding your problem. Is your site currently slow? If so, how slow is it? And how much do you wish to speed it up? Have you attempted to profile any portions of your site to determine where the bottleneck is? Commented Feb 18, 2010 at 1:00
  • It's in the description: Now it's on 180-200 requests/second. While this is way more then enough for a homepage, I want to tweak this setup to have other sites built on same codebase work faster. Ideally I want to saturate 100Mbit connection with dynamic pages :-) Commented Feb 18, 2010 at 1:06
  • 2
    "Requests per second" isn't really a meaningful metric in this context. My netbook could handle "200 requests per second". You need to tell us what response time you're wanting to achieve under that kind of connection rate. Commented Feb 18, 2010 at 1:16

8 Answers 8

Reset to default
30
+75

Obviously, there is a lot you can try. Your best bet is chasing your logs for queries that don't use indexes (enable logs for those) and other non-optimized queries. I have compiled a huge list of performance related options over the years, so I've included a small subset here for your information - hopefully it helps. Here are some general notes for things you can try (if you haven't already):

MySQL

  • query_cache_type=1 - cache SQL queries is on. If set to 2, queries are only cached if the SQL_CACHE hint is passed to them. Similarly with type 1, you can disable cache for a particular query with the SQL_NO_CACHE hint
  • key_buffer_size=128M (default: 8M) - memory buffer for MyISAM table indexes. On dedicated servers, aim to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server
  • query_cache_size=64M (default: 0) - size of the query cache
  • back_log=100 (default: 50, max: 65535) - The queue of outstanding connection requests. Only matters when there are lots of connections in short time
  • join_buffer_size=1M (default: 131072) - a buffer that's used when having full table scans (no indexes)
  • table_cache=2048 (default: 256) - should be max_user_connections multiplied by the maximum number of JOINs your heaviest SQL query contains. Use the "open_tables" variable at peak times as a guide. Also look at the "opened_tables" variable - it should be close to "open_tables"
  • query_prealloc_size=32K (default: 8K) - persistant memory for statements parsing and execution. Increase if having complex queries
  • sort_buffer_size=16M (default: 2M) - helps with sorting (ORDER BY and GROUP BY operations)
  • read_buffer_size=2M (default: 128K) - Helps with sequential scans. Increase if there are many sequential scans.
  • read_rnd_buffer_size=4M - helps MyISAM table speed up read after sort
  • max_length_for_sort_data - row size to store instead of row pointer in sort file. Can avoid random table reads
  • key_cache_age_threshold=3000 (default: 300) - time to keep key cache in the hot-zone (before it's demoted to warm)
  • key_cache_division_limit=50 (default: 100) - enables a more sophisticated cache eviction mechanism (two levels). Denotes the percentage to keep for the bottom level. delay_key_write=ALL - the key buffer is not flushed for the table on every index update, but only when the table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the --myisam-recover=BACKUP,FORCE option
  • memlock=1 - lock process in memory (to reduce swapping in/out)

Apache

  • change the spawning method (to mpm for example)
  • disable logs if possible
  • AllowOverride None - whenever possible disable .htaccess. It stops apache for looking for .htaccess files if they are not used so it saves a file lookup request
  • SendBufferSize - Set to OS default. On congested networks, you should set this parameter close to the size of the largest file normally downloaded
  • KeepAlive Off (default On) - and install lingerd to properly close network connections and is faster
  • DirectoryIndex index.php - Keep file list as short and absolute as possible.
  • Options FollowSymLinks - to simplify file access process in Apache
  • Avoid using mod_rewrite or at least complex regexs
  • ServerToken=prod

PHP

  • variables_order="GPCS" (If you don't need environment variables)
  • register_globals=Off - apart from being a security risk, it also has a performance impact
  • Keep include_path as minimal as possible (avoids extra filesystem lookups)
  • display_errors=Off - Disable showing errors. Strongly recommended for all production servers (doesn't display ugly error messages in case of a problem).
  • magic_quotes_gpc=Off
  • magic_quotes_*=Off
  • output_buffering=On
  • Disable logging if possible
  • expose_php=Off
  • register_argc_argv=Off
  • always_populate_raw_post_data=Off
  • place php.ini file where php would look for it first.
  • session.gc_divisor=1000 or 10000
  • session.save_path = "N;/path" - For large sites consider using it. Splits session files into subdirectories

OS Tweaks

  • Mount used hard disks with the -o noatime option (no access time). Also add this option to /etc/fstab file.
  • Tweak the /proc/sys/vm/swappiness (from 0 to 100) to see what has best results
  • Use RAM Disks - mount --bind -ttmpfs /tmp /tmp
Improve this answer
4
  • That is nice list, I've had already majority of these, and when I added remaining things, performance haven't increased. Looks like bottleneck is somewhere between PHP and MySQL not being able to handle more than 800 requests per second from query cache... Commented Feb 24, 2010 at 13:24
  • Ok, how do you connect to the database (mysql_pconnect() instead of mysql_connect())? Do you use persistent connections? try both ways ... Commented Feb 24, 2010 at 13:30
  • I'm already on pconnect and connection pooling is enabled in php.ini... :-S Commented Feb 26, 2010 at 9:55
  • Just for the sace of completeness, I would try just connect. I've seen cases (especially in load testing) where that performs better. Commented Feb 27, 2010 at 0:56
1

If the bottleneck is not CPU, then its IO - either network or disc. So.. you need to see how much IO is going on. I wouldn't have thought its the network (unless you're on a 10mbps half-duplex link, but its worth checking the switch in case auto-detect isn't doing its job right).

That leaves disk IO, which can be a big factor especially on VPSs. Use sar or iostat to have a look at the disks, then google how to find more details if your disk is being used heavily.

Improve this answer
1
  • Yes, Network is not the problem - when running ab from local server, performance is just the same. I've checked iowait time - it's under 0,01% - basically everything is in disk cache, and there are no disk writes involved in processing request (all logs are disabled). Commented Feb 16, 2010 at 0:17
1

I would look into caching with either Nginx (memcached) or Varnish.

By the very least you should server static files with Nginx like SaveTheRbtz said.

Improve this answer
5
  • These are dynamic pages, so I would prefer to do not cache them. Commented Feb 17, 2010 at 11:15
  • 1
    memcached isn't a traditional caching app and can work wonders for dynamic pages. It sits between the DB and your app. You app first queries memcached for an object, if it's not there, then it's loaded from the DB. The net effect is that you're using RAM to serve your DB requests rather than the much slower persistent storage on the DB. Commented Feb 18, 2010 at 0:53
  • Memcache can be used with nginx, that is known feature. Slower persistent storage isn't used, it's all in query cache in MySQL. Commented Feb 18, 2010 at 1:09
  • Memcached and MySQL's query cache aren't really comparable; they don't even do the same thing. You're pretty quick to shoot down pretty much every suggestion posted here without bothering to understand them. I'd recommend that you be a bit more open minded. Commented Feb 19, 2010 at 4:04
  • I clearly understand the difference between memcached and MySQL query cache. But due to the fact that everything is in Query cache with 100% hit ratio, I wouldn't call it "slow persistent storage". Original yesterday's answer was about using NginX+Memcached which is quite common scenario to cache whole pages. Caching individual objects is another, totally different scenario. While using memcached in front of MySQL is on the table, I am thinking about getting more juice without it for now (as it would require quite a bit of code changes). Commented Feb 19, 2010 at 5:43
1

As the server doesn't seem to be problem, maybe the load generator is. Try to run it on it on several machines.

Improve this answer
1
  • Performance is the same even If I run it from the server itself. No matter how manu concurrent connections - 10 or 50. Load testing is does via ab -c 10 -t 10 Commented Feb 17, 2010 at 11:13
1

It sounds to me like you might be hitting the maximum amount of connections Apache lets in. Take a look at your Apache configuration. Increasing server limit and max clients should help if you are not already bound by some other limit like I/O or memory. Look at the values present for mpm_prefork_module or mpm_worker_module and adjust accordingly to meet your needs.

 ServerLimit 512 MaxClients 512
Improve this answer
8
  • Well, do I really need this provided that I have nginx in front of apache2, so I belive there is no much sence of having more than physical cores*2 Apache2 processes.... Commented Feb 18, 2010 at 0:26
  • Just verified this. Increasing number of Apache2 processes from 4 to 16 didn't improved performance at all (it even dropped by 0.5%). Increasing number of nginx workers to 2 or 4 didn't improved anything. Commented Feb 18, 2010 at 0:45
  • 1
    If your data is fairly static, i.e. it doesn't update every other page load, you could increase your query_cache. MySQL will hold onto the result set that way and pull from memory. However, if the table that is being cached receives any writes during that time, it invalidates the cache (even if the data isn't affected), making it wasted memory. Commented Feb 18, 2010 at 7:00
  • Right now I see 100% query cache hit ratio, and MySQL is still feels slow... Commented Feb 19, 2010 at 5:44
  • 1
    Add skip-name-resolve to your MySQL config file. That will save a DNS lookup on every connection to the server. The drawback here is that all connections will need to be locked by IP (assuming your not using '%'). If the SQL is on the same server and doesn't need to be accessed anywhere other than localhost, you could also add skip-networking to kill off the entire TCP/IP stack. However, I think the bottleneck is Apache. Commented Feb 19, 2010 at 18:20
0

Is this load generated by a tool or real-world loads?

You may want to check memcached. I've seen issues at high connection rates of it causing latency in the application.

If using a load generator, what do you get when hitting a small static page?

During the loads, you may want to check the network stack for TIME_WAIT conditions. Perhaps you are filling up your connection queue.

There's about a 100 more reasons and items you can look at but without more information, I'm just throwing out guesses at this point.

Improve this answer
4
  • It's tested via ab-c 10 -t 10 URL I am benchmarking from the server itself, so network shouldn't be the issue. I've posted more benchmarks per your request. Commented Feb 18, 2010 at 4:56
  • I would not spend too much effort tuning with ab. You may find it does not translate well into real world performance. What you may want to do is dissect your app and test each component. For example, hit the apache server directly with just a very small static page. This will give you and idea of your max req/sec on the backend. Put nginx in front, re-test calling the same backend file. Then test with a simple "hello world" type php page Sometimes all of the layers can mask something simple. Also, watch the connections during the test. Make sure your network stack is not filling up. Commented Feb 18, 2010 at 13:32
  • I did these benchmarks yesterday, and they are in the updated original question description. Also, tests are done on localhost, so network is not an issue. Commented Feb 19, 2010 at 5:46
  • Network can be an issue even when done on a local host. Not likely in your case but it can cause issues. At lest now you have an upper limit of ~450 req/sec with your current PHP setup. The next step is to drop in a database call and see how that changes. I like breaking this apart when doing high level tuning as it can really help you pinpoint the layer causing the most issues. Commented Feb 19, 2010 at 16:29
-1

99% percent of the time issues like this will trace back to the database. Make sure your hitting indexes first of all. If that doesn't work, start caching everything that you can.

Improve this answer
1
  • It is all indexes and as I was saying, it is even hitting MySQL query cache in 100% of cases Commented Feb 24, 2010 at 11:58
-1

I recommend you to use (if possible) a connection pooler to keep the database connected to your web applications (no needs to reconnect at each requests). That can make an huge difference of speed.

Also, try to analyse all your queries with EXPLAIN (and why not profile your queries with SHOW PROFILE ?).

Improve this answer
1
  • All queries use indexes. MySQL Connection pool is used. Commented Feb 24, 2010 at 12:24

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.