Web-App (PHP) - data is cached over and over again

4 posts / 0 new
Last post
thomas.hontele
thomas.hontele's picture
Offline
Last seen: 2 years 7 months ago
Joined: Sep 1 2011
Junior Boarder

Posts: 2

thomas honteele
Web-App (PHP) - data is cached over and over again

Hi,

I'm checking out whether InfiniDb is the right DB to use for developing an analytic Web-Application (PHP). The main use is to read and analyze data which is organized into star schemas.

Apart from some smaller drawbacks in terms of flexibility in the use of SQL (between joins not supported), we currently have performance issues with simple queries (one table, simple filter, no join, <10.000 rows). They take about 0,5 seconds while with myISAM tables they take about 0,02 seconds... I know that the first time data is read for a query, it's reading it from disk and caching the related data into memory.

The problem with server-side web-applications seems to be that the data is read from the disk for caching over and over again, as the web-application typically creates a new connection for every request. I did not expect this caching to be dependent of the connection as I thought this would be managed on the server level.
To be sure I also tested these simple queries directly on the server and as expected they take ~0,5 seconds the first time (caching) and ~0,01 seconds for the following executions, so once cached the performance is good.

Is there a solution for this problem? Response time is key for the user acceptance of any web-app.

Thanks in advance,
Thomas

radams
radams's picture
Offline
Last seen: 2 days 4 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: Web-App (PHP) - data is cached over and over again

Hi Thomas,

Data is cached in InfiniDB on the first read. It is read from cache (if it's still there) regardless of the connection number. This can be verified by executing 'select calgetstats();' after the query and looking at the ApproxPhyI/O and CacheI/O statistics.

Thanks,

Robert
Calpont

thomas.hontele
thomas.hontele's picture
Offline
Last seen: 2 years 7 months ago
Joined: Sep 1 2011
Junior Boarder

Posts: 2

thomas honteele
Re: Web-App (PHP) - data is cached over and over again

Hi Robert,

Thanks for your answer. I can see indeed the ApproxPhyI/O reducing to 0 after a second run. The CacheI/O remains about the same. So that can 't be the problem.

In that case I do not understand what 's happening. The performance gain I get for heavy queries is significant but why do the small queries take ~0,5 seconds the first tiome executed on a new connection on the infiniDB engine while on myISAM they take only ~0,03 sec.

I've got the same database scheme running on both engines in order to be able to compare the performance.

Hereunder the simple query test results for 3 consecutive API-calls:

[table]
[tr]
[td]engine[/td]
[td]connection (api-call) #[/td]
[td]query run 1 execution time (s)[/td]
[td]query run 2 execution time (s)[/td]
[/tr]
[tr]
[td]infiniDB[/td]
[td]1[/td]
[td]0.86906313896179[/td]
[td]0.078881025314331[/td]
[/tr]
[tr]
[td]infiniDB[/td]
[td]2[/td]
[td]0.48795700073242[/td]
[td]0.058125972747803[/td]
[/tr]
[tr]
[td]infiniDB[/td]
[td]3[/td]
[td]0.48473000526428[/td]
[td]0.063098192214966[/td]
[/tr]
[tr]
[td]myISAM[/td]
[td]1[/td]
[td]0.54710984230042[/td]
[td]0.025302886962891[/td]
[/tr]
[tr]
[td]myISAM[/td]
[td]2[/td]
[td]0.019219875335693[/td]
[td]0.031240940093994[/td]
[/tr]
[tr]
[td]myISAM[/td]
[td]2[/td]
[td]0.019824981689453[/td]
[td]0.016183853149414[/td]
[/tr]
[/table]

Any idea of what might cause this behaviour? My only guess is the difference might have something to do with the PDO-connection we use to infiniDB (from PHP 5.3.6).

Thanks,
Thomas.

radams
radams's picture
Offline
Last seen: 2 days 4 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: Web-App (PHP) - data is cached over and over again

Hi Thomas,

InfiniDB is designed for use on large tables and, as you noted, the performance for queries on large tables is very significant. Query performance on small tables may be faster using a myisam engine. It's not clear what timing differences you are seeing with your app when using the PDO-connection.

Thanks,

Robert
Calpont