[opencms-dev] OpenCMS Performance

[Manfred Pruntsch]

Hi Doug,

> We are running Tomcat 4.0.4 and mySQL 3.2.  Lately we have been
experiencing sluggish performance with
> adding and editing content.
Be sure to have mysql 3.23.xx. The new features are up this version.

You can check the current status and extended status:
c:\Programme\mysql\bin>mysqladmin status
Uptime: 42096  Threads: 16  Questions: 5141  Slow queries: 0  Opens: 59
Flush t
ables: 1  Open tables: 1 Queries per second avg: 0.122

A table will be opened for every concurrent thread. May be you have too many
opened tables?

c:\Programme\mysql\bin>mysqladmin extended-status
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_clients          | 0     |
| Aborted_connects         | 0     |
| Bytes_received           | 0     |
| Bytes_sent               | 0     |
| Com_admin_commands       | 0     |
| Com_alter_table          | 0     |
| Com_analyze              | 0     |
--------end snippet-------------------------------


Next you can check the current settings of mysql variables
c:\Programme\mysql\bin>mysqladmin variables
+---------------------------------+-----------------------------------------
-
----------------------------------------------------------------------------
-
----------------------------------------------------------------------------
+
| Variable_name                   | Value


|
+---------------------------------+-----------------------------------------
-
----------------------------------------------------------------------------
-
----------------------------------------------------------------------------
+
| back_log                        | 50


|
| basedir                         | C:\Programme\mysql\


|
| binlog_cache_size               | 32768


|

------------------------end
snippet-------------------------------------------------


The most important variables are key_buffer_size and table_cache. Are they
set correctly?
The manual recommended by RAM size > 256MB
shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
           -O sort_buffer=4M -O record_buffer=1M &
see the manual for more details.

To check the parameter changing:
shell> mysqld -O key_buffer=32m --help
see the manual for more details.

You should have a look at the table_cache variable. Increase it if possible.
Each opened table allocate memory from the table cache. When the table cache
is too small tables have to been closed before new tables are opened.
The record_buffer is another one to have a look. If it was set to 24MB for
example, that means that each active thread in MySQL was constantly
allocating and releasing a lot of memory. The system kernel was doing a lot
of unnecessary work by keeping track of that. But it will impact the
performance only by stessing mysql with more quries...

You will get the current buffer sizes of the mysqld server with the
following command as well:
c:\Programme\mysql\bin>mysqld --help
Possible variables for option --set-variable (-O) are:
back_log              current value: 50
bdb_cache_size        current value: 8388572
bdb_log_buffer_size   current value: 0
bdb_max_lock          current value: 10000
bdb_lock_max          current value: 10000
binlog_cache_size     current value: 32768
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 1800
innodb_mirrored_log_groups  current value: 1
innodb_log_files_in_group  current value: 2
innodb_log_file_size  current value: 5242880
innodb_log_buffer_size  current value: 1048576
innodb_buffer_pool_size  current value: 8388608
innodb_additional_mem_pool_size  current value: 1048576
innodb_file_io_threads  current value: 4

And the last, row level locking can be used with the new table type innodb.
Unfortunatrely I haven't any experience with it.
> OpenCMS is doing, but my thought is that with our tables getting larger,
> that DB locking may be what is slowing us down.
You can use innoDB tables instead of the default myisam tabletype.
InnoDB tables provide many new features to mysql
- transactions
- _row level locking_
- hot backup and foreign keys
without compromising the speed of MySQL
See http://www.innodb.com/ for more details.

Why should be oracle faster than mysql?

best regards
Manfred



> -----Original Message-----
> From: owner-opencms-dev at www.opencms.org
> [mailto:owner-opencms-dev at www.opencms.org]On Behalf Of Doug Schnelzer
> Sent: Wednesday, August 21, 2002 4:23 PM
> To: opencms-dev at www.opencms.org
> Subject: [opencms-dev] OpenCMS Performance
>
>
> We're using OpenCMS to develop the content for a 300+ page site.  Our
> development environment is hosted on a Sun 220.  We are running
> Tomcat 4.0.4
> and mySQL 3.2.  Lately we have been experiencing sluggish performance with
> adding and editing content.  I was thinking of just moving to an Oracle
> instance to increase performance.  I haven't looked at what kind
> of locking
> OpenCMS is doing, but my thought is that with our tables getting larger,
> that DB locking may be what is slowing us down.
>
> Anyone have some high level guidance/experience for increasing performance
> in the Workplace?
>
> Thanks for the help,
> Doug Schnelzer
>
>