Feedback on Feb 7th SDUG event (Performance and Clustering)

[andydreisch]

OK, I had a scare for a while and thought the recording had no audio, but I was wrong.

The recording is posted, and Jacob's presentation is soon to follow. I'll post the Q&A as well, and queue Jacob up to address any unanswered questions that arose during the session.

Andy

[mycrmspacegunnar]

During the session it was explained that the shown number of records per listview has a big perfromance impact.
Someone mentioned the option to use SQL_CALC_FOUND_ROWS.

I would like to give some examples when SQL_CALC_FOUND_ROWS makes sense and when not.

To show the number of records and to list some rows of them there are basicly three way of programming it:

a) You select all records from the DB but limit the display of them.
This way is okay for very small tables only.
For bigger tables this performs very bad.

Your SQL might look like this:
"SELECT * FROM table1, table2, table3, WHERE table1.col1=xyz "
You display the number of found records
and display a certain portion of them.

Pervormance wise this scales very badly as the whole number of records need to be joined and fetched by the database and the whole number of records will be transmitted to the PHP engine.



b) Using LIMIT and SQL_CALC_FOUND_ROWS
This performs very good for queries without joines
Its uses much less memory than the above as only a subset of records will be transmitted to the PHP engine.

Your SQL might look like this:
"SELECT SCL_CALC_FOUND_ROWS * FROM table1, table2, table3, WHERE table1.col1=xyz LIMIT x,10"

On the database server side the joines need to be done for ALL records even if only a subset gets transmitted to the PHP engine.
So for big tables and using joins this will be very slow.


c) Using two queries.
If the where clause refers to not all the tables but onle one or two od them then the fastest approach is to use two seperated queries. One query to count the number of affected rows and one query to fetch the data records with limit.

Your SQL might look like this:

1st query
"SELECT count(*) FROM table1 WHERE table1.col1=xyz "
This query focus on one or few tables to count the rows.
We reduce the joines for this and gain speed.
The touched rows to do this query will get cached by the database server so the next query that reuses parts of them will run faster.

2nd Query
"SELECT * FROM table1, table2, table3, WHERE table1.col1=xyz LIMIT x,10"

We fetch only a subset of rows (limit) we save a lot of time in comparition to the SQL_CALC_FOUND_ROWS as usually the database will only join a limited number of rows. If I use an ORDER on a key than depending on my query we most often get away with joining only the number of rows that we display of just 20 rows. While the CALC_ROWS would need to join all rows in the database.


I hope this information is usefull for you.

Cheers
Gunnar

[mycrmspacegunnar]

Jacob,

during the session you mentioned that when using MyISAM tables you can get into performance issues because of the table locking of MyISAM.

I agree with you, both the table locking and the missing record cache can cause quite nasty performance "hickups" when using MyISAM tables in general.

MySQL offer other table handlers, as for example InnodB.
- InnoDB uses row level locking and is not effected of the table locking issues of MyISAM.
- And as InnoDB comes with datacache its performance ios much more reliable then MyISAM.
- In addition to this InnoDB is a much safer table type to store data into.
The risk to loose data is much much higher with MyISAM than with innodb on a server crash.


Wouldn't it make sense to use InnoDB as table instead of MyISAM ?
MySQL AB in general always comments to use InnoDB for most productive environments.


Have you tried this and what is your experience?


With innodb like any other transactional table handler there
is a rare chance that a query can fail because of a deadlock situation.
This can not happend with MyISAM as with MyISAM the whole table would just stand in such a case.
If a query fails because of locking it or course needs to be retried by the application. Does Sugar do this atm or will those queries get lost?

Cheers
Gunnar

[mycrmspacegunnar]

Jacob,

one more question regarding your session.
Add the end of the session you showed a page with a cluster setup.

Can you please elaborate a little bit more on this?
What HW and software solutions did you use for loadbalancing?
Can you recommend certain products?

Maybe you can set up a how-to for setting up what the page showed.
I think it will be most interesting for the readers here.

Many thanks in advance


Cheers
Gunnar

[kpit]

Hello gunner,

I have used and configured clients of mine. MySQL needs to be configured to have a default engine of innodb the mysql database files will still be myisam. This can be done by adding the following in [mysqld].

[mysqld]
 default-storage-engine=innodb
 ...

This will set the default storage engine to be innodb for all databases and tables created. I have found innodb tables to perform much better under heavy loads especially when there is frequent writing to the tables.

You will also have to make sure the innodb is tuned. You also want the MySQL Server to run on its own machine seperate from SugarCRM. This will yeald the best performance from MySQL. You may also have to tune the innodb settings to use more memory for innodb_buffer_pool_size and innodb_additional_mem_pool_size. On one machine I have configured the innodb_buffer_pool_size is set to 1G. The database is only about 250M and growing dailly. Right now and for some forseeable future the entire database will fit in memory Doing a table scan the first time takes the longest but once is it loaded future tables scans will avoid disk I/O on any future table scans if held in buffer. In an environment where you may have multiple databases being server you may have to increase this even more. The recommended is 50% - 80% of System Memory. I reccomend 50% and allow some memory to be used for disk cache.

As far as load balancers I have used F5 http://www.f5.com/ This system can be redundant and also use the closest server in a multiple datacenter/colocation facilities. I have also heard good things about http://www.coyotepoint.com/ load ballancers with similar capabilities.