- I have seen some benchmarks that conclude that Metabase is slow. Is it true that Metabase is slow?
No. Metabase is not inherently slow. The benchmarks that conclude that Metabase is slow are usually cooked up by the authors of other database abstraction packages. So it is only natural that the benchmark tests are intentionally distorted to prove that those other abstractions are better. That is a trick used by those others in order to attract less informed users that are unable to verify the facts.
Often those benchmarks employ tricks like using the slowest methods to call Metabase API.
Also those benchmarks just perform many repeated SQL SELECT queries to the same table, which makes the database server hit the same query cache or file system caches. That makes the queries take much less time to execute than in real world situations.
In real world database applications many read and write accesses to multiple database tables happen concurrently. This puts database clients on hold most of the time, regardless how fast such database client APIs can be.
Consequently, these forges benchmark tests make minor performance differences between each database API appear to be much more noticeable then they really are for most database applications. However, those minor differences are necessary to let Metabase provide a greater level of database independence, and usually those tests are only performed with a single type of database.
- My database is having too many connections and my server is too slow. What can I do to avoid this?
This situation usually happens when you are using a multi-process Web server, like Apache 1.x, and your applications use persistent connections.
It may happen even when your site is not too busy, but the site pages use too much static content items, like images or CSS style sheets files.
What happens is that when your Web server is having a surge with many users accessing your site simultaneously, Apache needs to fork more processes to deal with the surge.
Each new process may eventually create a new database connection. Each database connection may consume a lot of memory. When the physical memory is exhausted, your server starts using virtual memory and the server becomes very slow.
You could use non-persistent database connections, but that would slow down the database access because the re-connection overhead is often very high.
Alternatively, you can use a connection pool manager tool. That is a separate middleware that sits between the Web server and the database. It can establish upto a limited number of database connections and forwards the connection requests and the responses. After a surge, it can shutdown unused connections to save memory.
There are connection pool managers that work with PHP like SQL Relay. This solution works but obviously it adds permanent overhead because all information exchanged between the database client and the server has to be forwarded by the connection pool manager middleware, making it all a bit slower.
Since the excessive number of Web processes is often caused by the high number of static content (images and CSS) files that is served by Apache, a more efficient solution consists in serving static content by a separate Web server.
Static content can be served more efficiently by a multi-threaded Web server. Multi-threaded Web servers take much less memory to run and can serve many concurrent accesses. Examples of Web servers that can run in multi-threaded mode are Apache 2 and thttpd.
The separate static content server could run on the same machine as the main Web server, as long as it runs on a different TCP port or IP address. Serving content on other port than the HTTP default port (80) is not the most recommended solution because some institutions set their firewalls to block any traffic to non-standard ports. Therefore it is always recommended to use a spare IP address, if possible.
Since when you move the static content to a separate Web server, your main Apache 1.x will only be serving pages generated by your PHP database driven application scripts. It will not start more persistent database connections than the limit of processes allowed by Apache.
To make sure your physical memory is not exhausted with excessive database connections, you should adjust Apache configuration to a reasonable limit number of processes. That is done setting the MaxClients Apache configuration option. You should also disable the option KeepAlive.
To make sure that the number of running processes returns to a reasonable number a while after a surge of accesses, you should also adjust the values of the options MaxSpareServers, StartServers and MinSpareServers. Here is an example of Apache configuration option values:
