Howto: Database optimizing

Thought of something everyone else is having trouble with and just wanna help them? Post it here.
Guest

Re: Howto: Database optimizing

Post by Guest » Mon Oct 20, 2008 21:34

Guest wrote:yes.. thats all you do.
can i get a mod to verify that this is all i do?

User avatar
FriedRoadKill
Regular
Posts: 226
Joined: Fri Jun 20, 2008 22:23

Re: Howto: Database optimizing

Post by FriedRoadKill » Mon Oct 20, 2008 21:56

He already did :)

6th post on page 7
I did data dumps. And some code. ~Now retired ~

User avatar
Trent Angelus
Greenhorn
Posts: 24
Joined: Sat Sep 20, 2008 01:32
Location: EDK3.1:865

Re: Howto: Database optimizing

Post by Trent Angelus » Wed Oct 22, 2008 00:52

beansman wrote:
Trent Angelus wrote:My DB seems slower after this. Really slow :(
I converted the table types, and added the indexes.
I went around to all the tables and merged the separate indexes into one, as Tribalize suggested.
How much memory does your innodb have to run on?
I checked my Innodb settings agains the OP. Mine reads:

Code: Select all

innodb_buffer_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_flush_method = null
No idea how to change these settings :(

beansman
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman » Wed Oct 22, 2008 08:33

16M is way too less ;) I run with 512.

Can you access the mysql config file?
/HyperBeanie

Author of the Value Fetcher: Get It Here
Co-owner and developer of EVSCO

User avatar
Trent Angelus
Greenhorn
Posts: 24
Joined: Sat Sep 20, 2008 01:32
Location: EDK3.1:865

Re: Howto: Database optimizing

Post by Trent Angelus » Sat Oct 25, 2008 14:04

Yes. I modified C:\Windows\my.ini to this:

[mysqld]
basedir=c:/xampp/mysql
#bind-address=censored
datadir=c:/xampp/mysql/data
#language=c:/xampp/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=censored
#set-variable=key_buffer=16M
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT


But the variables page in PHPMyAdmin doesn't show any changes. :(

blue_sassley
Apprentice
Posts: 54
Joined: Sat Jun 14, 2008 20:24
Contact:

Re: Howto: Database optimizing

Post by blue_sassley » Wed Oct 29, 2008 18:10

Can someone that has followed this thread closely give me a full listing of the MySQL things I can run on my DB that gives the best of all these posts please? I read over the whole thing but there are so many little one posted hear and there I think it would help everyone that fell off the wagon after the big server crash months ago get back up to speed. Or have all of these things been added to the newest rev 359?

Thanks,
Blue
Image

User avatar
Seahorse
Apprentice
Posts: 48
Joined: Sat Jun 14, 2008 20:24
Location: In a bar, drinking JD...
Contact:

Re: Howto: Database optimizing

Post by Seahorse » Thu Oct 30, 2008 09:10

+1
"Regrettably your planet is one of those scheduled for demolition"
Rgds
Mike

User avatar
bigsteve
Someday Author
Posts: 96
Joined: Sat Jun 14, 2008 20:24
Contact:

Re: Howto: Database optimizing

Post by bigsteve » Thu Oct 30, 2008 11:43

Trent Angelus wrote:Yes. I modified C:\Windows\my.ini to this:

[mysqld]
basedir=c:/xampp/mysql
#bind-address=censored
datadir=c:/xampp/mysql/data
#language=c:/xampp/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=censored
#set-variable=key_buffer=16M
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT


But the variables page in PHPMyAdmin doesn't show any changes. :(
I think you have to restart your webservice for the changes to take effect....
Killboard Update/Install Service.
Installation & Upgrade to version 4.0 available now. Please Ask for Costs.
Helping you all out.

Image

EVE is life, Life is EVE
That's it pick on the minority. Bigots.

hux
Greenhorn
Posts: 19
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by hux » Thu Oct 30, 2008 12:24

Is it useful to switch to innoDB without memcached available or go these two hand in hand?

beansman
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman » Thu Oct 30, 2008 12:30

Very usefull!

I run it at http://svn.nsbit.dk/killboard without memcached. I have 850k+ killmails.
The server is a 1.4 Ghz with 1 gb ram. (Not a lot) and it really helps.

You can always go back again. :)
/HyperBeanie



Author of the Value Fetcher: Get It Here

Co-owner and developer of EVSCO

Post Reply

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 2 guests