optimize database

[Dr_Michael]

went into phpMyAdmin and decided I had to start fiddling (ugh!) so i repaired and optimised each table on its own, one by one.

Is there any difference if you optimise all the tables by once? I mean all together and not one by one.

[halebopp]

Is there any difference if you optimise all the tables by once? I mean all together and not one by one.

There shouldn't be any defference.
@egg: Ich you do a dump using phpMyAdmin, you can easily transfer your data anywhere else. Just copy the source code, too, and you're done
If your MySQL dump is quite big, i.e. over 100MB or so, you might get problems with the php script execution time, depending on the server spped. In this case, you should ask your hoster to make a dump for you.

[Overseer]

i';ve screwed my dbase before optimizing while its live... lol

be careful.. i did it lots of times before the day it got broke

[Overseer]

ohh yeah and my dbase is now around 210 meg total

[halebopp]

i';ve screwed my dbase before optimizing while its live... lol

Don't get your point.. Please explain that...

be careful.. i did it lots of times before the day it got broke

Update your MySQL Older versions had problems optimizing large tables, but the current versions seem to work fine.

[Overseer]

live = board was open and being used...


mysql version is out of my control. the current version running there is 3.23.54 but that means nothing to me.

[mediman]

I did the disable log clicks about 5 days ago, and yesterday members were complaing of 20+ second load times as displayed on page.

I did the clean active log, repair tables, recount all boards, empty forum log, and then optimize DB, all from the admin centre of the board. no difference. 8 hours later still getting long load times.

went into phpMyAdmin and decided I had to start fiddling (ugh!) so i repaired and optimised each table on its own, one by one.

my DB went from 38.8MB to 38.7MB in size

now getting load times of 0.2 or less.

I will be doing the phpMyAdmin optimise and repair once a day

I dont ahve shell access as yet....thats my next job, to upgrade to dedicated server and full admin access...but I ave to be sure they wont lose any data from the DB as I am unsure as to the integrity of my DB backups.....

bad idea! there are probs with phpmyadmins repairfunction! best thing you can do is to remove click log function!

mediman

[mediman]

i';ve screwed my dbase before optimizing while its live... lol

BS! the mysql optimize function don´t need to lock the tables, you can optimize during using the db without any probs! we are not such idiots to code optimize function without checking this first!

but, there are probs with optimize on large tables! but this is a mysql prob, not a prob of mine or manjus code in admincenter!

mediman

[Dr_Michael]

How often should I optimize my database? Is there any risk?

[[Unknown]]

How often should I optimize my database? Is there any risk?

There have been some worries about doing it on very large databases.

My understanding is that these worries are gone with MySQL 4.x.  If your server has the latest version of MySQL, I would recommend doing it monthly.

Otherwise, it can cause problems on very large databases. (in 3.x, reportedly...)  This is a bug in MySQL, apparently, and definitely nothing wrong in mediman and manju's fine work....

-[Unknown]

[Dr_Michael]

There have been some worries about doing it on very large databases.

My understanding is that these worries are gone with MySQL 4.x.  If your server has the latest version of MySQL, I would recommend doing it monthly.

Otherwise, it can cause problems on very large databases. (in 3.x, reportedly...)  This is a bug in MySQL, apparently, and definitely nothing wrong in mediman and manju's fine work....

-[Unknown]

MySQL 4.0.12 is it fine?
Database is about 24MB. So you believe that a monthly optimise is ok?

[David]

There have been some worries about doing it on very large databases.

My understanding is that these worries are gone with MySQL 4.x.  If your server has the latest version of MySQL, I would recommend doing it monthly.

Otherwise, it can cause problems on very large databases. (in 3.x, reportedly...)  This is a bug in MySQL, apparently, and definitely nothing wrong in mediman and manju's fine work....

-[Unknown]

MySQL 4.0.12 is it fine?
Database is about 24MB. So you believe that a monthly optimise is ok?

I repair, anaylize, and then optimize my database nightly.

[Egg]

my database is now at 43MB

if I dont do it nightly the board slows up considerably. dont know if its connected.

[David]

if I dont do it nightly the board slows up considerably

That makes it sound connected.

[Derek]

Just a note: my server people disabled my clicklog table a few months ago and phoned my house telling me that it was causing all sorts of problems with all the databases. Keeping 90 connections to the db open at all times, etc. So I edited the code whilst waiting on hold, and I just had to tell him that I fixed it, the problems would be solved, etc.

I don't use it now, because it's useless to me, and it's just one more insertion every time a page is loaded that I don't need.