Reporting is very slow

[thewangod]

I have a dual 3GHZ Hyperthreaded Logalot server with 2GB of RAM and reporting seems to be very slow (about 15 minutes to do a report). My CPU and memory seems very underutilized. The CPU never goes above 25% when running a report
Is this the way the product is supposed to work?
Does anyone else have the same problem? If so, there a soloution?
My boss wants several reports on Fridays and I don't have time to wait around for them.

[mkrygeri]

Have you looked at your my.ini settings for mysql?
There are quite a few options you can add to increase performance.
we have a similar box with 3GB of RAM and it was lightning fast after I added these options. I'll comment each important item with a #.
Here is my my.ini:
[mysqld]
basedir=C:/SOE/mysql/
datadir=C:/SOE/mysql/data/
tmpdir=c:/soe/tmp/
#Allows mysql to cache all table keys. This speeds up query speed.
set-variable = key_buffer=1024M
set-variable = max_allowed_packet=32M
set-variable = table_cache=256
# Speeds queries up by sorting in memory rather than disk.
set-variable = sort_buffer=128M
set-variable = record_buffer=8M
set-variable = myisam_sort_buffer_size=32M
# Makes threads activly available to queries by running in memory
set-variable = thread_cache=16
# Caches common query results. Will speed up the interface A LOT!
set-variable = query_cache_size=64M
# Limits the size of a ny cached query.
set-variable = query_cache_limit=4M
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
# Allows table join results in memory. This is per thread. BE CAREFUL!
join_buffer_size= 64M
#large queries require that a temp table be built. This says anything < #256M uses memory. This is per thread. BE CAREFUL!
tmp_table_size = 256M
read_buffer_size=32M
read_rnd_buffer_size=128M
net_buffer_length=1M
skip-bdb
skip-innodb

#END CONFIG

You should also defrag your drive regularly. MyISAM tables do not allocate contiguos blocks of disk.

[dalel]

Just so everyone is aware we have implemented a new style of indexing for the MySQL DB that will speed up your reports by at least 400%. This new indexing style will be part of the next version of Logalot. This will be a free upgrade for all maintenance customers.