Go Back   AvaMods 5.o Forums > AvaMods - General > General
       Log In
Log In | Lost Password | Register


General Anything that DOESNT fit in one of the other forums.... this is basically your off-topic section..

Reply
Views: 567 - Replies: 6  
Share LinkBack Thread Tools
  #1 (permalink)   diamond-optic is Special  
Old August 11th, 2008
diamond-optic's Avatar
i dont want a goldfish
 
Join Date: March 13th, 2005
Location: Upstate New York
Status: hazey
Posts: 3,259
Default mysql speed

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

Quote: Originally Posted by ThePowerCosmic in regards to AvaMods
They are the type of people that are absolutely destroying the fabric of america and everything that goodness stands for.

Reply With Quote
  #2 (permalink)  
Old August 11th, 2008
Former AvaMods Admin
 
Join Date: October 14th, 2006
Location: Philadelphia
Status: No Status
Posts: 989
Send a message via AIM to mein gott Send a message via Yahoo to mein gott
Default

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
Reply With Quote
  #3 (permalink)   diamond-optic is Special  
Old August 11th, 2008
diamond-optic's Avatar
i dont want a goldfish
 
Join Date: March 13th, 2005
Location: Upstate New York
Status: hazey
Posts: 3,259
Default

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
Attached Files
File Type: ini my.ini (10.0 KB, 1 views)

Quote: Originally Posted by ThePowerCosmic in regards to AvaMods
They are the type of people that are absolutely destroying the fabric of america and everything that goodness stands for.


Last edited by diamond-optic; August 11th, 2008 at 07:59 PM.
Reply With Quote
  #4 (permalink)   diamond-optic is Special  
Old August 12th, 2008
diamond-optic's Avatar
i dont want a goldfish
 
Join Date: March 13th, 2005
Location: Upstate New York
Status: hazey
Posts: 3,259
Default

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

Quote: Originally Posted by ThePowerCosmic in regards to AvaMods
They are the type of people that are absolutely destroying the fabric of america and everything that goodness stands for.

Reply With Quote
  #5 (permalink)  
Old August 12th, 2008
Former AvaMods Admin
 
Join Date: October 14th, 2006
Location: Philadelphia
Status: No Status
Posts: 989
Send a message via AIM to mein gott Send a message via Yahoo to mein gott
Default

yeah thats not good... yer gotta reduce that database...back it up and remove a years data..but yo still have they back up
Reply With Quote
  #6 (permalink)   diamond-optic is Special  
Old August 13th, 2008
diamond-optic's Avatar
i dont want a goldfish
 
Join Date: March 13th, 2005
Location: Upstate New York
Status: hazey
Posts: 3,259
Default

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

Quote: Originally Posted by ThePowerCosmic in regards to AvaMods
They are the type of people that are absolutely destroying the fabric of america and everything that goodness stands for.

Reply With Quote
  #7 (permalink)   diamond-optic is Special  
Old August 13th, 2008
diamond-optic's Avatar
i dont want a goldfish
 
Join Date: March 13th, 2005
Location: Upstate New York
Status: hazey
Posts: 3,259
Default

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

Quote: Originally Posted by ThePowerCosmic in regards to AvaMods
They are the type of people that are absolutely destroying the fabric of america and everything that goodness stands for.

Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT -4. The time now is 07:25 PM.

Powered by vBulletin® Version 3.7.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.