Hello All,
I'm experiencing performance troubles with PHP5/SQLite (php 5.0.3) on Solaris 9, 10, AIX 5.2 but not on Linux (Suse 9). These troubles happen when using apache but also when using only the PHP binary.
The problem resides in the fact that SQLite consumes all the CPU and is very slow for some reason and I can't identify the cause. When I make a truss, I can see that the operations that need much time are the "writing" statements but why is that so slow, I don't know.
I'm wondering if this behaviour could be due to a system library or to the backward operating system.
So far, I've not seen such problems under Linux (Suse 9 Enterprise server) nor under Windows (incredible isn't it?
)). Do you have any clue?
Thanks.Well, I forgot to mention that I'm using transactions which is supposed to boost the performance. I'm also using triggers that might slow down the execution but the changes made by the triggers have to be done anyhow! It's better to use triggers than just issuing other SQL statements.Using transactions only boosts performance if you do more than one insert / update.
You might want to consider setting the async option (if indeed, it exists)
Mounting the filesystem your databases are on with the "noatime" option might help.
Another thing to check is whether your filesystem has data journalling on - if so, this is a *very* bad idea for performance. Data journalling is not the same as filesystem journalling.
MarkHello MarkR,
Thanks for your reply but I'm not sure to understand everything. First of all, regarding the transactions, you're right and I embed much more than only 1 SQL statement within a transaction.
Regarding "data journaling", what do you mean exactly? Are you talking about the ".db" file created by SQLite or about anything else?
ThanksOk I found the problem. It was actually due to a "costly" trigger. I managed to build another more performing trigger and it did it.
The way I propagate the information is mainly based on triggers, this means that a "small" change can have major impact on the database due to the cascade of triggers that follows this change.
Thanks for your help anyway.
I'm experiencing performance troubles with PHP5/SQLite (php 5.0.3) on Solaris 9, 10, AIX 5.2 but not on Linux (Suse 9). These troubles happen when using apache but also when using only the PHP binary.
The problem resides in the fact that SQLite consumes all the CPU and is very slow for some reason and I can't identify the cause. When I make a truss, I can see that the operations that need much time are the "writing" statements but why is that so slow, I don't know.
I'm wondering if this behaviour could be due to a system library or to the backward operating system.
So far, I've not seen such problems under Linux (Suse 9 Enterprise server) nor under Windows (incredible isn't it?
)). Do you have any clue?Thanks.Well, I forgot to mention that I'm using transactions which is supposed to boost the performance. I'm also using triggers that might slow down the execution but the changes made by the triggers have to be done anyhow! It's better to use triggers than just issuing other SQL statements.Using transactions only boosts performance if you do more than one insert / update.
You might want to consider setting the async option (if indeed, it exists)
Mounting the filesystem your databases are on with the "noatime" option might help.
Another thing to check is whether your filesystem has data journalling on - if so, this is a *very* bad idea for performance. Data journalling is not the same as filesystem journalling.
MarkHello MarkR,
Thanks for your reply but I'm not sure to understand everything. First of all, regarding the transactions, you're right and I embed much more than only 1 SQL statement within a transaction.
Regarding "data journaling", what do you mean exactly? Are you talking about the ".db" file created by SQLite or about anything else?
ThanksOk I found the problem. It was actually due to a "costly" trigger. I managed to build another more performing trigger and it did it.
The way I propagate the information is mainly based on triggers, this means that a "small" change can have major impact on the database due to the cascade of triggers that follows this change.
Thanks for your help anyway.