I am testing a 600.000 nodes Drupal installation.
I see very good results in Devel from cache, but the quad-processor is becoming slow.

When using pgadmin3 to connect to the database, I see that several queries could be optimized.

It seems like Drupal developers are only looking at Devel SQL execution time but forget that the database
is the central place for quality and speed.

Let me give you an example:

SELECT s.source, t.translation, t.language FROM locales_source s LEFT JOIN locales_target t ON s.lid = t.lid AND t.language = 'fr' WHERE s.textgroup = 'default' AND s.version = '6.14-dev' AND LENGTH(s.source) < 75
In PostgreSQL, the query executes in 488 ms.

Drupal devel module displays:
SELECT s.source, t.translation, t.language FROM locales_source s LEFT JOIN locales_target t ON s.lid = t.lid AND t.language = 'fr' WHERE s.textgroup = 'default' AND s.version = '6.14-dev' AND LENGTH(s.source) < 75
28 ms

This is 20 times less than real time.

The problem is that a server with 1000 users relies on real SQL queries being executed.

There should be at least information displayed that SQL queries times are from cache and should be checked.
I think this is one of the reason why Drupal is ***sometimes*** slow.

Developers rely on chache and do not understand the cost of some SQL clauses.
For example, DISTINCT is very costy in CPU time and index.

This is a major issue for me, I may be wrong.

Developers should always use a GUI like pgAdmin of MySQLfoobar to verify query times and learn writing fast natural SQL queries.

Kind regards,
Jean-Michel

Comments

grub3’s picture

grub3 commented

Especially, databases should be set with server-side logging of slow queries. This is possible in MySQL and PostgreSQL and this cannot replace a caching strategy. If 1000 people are connected to a server and run 400 ms queries, you SQL server will die, even if it is quad-core with 16 Gb like mine.

This should also be added to the developer guide (if not already).

moshe weitzman’s picture

moshe weitzman commented
Status: Active » Closed (works as designed)

DB does not provide this info. We could state that query cache is enabled or disabled but thats already reported at admin/reports/status/sql

grub3’s picture

grub3 commented
Status: Closed (works as designed) » Active

I think a statement should be displayed on top of the debugging dialog.

WARNING: when cache is enabled, SQL query time is the execution time of the query using cache. It does not inform you of the real execution time in the database.
To know the real execution time of SQL queries, you should either

  • run queries manualy from an SQL client,
  • enable SQL tracing server-side in your database,

Some queries that display at 20ms may execute in 400 ms or more. In a production environment, this can drive your database down. Each database system allows to log slow queries. Contact your system administrator or read this post URL

Feel free to rewrite something understandable.

Then we only need to describe shortly how to set-up tracing of slow query in MySQL and PostgreSQL server-side, not in PHP.

moshe weitzman’s picture

moshe weitzman commented
Status: Active » Closed (won't fix)