I am evaluating options to improve the performance of Moodle. Can anyone confirm, one way or another, whether pgpool can be used with Moodle? Specifically does Moodle contain SELECT queries that have side effects, or UPDATE or INSERT queries that have undeterministic functions like "random"?
I did a quick grep and couldn't find anything that rules out pgpool (or will require modification).
Thanks in advance for any information!
> SELECT queries that have side effects
Some SELECTs move sequences forward.
Some SELECTs move sequences forward.
They do? wow! how is that possible?
iirc, pgpool is not smart enough to differentiate between SELECTs that bump sequences and SELECTs that retrieve data only.
Eg select nextval('foo_id_seq') is treated as a readonly query.
Eg select nextval('foo_id_seq') is treated as a readonly query.
Penny, would you say pgpool (we try using pgpool-II) would not be usable for postgress replicates with Moodle ? Some one knows about another way having real synchronous clustering on Postgres ?
I've had a few discussions about using pgpool before, and the general consensus was it doesn't have enough built-in smarts to differentiate between reads and writes at this point.
You would really have to handle this at the application level by making the database abstraction layer be aware of reads vs writes and behave accordingly (send writes to all databases, fetch reads from a pool)
In Moodle, 99% of the queries go through a function that knows whether it is read or write (get_records vs insert_record) for example, but that doesn't solve the problem of sending raw sql (execute_sql for example).
You end up either having to parse the query in the database abstraction layer (bad, because as above, SELECTs can actually be writes), or in the pgpool layer (bad, because it doesn't, but is philosophically a better place for it), or in the caller to the database abstraction layer, I mean, where the sql is written (good, because that place knows what sort of query it is -read or write, but bad because it really should be handled somewhere more central).
You would really have to handle this at the application level by making the database abstraction layer be aware of reads vs writes and behave accordingly (send writes to all databases, fetch reads from a pool)
In Moodle, 99% of the queries go through a function that knows whether it is read or write (get_records vs insert_record) for example, but that doesn't solve the problem of sending raw sql (execute_sql for example).
You end up either having to parse the query in the database abstraction layer (bad, because as above, SELECTs can actually be writes), or in the pgpool layer (bad, because it doesn't, but is philosophically a better place for it), or in the caller to the database abstraction layer, I mean, where the sql is written (good, because that place knows what sort of query it is -read or write, but bad because it really should be handled somewhere more central).
Penny, we are resulting working on the same project -> Jonathan !! How funny life is... how great moodle community ...
Cheers.
Yes I made that connection shortly after posting my reply yesterday too! 