Re: [Phpgroupware-developers] SQL garbage in contacts backend

[Markus Kaemmerer]

Alex Borges <address@hidden> wrote:,

>person_id is a primary key so the index should be implicitly created (or
>thats the rationale, if this is not the case, then we should change this
>to reflect the right thing).

in ./phpgwapi/setup/tables_current.inc.php is no primary key defined.
There is an index to person_id defined, but in my fresh installation
(MySQL) the table has no index.

>> I think that this part of the SQL string does not make any sense. This
>> "(CASE WHEN last_name IS NULL THEN '' ELSE last_name
>> END))" should work. 
>Yes, you are right.... i got mislead myself. This is actually for the
>full name implementation since the table has no full name field. 

Is this a bug in the SQL builder or in the application and the SQL
builder does not remove unnecessary tokens?

>> Is there a reason why not add a "nullable => false and default => ''
>> in database description? Here in my SQL book this definition seems to
>> be a standard SQL command and should be supported from every database
>> (we can verify this). 
>
>Most is handled the right way, i dont have the details right now.

Maybe you should update all tables to the current and correct
definition to solve this and remove all unnecessary tests for NULL. 

>> >Communication types and contact types are catalogs (really small ones,
>> >tops 50 entries). In the actual addressbook code, the whole contents of
>> >them are cached. So, this is just a 'first hit' performance hit
>> 
>> Because our synchronisition works with XML-RPC calls, this 'first hit'
>> hits us every call. I do not see a reason, why this tables should be
>> cached. In addition I think this can lead to unconsistency of the
>> cached values and the database values.
>
>No, they hold no real values. They are catalogs per design. Anyhow, this
>first hit SHOULD (im not shure if under xml-rpc is, but normally it is),
>be session wide....that means only the first time contacts_sql is
>instanced should this stuff be brought in from the database.

I don't think it is a good idea to cache databases (even small ones)
in session data. See my other mail about performance tests. This test
show, that this does not speed up. Maybe you should do your own test
regarding this topic. 

I think it would be better to let the database cache such things,
databases are made for this. Another problem may be database
inconsistency. 

>> >Catalog, should be small, executed upon contacts_sql instantiation. You
>> >can also tell it to lazyly not-cache it, but that would be stupid.
>> 
>> is there a switch or something like this we can set if we get our data
>> trough IPC and XML-RPC?
>
>Yes. Upon instantiation of sql_contacts you can tell it not to prefetch
>the catalogs. 

How do I do this?

>Although im not shure thats the behaviour you want.
>Catalogs are cached in the session cache. Those queries should be
>executed only once per whole phpgw session. Actually, their prefetching
>could be done in the login hook.

I think that I saw theses SQL statements more than one in my log in a
synchronization session. Our XML-RPC implementation holds one phpGW
session for a complete synchronization process, so this caching should
occur only once. 

>ITs like this. We have no full_name in the person table. Just a
>first_name, last name, middle name...etc. This query concatenates them
>when you ask for the full_name field in any of the functions wanting to
>get anything from contacts

Dirk found, that vCard export only works, when there is a first and a
last name. But I have many contacts in my Palm where I have only last
names. I don't now, if Dirk has changed this until now. 

>1.- Php is just a language, and apache is just a web server. Neither are
>an application server or has any natural provisions for thread creation
>or subprocess delegation. Jakarta/tomcat (for example) does have this
>kind of provisions thus making it a DIFFERENT (not better or worse)
>scaling model.

I think that enterprise applications like groupware systems should be
written in a real programming language for a real application server,
but that's another topic and now it is a bit late to translate
phpGroupware in JavaGroupware or groupware.NET :-).

>2.- With this kind of setup, you have to think about performance as a
>functionality <-> performance tradeoff. All queries in phpgw have to run
>in databases that do not support subselects (otherwise, we would reduce
>the hit rate to the databases by a huge factor). 

AFAIK MySQL from 4.0 and the other databases are supporting sub
selects in queries, but this does not help, because we need MySQL 3.x
support, right? But most of the 'normal' queries are optimized from a
good database so they should be not much slower than queries with sub
selects.

>Well, we want groupware. And all that, makes for a big ass database, and
>potentially, a need to manage large ammounts of memory in the php app
>(look at acl fetching when you login). 

My be it would be much better to use shared memory to cache things
like acl and little tables than sessions. This would help in really
big installations a lot more than caching in session variables.

>If you want to make it faster, you need to look at the model of php apps
>and ask yourself what is taking the most resources. In my experience:
>
>Problem                                        Solution
>- Loading of php code and execution    Turck,apc or other cache

I don't see a major problem here. MMcache does the trick here :).

>- Exponential use of db connections    Cluster db
>                                       Use non-persistant connections

I see the major problem in the overhead for every single request (see
other mail). 

>> I added some (quick choosen) indici manually with phpMyAdmin, this
>> gave me a 30% performance boost but does not solved all my problemes.
>
>Well thats a lot! It shows that not all the indexes that we should be
>creating are created. Gather a list and lets put them in!

You now your database at best, so you should decide which field needs
and index. Adding more index to fields that are not used only slow
down things. 

>Another tip....use php4 sessions, not database sessions. Db sessions
>ammount to a, potentially large,  extra hit to the database.

In my measurements this doesn't make a noticeable difference.

I made a little database diagram for all contact tables (see
attachment). You can see, that contact_org_person, contact_org and
contact_person does not have an relation to other databases (reverse
engineered from my current installation).

Markus

--
Markus Kämmerer         Team Software Solutions
pro|business AG, EXPO Plaza 1, 30539 Hannover
E-Mail: address@hidden,  Phone.: 0511/60066-0
WWW: http://www.probusiness.de/,    Mobile: 0177/5990932

**********************************************
           address@hidden 2004

*       Halle 1, Stand 3k4 (Magirus Deutschland GmbH)
*       Halle 1, Stand 7f2 (EMC Deutschland GmbH)
*       Halle 6, Stand D46 (Land Niedersachsen)
**********************************************

contacts.png
Description: Binary data