Detecting when OPTIMIZE is needed.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Detecting when OPTIMIZE is needed.

Post by Sevengraff »

In phpMyAdmin, it seems to be able to tell when you need to run OPTOMIZE TABLE `blah`. It doesn't seem to pop up a lot, but I was thinking that it would be good if my script could detect when a table needs to be optomized and then run the needed query.

Image
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html will tell you more about it, or actually the link to ANALYZE TABLE.
Using analyze, you get a msg_type that you can use to determine your action(s).
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

Thanks! I'll look into that then.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Well, not sure, as i needed to test it myself and found:

Code: Select all

Array
(
    їTable] => jam_l.posts
    їOp] => analyze
    їMsg_type] => status
    їMsg_text] => Table is already up to date
)
...where I have the same red overhead as you had. Lets report back here whoever finds a solution.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Might have found it... ;)

Code: Select all

SHOW TABLE STATUS like 'users'
Array
(
    їName] => users
    їType] => MyISAM
    їRow_format] => Dynamic
    їRows] => 3
    їAvg_row_length] => 162
    їData_length] => 488
    їMax_data_length] => 4294967295
    їIndex_length] => 3072
    їData_free] => 0
    їAuto_increment] => 
    їCreate_time] => 2003-10-04 00:34:55
    їUpdate_time] => 2003-10-04 04:25:33
    їCheck_time] => 2003-10-04 04:25:33
    їCreate_options] => 
    їComment] => 
)

Code: Select all

-- interesting part of the above:
    їData_free] => 0
Before "OPTIMIZE TABLE USERS" the above was not 0. Try on your own table and see what happens.
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

I think you've got it. that looks like it's what phpMyAdmin looks for. On tables that it says need optomization, [Data_free] is never 0.
Thanks a bunch man.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

No, thank you. Interesting subject that I've never thought of.

Just don't add it into a averagely large script as the optimize function locks the table resulting in other users errors. (In the meaning of adding it to a template system or similiar.) Just thought I'd mention it just if anyone else reads it and gets the bright idea... ;)
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

it seems that tables that hold "users online" data need to be optomized rather often, as they are updated every time someone loads a page.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

The followup question about this would be; what happens in ½-year when you havn't optimized your users_online table once?

Will it begin to display errors or merely take up more space on the servers hdd than it actually requires...

I was thinking that adding an automated function to the script perhaps isn't such a bad idea, as you can make it run on certain days of the month or similiar...

Code: Select all

if (date(j H:i:s) == '1 00:00:00') {
 function_optimize_tables();
}
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

yeah but then your assuming the script will run exactly that second. better to have something else trigger it. like when the admin logs in, or access a specifc place maybe.
Cruzado_Mainfrm
Forum Contributor
Posts: 346
Joined: Sun Jun 15, 2003 11:22 pm
Location: Miami, FL

Post by Cruzado_Mainfrm »

if you have unix, u can set a cronjob to execute the file every hour or so
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Multi-table JOIN queries can be more efficient with optimised tables.
evilMind
Forum Contributor
Posts: 145
Joined: Fri Sep 19, 2003 10:09 am
Location: Earth

Post by evilMind »

You might want to check the keys in your table. If you modfiy a key over and over the table has to re-index it'self resulting in overhead

* actually every time you modify a key/index/etc the table has to re-index itself *
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

To point out a few things, running optimize table often isn't good. From the manual:

"In most setups you don't have to run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable length rows it's not likely that you need to do this more than once a month/week and only on certain tables."

The key thing to remember is that you should design your tables so that this isn't necessary.

For example, make your tables fixed length rather than variable length. This would mean replacing all your varchar fields with char fields, and what not. However, when you actually look at what you are doing, this is actually easier than you suspect.

Fixed length tables are also quicker to use, and search through. The reason is because to determine the size of the table, MySQL has to do less computations. For example, using an index, if MySQL knows that record 45,000 is what it needs, on a fixed length table, it just needs to go to the 45,000xbytes_per_record and start reading. Otherwise, on a variable lengthed table, it has to read through the database, etc. That's a very unofficial description, but it's basically how it works.
Post Reply