Force cache load

User fca35de8d7

21-10-2013 14:10:52

Hello,


we want to search in a structure-table with ~38million entrys.

We use MySQL 5.5 on a sci-linux system with 32 cores and ~0.5TB RAM as DB server.

We have installed jchem 6.1. The search itself runs fine, but the cache load of the table is very slow (~12 to 20 min).


We found the select instruction, but it get only 2000 structures and a very long delay between the individual instructions.


Is there a possibility to force the cache load with the cache manager?
Thank you for help.

ChemAxon 61b4fee994

22-10-2013 12:11:54

Hello,


I'm afraid currently this is the normal duration of cache load with your data, we saw much higher cache load times as well. You can call CacheManager.INSTANCE.loadCache , which loads the cache without using a warmup search, but the time of cache load will still be the same.


We set the fetch size of the statement to 2000 rows when querying data from the database, that's what you saw, so we receive the data in 2000 rows packets. We experience that more than 90 percent of the consumed time during cache load is the time of reading data from the database, so a database optimization may speed up the whole thing. We can't provide more information about it since it depends from the actual infrastructure where JChem Base is set up.


Regards,


Tamas

User fca35de8d7

22-10-2013 15:18:19

Hello,


are there some key values for MySQL server, which I should control?


Regards,


 


Bernd

ChemAxon 61b4fee994

24-10-2013 08:01:08

Hi,


We are not experts on MySql administration settings, so we can only guess, but I would take a look at and exampine the system variables, some of them may be useful.


Regards,


Tamas

User fca35de8d7

24-10-2013 08:58:32

Hi Tamas,


We have a optimized System (HP Proliant DL380p Gen8) wich can do easily IO 300MB/s and it does if we use our own sql routines.


But with the JCS we got in maximum 20MB/s.


Here are our my.cnf, maybe you see something we don't see:


[mysqld]


## General
datadir                         = /data/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
skip-name-resolve
sql-mode                        = NO_ENGINE_SUBSTITUTION
user                            = mysql

## Cache
thread-cache-size               = 128
table-open-cache                = 4096
table-definition-cache          = 2048
query-cache-size                = 256M
query-cache-limit               = 1M

## Per-thread Buffers
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 1M
join-buffer-size                = 1M

## Temp Tables
tmp-table-size                  = 128M
max-heap-table-size             = 128M

## Networking
back-log                        = 100
max-connections                = 30
max-connect-errors              = 10000
max-allowed-packet              = 16M
interactive-timeout             = 3600
wait-timeout                    = 600

### Storage Engines
innodb                          = FORCE

## MyISAM
key-buffer-size                 = 64M
myisam-sort-buffer-size         = 128M


## InnoDB
innodb-buffer-pool-size        = 266G
innodb_thread_concurrency      = 32

## Replication
server-id                       = 1
relay-log-space-limit           = 16G
expire-logs-days                = 7

[mysqld_safe]
log-error                       = /var/log/mysqld.log
open-files-limit                = 65535

[mysql]
no-auto-rehash
(END)


Maybe there is a

ChemAxon 61b4fee994

24-10-2013 11:28:32

Hi,


I have to point out again that I'm not an expert of these settings, but I looked at them and couldn't find something odd about them, so it's possible that no or just a small gain of speed can be achieved.


Regards,


Tamas

ChemAxon 61b4fee994

04-11-2013 09:24:19

Just to correct the false information in this topic: there is some calculation at cache load in version 6.1, but from 6.2, this is almost eliminated, so we expect a huge speedup in 6.2. The information that more than 90 percent of the cache load time is fetching data from database also refers only to version 6.2.


Tamas