Howto: Database optimizing

[Guest]

yes.. thats all you do.

can i get a mod to verify that this is all i do?

[FriedRoadKill]

He already did

6th post on page 7

[Trent Angelus]

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]

16M is way too less I run with 512.

Can you access the mysql config file?

[Trent Angelus]

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]

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

[Seahorse]

+1

[bigsteve]

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....

[hux]

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

[beansman]

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.