Previous Thread
Next Thread
Print Thread
Rate Thread
#251879 06/21/2003 11:17 AM
Joined: Feb 2001
Posts: 104
Journeyman
Journeyman
Offline
Joined: Feb 2001
Posts: 104
I'm looking at switching over to 4.X mysql, reading the info on mysql.com for upgrading from 3.x to 4.x it's talking about converting the isam tables over to myisamtables?

Can anyone shed some light on what's needed in respect to a threads database?


Sponsored Links
Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
Not much really, but you really should change the databases to myisam now, as isam support is deprecated and will be removed in the future. The conversion is quite simple, just run the command: mysql_convert_table_format <database>. Make sure noone else is using the database at the same time.

Joined: Feb 2001
Posts: 104
Journeyman
Journeyman
Offline
Joined: Feb 2001
Posts: 104
Thanks!

I've pretty much just got done stumbling though upgrading my testbed to 4.0.13, Musta done something right as it's up!


Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
And if you are running on a Windows host without Perl installed to run this script - which should be residing in the mysql/scripts folder you will have to run the ALTER TABLE commands one by one on the tables that are not MyISAM.


Nikos
Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
It might also be easier to just dump the data, drop the database and create a new one in myisam format and restore all data into it. At least if there are a lot of tables, then you won't have to write as much. =]

Sponsored Links
Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
Don't forget that there is also an upgrade script you'll need to run when you go from 3.x to 4.x that adds all the new permissions. I cannot rememmber the name of the script off hand, but it's noted in the docs.


Picture perfect penmanship here.
Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
it is mysql_fix_privilege_tables and it is located under the mysql/scripts foder. However if you are running your board on a Windows host you will need to run it with

Perl.exe <path-to-mysql>/scripts/mysql_fix_privilege_tables

If however you don't have perl installed, open the file in wordpad and also open a DOS window. Connect to mysql using the mysql command and your username/password selecting mysql as the database. Following that run the mysql commands outlined in the script. Hence you need to run the following script (substitute the values outlined in <> with your own data)
Code
<br /><path-to-mysql>/bin/mysql.exe -f --user=<username> --password="<password>"  --host="<host>" mysql<br />ALTER TABLE user type=MyISAM;<br />ALTER TABLE db type=MyISAM;<br />ALTER TABLE host type=MyISAM;<br />ALTER TABLE func type=MyISAM;<br />ALTER TABLE columns_priv type=MyISAM;<br />ALTER TABLE tables_priv type=MyISAM;<br /><br />alter table user change password password char(16) NOT NULL;<br />alter table user add File_priv enum('N','Y') NOT NULL;<br /><br />CREATE TABLE if not exists func (<br />  name char(64) DEFAULT '' NOT NULL,<br />  ret tinyint(1) DEFAULT '0' NOT NULL,<br />  dl char(128) DEFAULT '' NOT NULL,<br />  type enum ('function','aggregate') NOT NULL,<br />  PRIMARY KEY (name)<br />);<br /><br />alter table user add Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv enum('N','Y') NOT NULL;<br />alter table host add Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv enum('N','Y') NOT NULL;<br />alter table db add Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add Alter_priv enum('N','Y') NOT NULL;<br /><br /># The statements below might generate errors - ignore them<br />UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv;<br />UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv;<br />UPDATE host SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv;<br /><br />ALTER TABLE user<br />ADD ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL,<br />ADD ssl_cipher BLOB NOT NULL,<br />ADD x509_issuer BLOB NOT NULL,<br />ADD x509_subject BLOB NOT NULL;<br />ALTER TABLE user MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL;<br /><br />CREATE TABLE IF NOT EXISTS tables_priv (<br />  Host char(60) DEFAULT '' NOT NULL,<br />  Db char(60) DEFAULT '' NOT NULL,<br />  User char(16) DEFAULT '' NOT NULL,<br />  Table_name char(60) DEFAULT '' NOT NULL,<br />  Grantor char(77) DEFAULT '' NOT NULL,<br />  Timestamp timestamp(14),<br />  Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') DEFAULT '' NOT NULL,<br />  Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL,<br />  PRIMARY KEY (Host,Db,User,Table_name)<br />);<br />CREATE TABLE IF NOT EXISTS columns_priv (<br />  Host char(60) DEFAULT '' NOT NULL,<br />  Db char(60) DEFAULT '' NOT NULL,<br />  User char(16) DEFAULT '' NOT NULL,<br />  Table_name char(60) DEFAULT '' NOT NULL,<br />  Column_name char(59) DEFAULT '' NOT NULL,<br />  Timestamp timestamp(14),<br />  Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL,<br />  PRIMARY KEY (Host,Db,User,Table_name,Column_name)<br />);<br /><br /><br />ALTER TABLE columns_priv change Type Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL;<br /><br />alter table func add type enum ('function','aggregate') NOT NULL;<br /><br />alter table user<br />add Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER alter_priv,<br />add Super_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_db_priv,<br />add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Super_priv,<br />add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Create_tmp_table_priv,<br />add Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Lock_tables_priv,<br />add Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Execute_priv,<br />add Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Repl_slave_priv;<br /><br /># The statements below might generate errors - ignore them<br />update user set show_db_priv= select_priv, super_priv=process_priv, execute_priv=process_priv, create_tmp_table_priv='Y', Lock_tables_priv='Y', Repl_slave_priv=file_priv, Repl_client_priv=file_priv where user<>"";<br /><br />alter table user<br />add max_questions int(11) NOT NULL AFTER x509_subject,<br />add max_updates   int(11) unsigned NOT NULL AFTER max_questions,<br />add max_connections int(11) unsigned NOT NULL AFTER max_updates;<br /><br />alter table db<br />add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,<br />add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;<br />alter table host<br />add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,<br />add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;<br />


I hope this helps


Nikos
Joined: Feb 2001
Posts: 104
Journeyman
Journeyman
Offline
Joined: Feb 2001
Posts: 104
Thanks Guys. Looks like it's working like a charm....

Search results are down to about 2-5 seconds from a 20-30 second average on 3.23

I'm still somewhat curious why mysql focuses so hard one one query at a time, I was under the impression that it multi-tasked, and if thats the case ie how would more than one user be connected to the forum if it weren't? How come when someone does a search every other query grinds to a halt until the search is completed?


Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
Wow, that's a very significant increase in speed. I don't think I had that much, but I already used MyISAM tables in 2.23.x which is probably part of it. Also, I think I upgraded when something else was awry, so just the fact that the server worked was a plus for me. =] Actually, it wasn't MySQL that was the problem, but I eventually found the culprit and thought I'd go with 4 even though it was still in gamma back then...

Don't know about the one query thing, but it is true that usually 1-3 threads get almost all CPU power. It might have something to do that there is only one CPU so it can only do one thing at a time, even though it switches between threads very quickly. Maybe it is more efficient to stay on the thread doing the heavy query until it is finished to get it out of the way? I don't know, I have very little knowledge of the internals of thread switching and mysql...

Joined: Jun 2002
Posts: 37
User
User
Offline
Joined: Jun 2002
Posts: 37
Hmm... would you still need to run the mysql commands mentioned by dimopoulos if you did a fresh install of MySql 4.0.x on a fresh installation of Win2K Server (data transfered from a 3.23.x database)?

Sponsored Links
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
I believe you would... I think if you had the data in place when you upgraded it would ask you if you wanted to convert, but it's been a long time since I've updated to the 4.x series


- Allen wavey
- What Drives You?
Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
Yes you would only if you transferred your 3.23 mysql database across. In Windows you can just install 4.x, stop the service, change the ini file to the relevant path you want (say the D: drive or something like that) and then copy/move all the databases (3.23.x) to the new location. Starting the service again will use the new mysql database. In that case yes an upgrade is due as mentioned above. If however you just move all the databases and keep the mysql used from the fresh installation of 4.x there is no need to run the script.


Nikos
Joined: Jun 2002
Posts: 37
User
User
Offline
Joined: Jun 2002
Posts: 37
For my peace of mind and others who can benifit from this, here's what I did:

1. Dumped my database into a .sql file
2. Installed MySQL 4.0.13 on a new Windows server
3. Created a new database with no data
4. Uploaded my .sql file to the new database

If I am understanding this correctly, I do not need to run the script, correct?

Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
Yes you are right.


Nikos
Joined: Jun 2002
Posts: 37
User
User
Offline
Joined: Jun 2002
Posts: 37
Thank you!

Joined: Feb 2001
Posts: 104
Journeyman
Journeyman
Offline
Joined: Feb 2001
Posts: 104
Here's what I've got for a my.cnf

We literally went from 30 second plus querries to 2.3 seconds. Dunno if it was changing from 3.2 to 4.0 or changing from openbsd to redhat either way I'm a happy dude

We just added another 256Meg of ram, so I've got a total of 512 to play with... heh heh...



Code
  [mysqld]<br />datadir=/var/lib/mysql<br />socket=/var/lib/mysql/mysql.sock<br />max_connections     = 500<br />skip-innodb<br />query_cache_limit   = 1<br />query_cache_size    = 64<br />query_cache_type    = 1<br />interactive_timeout = 100<br />wait_timeout        = 100<br />connect_timeout     = 10<br />thread_cache_size    = 128<br />key_buffer          = 64M<br />join_buffer         = 1M<br />max_allowed_packet  = 16M<br />table_cache         = 2048<br />record_buffer       = 2M<br />sort_buffer_size    = 8M<br />read_buffer_size    = 8M<br />read_rnd_buffer_size=768K<br />max_connect_errors  = 10<br />thread_concurrency  = 2<br /><br /><br />[mysql.server]<br />user=mysql<br />basedir=/var/lib<br /><br />[safe_mysqld]<br />err-log=/var/log/mysqld.log<br />pid-file=/var/run/mysqld/mysqld.pid<br />

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Those settings will help a lot on most servers But you said you only have 512mb of ram... you may want to try query_cache_size = 32.. or even 16 and compare query times with server loads.

Too much cache can also be a bad thing.. so you may want to experiment a little bit I found a better comprimize changing that value on mine to 32, and I have a gig of ram.

Just something to consider

Joined: Feb 2001
Posts: 104
Journeyman
Journeyman
Offline
Joined: Feb 2001
Posts: 104
Thanks, I'll give that a look see.



Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,157
Joined: July 2001
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)