MySQL Forums
Forum List  »  Performance

Large Dataset with single user
Posted by: Olaf Wijk
Date: October 14, 2013 05:26PM

Hello all,

I have been doing some searches on increasing performance for mysql servers and have quite the idea now but none describe really my situation.

I run a database for my own poker analysis program and have precalculated data in order to do quick search ups instead of real time calculations, calculating this took 2 weeks with 6 of my 8 cores and resulted in a 4 column datatable with 2.8 million rows and a datafile of almost 200gb. The table uses MyISAM and only is accessed by 1 user at the time. The size mainly comes from 1 BLOB column that holds binary data of a comparable 1100 records in related to the primary key that are always loaded for use (as this is the actual data used by my program). -This is something I doubt it was the best approach but having 2.8mill * 1174 records didn't seem smart-

I can search very quickly and a data record is found in milliseconds however.... every time I query I need about 2700 records of data (these are just "select * from xxx where pk = exactnumner"). This takes in total 10-15 seconds. Which is way to slow because it should be in like 1-2 seconds(even faster preferably).

Now my system is a 32gb(8 in ramdisk) quadcore(8 threads) with a 2x7200rpm SATA3 2TB in Raid 1. I know it aint the fastest IO system but it is required for other programs running. Now when I run queries the only thing that spikes is my disk activty to 30-70mbps IO activity and almost none CPU activity in either my program or MySQL.

Now my question: what to do to make that query run under a second?

My first thought was to move the entire database to an SSD drive but I don't have one so before I spend 200$ I'd like to hear some experts out.

I didn't do much changings but below my my.ini



[client]

port=3306

[mysql]

default-character-set=utf8


[mysqld]

port=3306


basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

character-set-server=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100
thread_concurrency = 8

query_cache_size=0

table_cache=256

tmp_table_size=35M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=69M

key_buffer_size=4000M

read_buffer_size=2M
read_rnd_buffer_size=8M

sort_buffer_size=256K


innodb_data_home_dir="C:/Data/"

max_allowed_packet = 1M


innodb_additional_mem_pool_size=30M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=2M

innodb_buffer_pool_size=4000M

innodb_log_file_size=54M
innodb_lock_wait_timeout = 6000

innodb_thread_concurrency=18

Options: ReplyQuote


Subject
Views
Written By
Posted
Large Dataset with single user
1853
October 14, 2013 05:26PM
1078
October 15, 2013 09:21PM
913
October 21, 2013 09:06AM
823
October 22, 2013 09:06AM
810
October 24, 2013 06:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.