Kodi Community Forum
DB - SQLLite/MySQL - Performance issues - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Development (https://forum.kodi.tv/forumdisplay.php?fid=32)
+--- Forum: Kodi Application (https://forum.kodi.tv/forumdisplay.php?fid=93)
+--- Thread: DB - SQLLite/MySQL - Performance issues (/showthread.php?tid=199805)

Pages: 1 2 3 4 5 6 7


DB - SQLLite/MySQL - Performance issues - m.savazzi - 2014-07-12

Team,
it appears to me that the DB has some potential space for improvement.

Jus timporting my library from a single XBMC implementation MySQL reported those data as RED (aka not good):

Handler read rnd
362.9 k The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

Handler read rnd next
680.4 M The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Connections
22.9 k The number of connection attempts (successful or not) to the MySQL server.

Select full join
7 The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Slow queries
1.1 k The number of queries that have taken more than long_query_time seconds.Documentation

Sort merge passes
88 The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

Table locks waited
325 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

I think we need to seriously work on DB and Library to optimize this. The result can be outstanding.

Who is in charge of the DB part? how we can work on it?

M

I worte this as to import my library it takes more than 12 HOURS! reading all the information from local NFO files fully compiled and correct (from EmberMediaManager)...


RE: DB - SQLLite/MySQL - Performance issues - jmarshall - 2014-07-13

If it takes 12 hours you're doing something massively wrong or you have a gigantic collection.

It takes about an hour to scan my entire collection online (several thousand episodes, 500 odd movies) into a sqlite database. Obviously the vast majority of that is disk access and online queries, rather than database writes!

And as I wrote via PM, grab the source and feel free to take a look.

Note that mysql-specific improvements (i.e. stuff that makes no differences at all to sqlite) will unlikely to be accepted unless they're minimal impact.

Jonathan


RE: DB - SQLLite/MySQL - Performance issues - Tolriq - 2014-07-13

Do not know if this was changed, but when I tried mysql long ago one of the biggest problems was connections.

But it seems that it's still the case reading his post :
22.9 k The number of connection attempts (successful or not) to the MySQL server.

On mysql opening and closing connections is very very very slow and not efficient when using network based connection.

Don't know how internally all is handled in Xbmc, but keeping database connection open would be a major benefit. (And I'm pretty sure this could add some small gains to sqlite too)


RE: DB - SQLLite/MySQL - Performance issues - m.savazzi - 2014-07-13

Jonathan
Will look the code. Those numbers show a core issue on how data is managed that has a lower impact on "localhost" like sqlite and major impact on real network connections.

MySQL should be a major and core component as is the only solution to support multi endpoints.
sqlite cannot be used in that way.

m


RE: DB - SQLLite/MySQL - Performance issues - Montellese - 2014-07-14

MySQL was added as a hack for people who want to synchronise multiple XBMC instances and it is hardly supported. Only very few people in the team (and most of them are not devs) use a MySQL setup.

The whole database code has been written with SQLite in mind and there the number of "connections" doesn't really matter. Unfortunately moving to a mode where the connection is kept open would require a major rewrite of the database code.


RE: DB - SQLLite/MySQL - Performance issues - m.savazzi - 2014-07-14

Harg... this is quite bad. Anyway I can try to help and bring it to the next level.

Is there anyone leading the DB part I can try sync with?

M


RE: DB - SQLLite/MySQL - Performance issues - Martijn - 2014-07-14

Also keep in mind that mysql will eventually be dropped.


RE: DB - SQLLite/MySQL - Performance issues - Tolriq - 2014-07-14

You should really ask users about that, as from Yatse stats there's a lot (a lot) of users of multiple rpi installation with mysql backend.

And no UPnP sharing is not a viable long term solution for multiple installations.


Re: RE: DB - SQLLite/MySQL - Performance issues - Martijn - 2014-07-14

(2014-07-14, 14:32)Tolriq Wrote: You should really ask users about that

Nope.
We need to maintain it and no one wants to do that. This is a team decision.

This is not the topic to start all over again.


RE: DB - SQLLite/MySQL - Performance issues - da-anda - 2014-07-14

if somebody is going to touch the database code, best would be to implement a database abstraction layer (DBAL) and make the database connectors addons. But before doing so, interested party should get in touch with us to make sure it'll work out well with the upcoming features (UPNP client/server sharing)


RE: DB - SQLLite/MySQL - Performance issues - Memphiz - 2014-07-14

It won't be dropped without a suitable replacement - just to calm down the nerves a bit (Martijn you are so evil :p).


RE: DB - SQLLite/MySQL - Performance issues - Martijn - 2014-07-14

(2014-07-14, 16:23)Memphiz Wrote: It won't be dropped without a suitable replacement - just to calm down the nerves a bit (Martijn you are so evil :p).

I said "eventually" Cool


RE: DB - SQLLite/MySQL - Performance issues - m.savazzi - 2014-07-14

Guys,
the more XBMC gets on devices (pc, ARM, NAS, etc...) the more the need to share the info becomes crucial.
uPNP is not a feasible solution as it needs to have both devices on and is a streaming format. On the WiFi is very bad (for example).

the idea of a DBAL or DAL is absolutely correct and should be implemented as soon as possible as it could heavily benefit the whole XBMC whose library management is a very "complex" part.

I would be very happy to help on this part (even in my own interest) but need a little help from some "senior" xbmc developer to quickly understand the logic behind it and how to try to optimize.

I would suggest we use something already working like:
http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/platforms.html

The Doctrine Project is the home to several PHP libraries primarily focused on database storage and object mapping. The core projects are a Object Relational Mapper (ORM) and the Database Abstraction Layer (DBAL) it is built upon.


Powerful database abstraction layer with many features for database schema introspection, schema management and PDO abstraction.
◾Latest Version: 2.4


And we have ALL possible databases there including all SQLite, MySQL, SQLServer, Oracle....

I mean... it would be PERFECT!


As a side note we may have to check how db is used in some areas (i.e. loading info on scrape) as maybe there are some areas there to improve too.

M

more info:

The Doctrine database abstraction & access layer (DBAL) offers a lightweight and thin runtime layer around a PDO-like API and a lot of additional, horizontal features like database schema introspection and manipulation through an OO API.

The fact that the Doctrine DBAL abstracts the concrete PDO API away through the use of interfaces that closely resemble the existing PDO API makes it possible to implement custom drivers that may use existing native or self-made APIs. For example, the DBAL ships with a driver for Oracle databases that uses the oci8 extension under the hood.

The following database vendors are currently supported:
◾MySQL
◾Oracle
◾Microsoft SQL Server
◾PostgreSQL
◾SAP Sybase SQL Anywhere
◾SQLite
◾Drizzle




if you look at the APIs there is a lot of similarities to what I've seen in the database classes.

I mean we can get rid of all of that and just rely on Doctrine (that is managed outside of the team Smile ) the impact would be quite low, just to change the references and queries

(I do prefer this than to build a stub interface between exisiting class and Doctrine)

M


RE: DB - SQLLite/MySQL - Performance issues - da-anda - 2014-07-14

I'm not aware that Doctrine has a C++ version - it's PHP only AFAIK. But yes, Doctrine is nice to use and having something like this in XBMC would be awesome IMO. BUT be warned that integrating such an abstraction is a really HUGE task, because you usually also have to abstract the way queries are build by using a custom QOM (query object model) + you need an object mapper. The cool thing in Doctrine is that you don't need a DB schema because it's auto generated out of the objects you like to manage with it, but due to missing class reflection this won't work with C++ from what I have heard (just like DependencyInjection).


RE: DB - SQLLite/MySQL - Performance issues - m.savazzi - 2014-07-14

is BIG :O
not HUGE as we already have a CDatabase class that, hopefully, is used everywhere.

Let's say is not a single line of code Tongue

ideal would be to able to have doctrine as a library to be loaded independently

I've found: http://www.swig.org/tutorial.html

SWIG is an interface compiler that connects programs written in C and C++ with scripting languages such as Perl, Python, Ruby, and Tcl. It works by taking the declarations found in C/C++ header files and using them to generate the wrapper code that scripting languages need to access the underlying C/C++ code. In addition, SWIG provides a variety of customization features that let you tailor the wrapping process to suit your application.