#1  
Old 18th January 2011, 21:35
FeraTechInc FeraTechInc is offline
Senior Member
 
Join Date: Feb 2007
Posts: 136
Thanks: 3
Thanked 5 Times in 5 Posts
Default Optimizing MYSQL?

Is there a way to optimize MYSQL when making joins so it runs faster with multiple concurrent users?

I've already run the optimization through PHPMYADMIN on all tables.

Any other suggestions would be very much appreciated.
Reply With Quote
Sponsored Links
  #2  
Old 18th January 2011, 21:37
FeraTechInc FeraTechInc is offline
Senior Member
 
Join Date: Feb 2007
Posts: 136
Thanks: 3
Thanked 5 Times in 5 Posts
Default

Forgot to mention,

Running ISPConfig 3 on Ubuntu 9.10
Quad Core Processor with 8gig RAM and 2TB Software Raid 1 Drives

Have already run MYSQL Tuner as well.
Reply With Quote
  #3  
Old 18th January 2011, 22:12
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,504
Thanks: 813
Thanked 5,265 Times in 4,129 Posts
Default

Which software causes the load on mysql? Which mysql table type do you use in that database?
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #4  
Old 21st January 2011, 03:07
FeraTechInc FeraTechInc is offline
Senior Member
 
Join Date: Feb 2007
Posts: 136
Thanks: 3
Thanked 5 Times in 5 Posts
Default

The software we are running is a CMS system called Xoops. Similar to Joomla, Wordpress or Drupal.

The types of database is:
MyISAM
Reply With Quote
  #5  
Old 21st January 2011, 11:16
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,504
Thanks: 813
Thanked 5,265 Times in 4,129 Posts
Default

If the cms uses a lot of joins, then it might be faster to use innodb instead of myisam.

See also:

http://www.mysqlperformanceblog.com/...am-and-innodb/


What you can try is this:

1) Make a backup of the database, in case that you have to switch back or something goes wrong when changing table type.
2) Change the database table type to innodb for all myisam tables.

as alternative, you can e.g. make a copy of the database in mysql, change type to innodb and then change the database name in the cms to the new database.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #6  
Old 21st January 2011, 11:19
FeraTechInc FeraTechInc is offline
Senior Member
 
Join Date: Feb 2007
Posts: 136
Thanks: 3
Thanked 5 Times in 5 Posts
Default

Is there a simple way of doing this through phpmyadmin?
Reply With Quote
  #7  
Old 21st January 2011, 11:27
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,504
Thanks: 813
Thanked 5,265 Times in 4,129 Posts
 
Default

If you like to follow my second alternative (doing a copy of the db), then you can do everything in phpmyadmin:

1) select the database, go to "operations" > "Copy database to", enter a new database name, select "Structure and data", "CREATE DATABASE before copying", "Add AUTO_INCREMENT value" and "Switch to copied database" and click on go. This should create a copy of the database with all data and switch phpmyadmin directly to the new database.
2) Now, in this new database, click on every table that is of type myisam, click on "operations", go to "Storage Engine" and select "InnoDB" instead of "MyIsam" and click on "go". Repeat this for every table in the database.

An alternative method might be to:

1) Export the database in phpmyadmin and save it as .sql file.
2) Edit the .sql file with a text editor and replace all occurrences of "ENGINE=MyISAM" with "ENGINE=InnoDB".
3) Create a new database in phpmyadmin and import the sql dmp again.

But depending on the size of your database, the db might be too large for phpmyadmin to export and import it, in that case you would have to use the mysqldump command on the shell for the export and the mysql command for reimporting the db.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.

Last edited by till; 21st January 2011 at 11:30.
Reply With Quote
Reply

Bookmarks

Tags
mysql, optimize

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CPU load locks up box. Apache or MYSQL related. crypted General 61 29th October 2010 22:16
Squirrelmail login failure Cracklefish Installation/Configuration 9 30th June 2010 20:38
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 13:58
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 02:38.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.