mysql speed

[diamond-optic]

i doubt any of you know shit about this.. maybe gott if any of you lol..

but im wondering if theres any config settings i can tweak with MySQL (v5 now) to speed it up a little without using a ton of RAM....

right now it averages about 100-150mb out of my 2gb total so i can still spare some but i dont wanna go crazy with that as id like to use my pc for stuff other then running a mysql server

a little more speed with inserts and such would be great right now as ive got 3+ years of logs to reprocess and having that go faster would be a nice bonus lol.. but the config stuff is a bit confusing and im sick of going in circles on google trying to find details about it all

[mein gott]

one of the best ways to optomise SQL is to tweak the way the data is handled and not always in the config file. sometimes the longest querie can be halfed in execution just by reducing the amount of tables to those that really provide the true data.

is this just SQL server? with out seeing your config file its hard to offer help, the data below is general and should help if your using a vanilla config file

check these aginst your own config file, and make changes where needed.

set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = table_cache=1024
set-variable = sort_buffer=4M
set-variable = record_buffer=3M
set-variable = thread_cache=8
set-variable = thread_cache_size=256
set-variable = thread_concurrency=4
set-variable = myisam_sort_buffer_size=64M

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M


if yours are bigger than this then thats a problem, remember back up the old CFG file first

[diamond-optic]

alright cool ill try some of that out...

i attached the config that ive been using..

..part of the problem may be some of the tables, especially once i get up to date on the logs (about a year and a half worth of logs left to go) a lot of the tables are huge.. like the database itself will end up being about 60 tables that add up to several GB of data.. so maybe its just slow as hell cause they become so large

and i dont think its neccesarilly my pc slowing it down.. as i overclocked from cpu:2400mhz ram:800mhz up to cpu:3375ghz ram:900mhz and its not processing the slightest bit faster

[diamond-optic]

heh wow now its going way slower lmao

i setup a month to process when i was at work.. and it says it took about 3hours to do it.. going at about 100 lines per second / 15kb/s

the database now contains about 12 million rows, and is just under 1gb in size lol.. and im only on november 2007

[mein gott]

yeah thats not good... yer gotta reduce that database...back it up and remove a years data..but yo still have they back up

[diamond-optic]

well once its up to date.. its much faster cause its not doing as much at once (like i usually had it run every 60 minutes and it only took a minute or so.. wasnt too bad)

i just wish i could make it go faster when doing years worth lol

[diamond-optic]

i got it to speed up a bit
up to around 500 lines per second / 65-70kb/s

just cranked up how much ram it can use.. and the max table size to put in the ram.. most of the tables are really small, theres just a couple that are quite large so now its using like 500+ mb and its helping lol.. then once its done ill turn it back down