Board MySQL Optimization Tips

[Solomon]

Hello bfarber,

Many thanks for this tutorial.

I am however, having some difficulties implementing the procedures described therein. When I go into phpMyAdmin using cPanel, I see a thin panel on the left with some databases and one among them is the _ipb database. On the right I have two columns "Localhost" and "phpMyAdmin - 2.11.0" and both these show some options and info. I do not see the option "forum_sessions" or similar (considering different database names etc.). When I click the _ipb database it opens the list of tables in the panel on the left and structurally displays of these tables on the right. At the top of it there is a menu consisting of Structure | SQL | Search | Query | Export | Import | Operations.

None of these gives me the option to change the forums_sessions tables or anything to that effect. The Server I am on is using "MySQL client version: 4.1.22"

Can you please point out the correct steps for those not on ipb servers, since I presume the above settings are available on a server hosted by IPB?

Regards,

Kokab

[Invisionary]

Click on the name of the _ipb database in the "thin column". That will open the database into the right hand panel.

Do that and see how you get on with the rest of the tutorial.

[Solomon]

I already did that:

When I click the _ipb database it opens the list of tables in the panel on the left and structurally displays of these tables on the right. At the top of it there is a menu consisting of Structure | SQL | Search | Query | Export | Import | Operations

Now when I click on Operations in the menu, I do not get the option Table Type as in the tutorial which says:

1 - Go into phpmyadmin, and edit your forums_sessions table (click forums_sessions, then click Operations). Where it says [b]Table Type[/b] change it from "MyISAM" or "InnoDB" to "HEAP". This keeps the sessions table in memory, and since the sessions table is accessed at least once on every page load, this improves access performance to the table.

If on the other hand I click on a single table name in the panel on the left, it opens that table only on the right and if I now click on Operations (in the menu) it gives some more options, but still not the option Table Type. Even if the option appeared here this would only allow to change the Table Type for this particular table. But I want to change all the 88 tables in one go.

[Darkside_RG_merged]

If on the other hand I click on a single table name in the panel on the left, it opens that table only on the right and if I now click on Operations (in the menu) it gives some more options, but still not the option Table Type. Even if the option appeared here this would only allow to change the Table Type for this particular table. [color="#FF0000"]But I want to change all the 88 tables in one go[/color].

:unsure: Why would you want to do this? The sessions table is the only one you would want to be running in memory if you changed all the rest you would lose your entire database on a shut down assuming it's even possible to convert all the tables.



The tutorial at a guess was done using an older version of phpmyadmin the above screen shot is from a shiny new recent version.

Table Options > Storage Engines

Change MyIASM to Memory which is synonymous with "heap" in older versions of phpmyadmin.

[Solomon]

Hi Darkside_RG_merged,

Thanks a ton for clearing up my misinterpretations. I changed the said table engine to HEAP and all is well. :blush:

Thank you Invisionary. Now, quite suddenly your suggestion makes sense.. :)

[Darkside_RG_merged]

Glad you got t sorted :)

I must admit it took me a little while to puzzle it out when I did it, hopefully the guide might get updated some when

[Solomon]

I guess I counted my chicken too soon.. This morning I realized that the change I initiated yesterday did not really take place. :(

The reason for this is that you must make some changes in the default level: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html and quoting from there:

When you create a new table, you can specify which storage engine to use by adding an ENGINE or TYPE table option to the CREATE TABLE statement:

CREATE TABLE t (i INT) ENGINE = INNODB;

CREATE TABLE t (i INT) TYPE = MEMORY;

The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.

If you omit the ENGINE or TYPE option, the default storage engine is used. [color="#FF0000"]Normally, this is MyISAM, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the default-storage-engine or default-table-type option in the my.cnf configuration file. [/color]

You can set the default storage engine to be used during the current session by setting the storage_engine or table_type variable:

SET storage_engine=MYISAM;

SET table_type=BDB;

When MySQL is installed on Windows using the MySQL Configuration Wizard, the InnoDB storage engine can be selected as the default instead of MyISAM. See Section 2.4.8.4.5,

[Solomon]

Problem resolved!

Here are the revised steps:

Click:
1. cPanel
2. phpMyAdmin
3. _ipb database
4. highlight ipf_sessions (in the list of tables in left panel)
5. Operations in the menu at the top (in the right panel)
6. Pull-down menu for Storage Engine (under Table Options)
7. Select HEAP
8. OK

Please note: When you click (after undergoing the procedure above) on the main _ipb database in the left panel, you will find the table now shows HEAP under the TYPE Column for ibf_sessions. If this is not the case, you may have to repeat the steps above.

[AaronM]

Sorry posted in wrong spot

[johndoe73]

This sounds like a very handy tutorial. Does anyone know where to find it?

[Solomon]

You will find it here: http://resources.invisionpower.com/index.p...mp;article=5974

There are many other interesting tips on servers and optimizing these to your needs: http://resources.invisionpower.com/index.p...amp;category=37 ;)

[johndoe73]

Thanks

[civ]

Any benchmarks from those who have made this change? Even anecdotal is fine...