Hi,
I want to share a few tips and tricks how to profile and optimize our database usage. In general database queries are very bad and should be avoided if possible. The reasons for that are: - Roundtrips: Bigger ownCloud installations have the database not installed on the application server but on a remote dedicated database server. The problem is that database queries than go over the network. This roundtrips can add up significantly if you have a lot of queries. - Speed. A lot of people think that databases are fast. This is not always true if you compare it with handing data internally in PHP or in the filesystem or even use key/value based storages. So every developer should always double check if the database is really the best place for the data. - Scalability. If you have a big ownCloud cluster setup than you usually have several ownCloud/webservers in parallel and a central database and a central storage. This mean that everything that happens on the ownCloud/PHP side can parallelize and can be scaled. Stuff that is happening in the database and in the storage is critical because it only exists once and can't be scaled so easily. Se we should always reduce the load on the database by: 1. Make sure that always an index is used. 2. Reduce the overall numbers of queries. There a several ways to monitor which queries are actually executed on the database. With MySQL is is very easy just with a bit of configuration: 1. Slow query log If you put this into your my.cnf file than every query that takes longer than one second is logged to a logfile. log_slow_queries = 1 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time=1 If a query takes over one second than we have a serious problem of course. You can watch it with "tail -f /var/log/mysql/mysql-slow.log" while using ownCloud. 2. log all queries If you reduce the long_query_time to zero than every statement is logged. This is super helpful to see what is going on. Just do a tail -f on the logfile and click around in the interface or access the WebDAV interface. log_slow_queries = 1 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time=0 I find this quite eye-opening. We have some low hanging fruits here to fix. 3. log queries without an index. If you increase the long_query_time to 100 and add log-queries-not-using-indexes than all the queries that are not using an index are logged. Every query should always use an index. So ideally there should be no output. log-queries-not-using-indexes log_slow_queries = 1 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time=100 Measure the performance: If you do bigger changes in the architecture or the the database structure than you should always double check the positive or negative performance impact. There are a few nice small scripts than can be used: https://github.com/owncloud/administration/tree/master/performance-tests The recommendation is to automatically do 10000 PROPFINDs or file uploads, measure the time and compare the time before and after the change. Let me know if you have any questions. Does someone else has any useful tips how to profile and analyze out database layer than please share it here. Frank _______________________________________________ Devel mailing list [email protected] http://mailman.owncloud.org/mailman/listinfo/devel
