Hi

Mattias, you mentioned some time ago that you were busy with a new web server project that uses XML database which will become an open source project.
Could you please explain briefly what the "XML database" actually means.

This is interesting because often the DB queries are the slowest part of a (web-)application.
I was discussing about PHP's performance and I was told it is not important compared to DB query delays.
Unfortunately it is true, even if table indexes are done right. You can save milliseconds by optimizing your code or using a compiled language but who cares if the DB query takes one second. Sites that have an extremely heavy load, like FaceBook, make an exception. Then PHP starts to use too much CPU and memory resources.

I ask on this general list because it may be of interest to others, too.

Juha
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111116/4b2a3260/attachment.html>

Juha Manninen wrote

This is interesting because often the DB queries are the slowest part of a (web-)application.

For our applications, setting up the DB connection is the slowest part, not the actual queries. Our queries execute in 1-3 milliseconds.

As for a text file as database, compared to a real RDBMS server... text files are *way slower*! The tiOPF project has persistence layers for text files (TAB, CSV and XML) and SQL database servers. I attached the hourly Speed Timing unit test report. As you can see, querying TAB, CSV and XML are by magnitudes slower than any RDBMS backend.

quick example:

table iteration (selection of records) for TAB vs
SqlDB_Interbase..... 19056ms vs 452ms

2011/11/16 Graeme Geldenhuys

For our applications, setting up the DB connection is the slowest part, not the actual queries. Our queries execute in 1-3 milliseconds.

I guess your queries fetch from one table only. Complex SQL queries with many joins can be really slow, even with correct indexes.

Juha
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111116/d7fb57e1/attachment.html>

Juha Manninen wrote

I guess your queries fetch from one table only. Complex SQL queries with many joins can be really slow, even with correct indexes.

Nope, we often fetch from many tables. True, single table queries are just a couple of milliseconds, and multiple table queries are still far under 1 second. See attached image - status bar values, using the following query. This is just something I put quickly together as an example. Not extensive, but an example none the less.

select distinct l.Username, a.City, c.NAME as CountryName, t.ADDRESSDESCRIPTION from LEARNER l
inner join ADDRESS a on
a.OWNER_OID = l.OID
inner join COUNTRY c on
c.OID = a.COUNTRY_OID
inner join ADDRESSTYPE t on
t.OID = a.ADDRESSTYPE_OID

Our reports use much more extensive queries, but results are still instant (way under a second).

As an explanation of the values in the status bar of the screenshot.

Record count - this should be obvious ;-)
Time to execute.... - this is how long the sql execution took and returned a resultset.
Time to download... - this is how long it took to generate Objects based on the resultset (our apps GUI & CGI use objects everywhere, never datasets)

One of our screens that our CGI and GUI apps generates is a treeview. It pulls back just enough information to build the tree. The treeview contains about +- 70,000-80,000 nodes. Retrieving that data and building objects from it is also near instant.

But yes, SQL queries can take long too - depending on what it must do. I have worked on project where we had to do data conversion from a IBM mainframe system to a Intel based SQL Server 2000 database. A full data conversion had to be run on Friday's, and normally only ended on Monday morning (if no failures occurred). That's 2.5-3.0 days continuous running, resulting in a 8GB SQL Server database. But this is an extreme case. :)

It's a lightweight database for XML files. That means you give it a list of directories and it will load/parse all xml files including subdirectories into memory for fast access.
You can query via simple HTTP requests and get xml. So you are not limited to FPC, you can use any language.
There are several types of query, including some with XPath expressions. At the moment it supports only a few XPath features, but they are already quite powerful.
When a file was edited/changed you can trigger a rescan or you can rescan in interval. A rescan on a thousand directories to search for modified files usually takes 10-100ms and is done in separate threads to not disturb the queries. It parses about 5-20MB per second per core and automatically uses all cores.

It is currently used by two projects, that were migrated from eXist DB. One is running on an OS X server, the other on a Linux server. They have thousands of directories, more than 150MB of XML files, which refer to others in various ways.

Because it only needs xml files, the normal ugly database tasks like setting up data structures, uploading new data, editing, cleaning up, renaming, backup, restore and upgrade are a no brainer. Even running it on a cluster is simple - you only need a share.

For example one project uses it like this:
The users use their favorite tool to edit the xml files and after they committed to the svn server they switch to their browser and instantly see their changes on the website.

The xml database is only one part of the project(s). The queries are done via some instantfpc scripts.

This is interesting because often the DB queries are the slowest part of a (web-)application.

Yes, that was one of the reasons to switch from eXist DB. The amazing thing is, that for the current projects it was sufficient to implement only brute force searches, without any index and they still are more than five times faster and do more complex stuff. To be fair: eXist DB is quite fast. With more man power it would have been possible to speed up the eXist implementation.

The other reasons to implement a new xml database were debugging and usability.

The database is a single executable, so each developer can simply run it locally and test every step with all the usual debugger tools.

Usability: The users only have to handle files and directories. Well, I must admit, for some users even that is too much, but usually they quickly get used to it. It is far easier than the former work flow.

The project is not yet published. I have to clean up a few things and write a basic documentation. Then it will be published. License will be free for commercial use.

Mattias

Mattias Gaertner wrote

write a basic documentation. Then it will be published. License will be free for commercial use.

And for non-commercial ? ;))))

zeljko
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/fd36e605/attachment.html>

2011/11/17 Mattias Gaertner

[...]

Thanks for the explanation.
In essence it is a database optimized for reading but not writing. For updating the XML file contents there is no concurrency checks or atomic transactions.
That is fine with many use cases but for example e-commerce sites could not use it. They need safe transactions for user profile and sales action management.
This is partly why the SQL databases are slow, they take care of concurrency also when data is updated.

Your solution could be used to provide fast access to a slow SQL DB data by generating data dumps eg. once a day.

eXist DB is made with Java. It would be interesting to compare how much their performance differs. Java has become very fast but it still hogs memory.

Good luck with the project!

Juha
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/9be7b8d3/attachment-0001.html>

I really don't see why you think SQL databases are slow. We have lots of queries running on our system, huge amounts of updates, and Firebird does an extremely good job of it, and we do almost only multi-table select queries. Concurrency is not an issue at all with their multi-generational architecture - no locking is needed.

Michael.

+1

And our systems use GUID's [defined as varchar(36)] as primary keys. Not the most optimized primary key type, but very suitable for our needs. And still our performance on multi-table select queries are very fast.

I guess it boils down to the skill of the developer writing those SQL statements. I once improved a large report which took 15 minutes to run. In the end that same report complete in about 7 seconds flat - simply by changing the SQL statement and applying correct indexes to the tables involved.

[...] Thanks for the explanation. In essence it is a database optimized for reading but not writing.

Yes.

For updating the XML file contents there is no concurrency checks or atomic transactions.

At the moment the projects only edit the files by svn and other external tools.
In the later project phase editing will be added. Concurrency checks are already there.

That explains why writing is slow, not why reading and searching is slow.

Your solution could be used to provide fast access to a slow SQL DB data by generating data dumps eg. once a day.

Ehm, SQL is for relational databases. A XML database is hierarchical. You can easily map data between those two, but their optimizations are different. A simple search in a XML database often require complex joins in SQL.

eXist DB is made with Java. It would be interesting to compare how much their performance differs. Java has become very fast but it still hogs memory.

Yes.

Good luck with the project!

Thanks.

Mattias

What database do you use ? It is not optimized primary key type (and x50 slower than eg. int) ,but wondering if it's faster than pure varchar(36) (because of no non-ascii chars in guid) ?
Anyone measured such things ?

zeljko

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/fdfb9f6c/attachment.html>

2011/11/17

I believe your DBs are fast but I have seen really slow ones. I think the complexity of queries, the amount of data and amount of users are the key factors.

The delayed response of many web-sites is caused by slow DB access, not by slow program code or slow network connection.

In a big company I was involved with PDM, eMatrix object DB which was built on top of Oracle relational DB. It created mystical table structure for the objects. Normal queries took seconds but if your query would return tens of thousands of items it practically halted and the process had to be killed. Another DB was an Oracle ERP. It also had complex table structure and lots of data, and queries typically took seconds. It was running in a computer that was the fastest in our country when it was bought! The table indexes could not be wrong because many people had optimized them.

I in fact created a solution for browsing and searching the PDM data quickly, using VirtualTreeView + data dumps read into memory. In many cases the reading should be optimized and fast because it happens often. Writing can be slow, in most cases nobody cares.

Juha
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/3c47e2e5/attachment.html>

Agreed.

That is a VERY dangerous statement :-)

You should have started with this :)

I don't think we ever run a query that takes a second to complete. Often we join up to 15 tables in a single query.

I think that the mapping objects <-> queries of these object <-> rdbms generating tools produce the most horrible tables and queries; You can optimize all you want, but if it is bad to start with, optimization will not help.

I suggest you try tiOPF. We use it. It allows you to design your tables as you want them, and tiOPF allows you to fine tune every query which loads objects from the database. It takes some work, but the end result is super fast.

Some common sense is all it takes.

Michael.

El 16/11/2011 16:36, Graeme Geldenhuys escribi?:

Updates, inserts and deletes in RDBM are usually slower than many other solutions due transaction tracking, integrity etc. Where retrieving data I think that RDBM are much better where dealing with a lot of data, more flexible and at least as faster as any other solutions. Obviously the advantages of a RDBM have a price, particularly when updating. But IMHO the advantages overcome disadvantages, particularly when dealing with a lot of data. Nevertheless, for small amount of data and/or concrete searches or updates you can design a particular solution that works better.

When we must move a lot of data, we remove all triggers, indexes, and foreignkey controls. Then, after moving all data, we activate indexes, triggers, foreign keys etc. Depending on the RDBM you benchmark if it is faster to commit small transactions or large transactions (usually large transactions). In some RDBM you can even disable transactions. Of course this options are for admin management, like migrations, massive structure upgrade etc.

Sure that for small amount of data and single user (e.g. configuration files), XML is a good option.

+1

Seen Oracle DB designed with varchars as primary keys, same db ported to pgsql (redesigned same database by me), using ints as primary keys (so indexes too) x100 faster (some complex queries (and those are cca 3-4k chars with 10-20 joins and 2-3 subselects) dropped down from 10 minutes into reasonable 10 seconds).
And ,yes many "programmers" "optimized" that oracle db :)

zeljko
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/43b9f686/attachment.html>

zeljko wrote

What database do you use ? It is not optimized primary key type (and x50 slower than eg. int) ,but wondering if it's faster than pure varchar(36) (because of no non-ascii chars in guid) ?

We use Firebird 2.5. I know some databases servers support a GUID field type, but we don't use that. We simply use a varchar(36) - thus storing any string value. And the reason we use varchar() and not char() [which might be slightly faster] is because our DDL scripts insert some default static lookup data which is more human readable and using < 36 character text as primary keys.

As for the 50x slower....we have not noticed such large degradation at all. Yes there will probably be a speed penalty because our primary keys are not sequential - thus the DB needs to work a bit harder. But the speed penalty is so small neither we nor our customers notice anything. The benefits of using GUID's far outweigh the cons - in our situation. Your mileage may vary.

Anyone measured such things ?

Yes, some years ago I wrote a simple speed test. GUID's were indeed slower that say Integer based primary keys. But the speed difference was sufficiently small, so not something we needed to worry about. I also remember the indexing algorithms for some RDBMS improved drastically over the years, reducing the speed issue of text GUID style primary keys even more.

2011/11/17 Juha Manninen <juha.manninen62 at g.....>:

The delayed response of many web-sites is caused by slow DB access, not by slow program code or slow network connection.

In South African you can be sure the delay is caused by slow network connections - our internet speed sucks! Our ISP's still market 385k ADSL as "fast internet", when countries like USA and UK sell 20Mbps-100Mbps to home users! South Africans can only dream of such speeds.

?

Graeme Geldenhuys hat am 17. November 2011 um 13:18 geschrieben:

AFAIK USA has very expensive lines, UK has only fast lines in big cities and they are not very reliable.
In some German cities home users can get cheap and reliable 50-100Mbps lines, 10mbit up, 10ms latency.
The best so far is Tokio. ADSL is a thing of the past. They have cheap 100 or even 200 Mbps both directions.?
?
Dreaming... ?
Mattias
?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/b8da1bed/attachment.html>

Maybe I said to much x50, but no you definitelly cannot compare int vs varchar index in any modern rdbms. Well, you can do that on small databases but comparing such things inside huge databases comes to my >= x50 diff (even x170 in example below).
eg. table with > 20M records, try to ask such table form some data comparing indexed varchar field vs. indexed int field.
hw=Intel(R) Xeon(R) CPU E5405 @ 2.00GHz, 16GB ram, 64bit centos. db=postgresql 8.3.11 64bit

I'm currently logged into such database, so here is what I'm talking about:

mydb=# SELECT count(recno) FROM mytable;
count
---------
21458510
(1 row)

mydb=# EXPLAIN ANALYZE SELECT * FROM mytable WHERE intfld1=2008 AND intfld2=1 AND intfld3=205 AND intfld4=7800;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------ Index Scan using mytable_all on mytable (cost=0.00..40301.96 rows=10 width=451) (actual time=1.269..49.018 rows=17 loops=1) Index Cond: ((intfld1 = 2008) AND (intfld2 = 1) AND (intfld3 = 205)) Filter: (intfld4 = 7800)
Total runtime: 49.081 ms

mydb=# EXPLAIN ANALYZE SELECT * FROM mytable WHERE varchar20field='20080100020500007800';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------- Seq Scan on mytable (cost=0.00..1604013.71 rows=2261 width=451) (actual time=17485.339..23611.425 rows=17 loops=1)
Filter: ((varchar20field)::text = '20080100020500007800'::text) Total runtime: 8344.816 ms

so we have 49 msec vs 8344 msec. (> x50 :))
I've runned both few times, so they have equal cache hits 1st one is a key of 4 integers (1st query taken 120 msecs to return) 2nd one is same key but as varchar eg. '20080100020500007800'' (1st t query taken 25 seconds to return)
I know that this can be (maybe problem) of pgsql implementation since pgsql does not distinct char,varchar and text - they are same ... varchar and char are only types with constraint enabled on len of chars, but I doubt about others db implementation is siginificant faster in case of varchars vs ints since varchar indexes are naturally much bigger then int indexes. Thing to mention: during this test 170 users are connected to that database and doing their usual job w/o interrupts.
This is just case of problem when you deal with really huge amount of data, such problem is invisible on rdbms with relative small amount of data (eg . < 1M records in queried table(s)).

zeljko

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/50337074/attachment-0001.html>

?

"Santiago A." hat am 17. November 2011 um 11:55
geschrieben:

[...] Sure that for small amount of data and single user (e.g. configuration files), XML is a good option.?

XML was invented to markup texts. Later programmers noticed that it is also nice for config files.
A few hundred MB of XML does not sound much compared to some big SQL databases. But because it is "not that much" we want to search them in real time (multiple queries taking less than half a second). That is not a trivial task, not in a XML database, nor in a SQL database.
?
Another important point is, that our data is hierarchical, has lots of optional elements and attributes, the data model changes often and the data is inconsistent. Using a SQL database for that would be stupid.? ?
After a year of programming with hierarchical structures I came to the conclusion, that hierarchical databases are greatly underestimated. If your SQL tables are not filled dense and/or if you have a lot of parent-child relationships you should try them out.?
?
?
Mattias
?
?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/37c96ec1/attachment.html>

I can feel with you... we have the same low speed at home (a small town in Upper Bavaria). I was impressed once by my mobile connection (HSDPA) though when I downloaded a 80 MB file in less then 10 minutes in a train ride from Munich central station to Munich Pasing. ^^

Regards,
Sven

2011/11/17 Mattias Gaertner

In 1990's there was hype about object DBs which would replace relational DBs. For some reason they disappeared.
Now the existing object DBs are built on top of a relational DB, like the eMatrix I mentioned.

I understood that XML is only the input data format for your DB. It is parsed into a memory structure and searches are done there. Then it could be called in-memory DB as well because the XML format is not the essential part.

Juha
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/0820ee75/attachment-0001.html>

?

Juha Manninen hat am 17. November 2011 um 15:58
geschrieben:

AFAIK eXist DB also uses a relational backend. Uploading XML files is hundred times slower than our XML database.
Writing and maintaining a database is a lot of work. That's why we skipped the complicated part of special disk structures. The database is only for data that fits into memory. And at the moment there are no plans for special query optimizations, as the brute force searches are already fast enough.
Indexes are planned. They are probably needed when the data grows to several hundred MB.?
?
?

Yes, it is an in-memory DB. But the xml is essential. Input and output are xml. There are no none xml files.?
Of course in memory the layout uses trees and pointers to represent the XML, so the server requires several times more memory than the size of the xml files. About two to four times on a 32bit system.?
Relational DBs don't necessarily store the tables as 2D arrays in memory/disk. ?
?
Mattias
?

Juha

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20111117/547c9873/attachment.html>