I have tried to install MySQL 4.0.16 over my 3.23.58 install. The server is a Dual Xeon 2.4 with 1GB ecc regged ddr and 2 x 18,3GB SCSI harddisks, runninh win2k, IIS5.0, AcxtivePerl 5 and php 4.3. I followed all instructions for upgrade; Shutdown and remove the mysql service and installed 4.0.16. Since I have installed 4.0.16 I have big problems. At inconsequent times the MySQL server becomes really slow. What I see in the MySQL control center you can see in the attachment. The server seems to hang on the copy to temp table. When I kill that process it works again but otherwise I can take up to 1 minute before it reponds again. Does anyone know what might have caused this and how to solve it? We have about 200 - 350 people visiting the boards annd I get a lot of complaints about the long waiting times. Sometimes it works fine for an hour, but then it starts again without any obvious reason. I tried installing 3.23.58 again, but still the same problem.
Thanks for the help guys. I have turned off persistent connections in php.ini (though in threads they where off already) but I don't want to change the my.ini just yet. Could you please mail me your my.ini (without your password off-course:-)) so I can just test it with yours? the e-mail address is: [][email protected][/]
Thanks,
Ron
Here is my ini as it is at the moment. I have tried default ini settings as well. Makes no difference.
Code
<br /># This will be passed to all mysql clients <br />[client] <br />port=3306 <br />#socket=MySQL <br /> <br /># Here is entries for some specific programs <br /># The following values assume you have at least 32M ram <br /> <br /># The MySQL server <br />[mysqld] <br />port=3306 <br />user=root <br />password=***************** <br />#socket=MySQL <br />skip-locking <br />set-variable = key_buffer=256M <br />set-variable = max_allowed_packet=1M <br />set-variable = table_cache=256 <br />set-variable = sort_buffer=1M <br />set-variable = record_buffer=1M <br />set-variable = myisam_sort_buffer_size=64M <br />set-variable = thread_cache=8 <br /># Try number of CPU's*2 for thread_concurrency <br />set-variable = thread_concurrency=8 <br />log-bin <br />server-id = 1 <br /> <br /># Uncomment the following rows if you move the MySQL distribution to another <br /># location <br />#basedir = d:/mysql/ <br />#datadir = d:/mysql/data/ <br />tmpdir = c:/tmp/ <br /> <br /># Uncomment the following if you are using BDB tables <br />#set-variable = bdb_cache_size=64M <br />#set-variable = bdb_max_lock=100000 <br /> <br /># Uncomment the following if you are using Innobase tables <br />#innodb_data_file_path = ibdata1:1000M <br />#innodb_data_home_dir = c:\ibdata <br />#innodb_log_group_home_dir = c:\iblogs <br />#innodb_log_arch_dir = c:\iblogs <br />#set-variable = innodb_mirrored_log_groups=1 <br />#set-variable = innodb_log_files_in_group=3 <br />#set-variable = innodb_log_file_size=5M <br />#set-variable = innodb_log_buffer_size=8M <br />#innodb_flush_log_at_trx_commit=1 <br />#innodb_log_archive=0 <br />#set-variable = innodb_buffer_pool_size=16M <br />#set-variable = innodb_additional_mem_pool_size=2M <br />#set-variable = innodb_file_io_threads=4 <br />#set-variable = innodb_lock_wait_timeout=50 <br /> <br /> <br />[mysqldump] <br />quick <br />set-variable = max_allowed_packet=16M <br /> <br />[mysql] <br />no-auto-rehash <br /># Remove the next comment character if you are not familiar with SQL <br />#safe-updates <br /> <br />[isamchk] <br />set-variable = key_buffer=128M <br />set-variable = sort_buffer=128M <br />set-variable = read_buffer=2M <br />set-variable = write_buffer=2M <br /> <br />[myisamchk] <br />set-variable = key_buffer=128M <br />set-variable = sort_buffer=128M <br />set-variable = read_buffer=2M <br />set-variable = write_buffer=2M <br /> <br />[mysqlhotcopy] <br />interactive-timeout <br /> <br />[WinMySQLadmin] <br />Server=C:/mysql/bin/mysqld-nt.exe <br />QueryInterval=10 <br />
Ok I just put in in c:/winnt and rebootet the server. Now I will have to try it for a few hours to see if this helps. In a few weeks we will be upgrading to a total RAM of 2GB. Can you tell me what settings to change for this extra RAM?
Those are new settings available to MYSQL4 that will help a lot.
And you say you're getting a total of 2 GB of ram? Then I would suggest maybe doubling your key_buffer size, but otherwise it should run pretty well with that setup.
The Key buffer is very tunable; I would not recomend it based on installed ram size at all in fact you want this value as small as you can without impacting performance, especially on Windows servers, it needs baselining, for instance I get no real benefit raising mine above 128MB where the key read ratio today is 0.000246443
The server seems to hang on the "copy to temp table". Does anyone know what (and whatfor) exactly MySQL is doing there? Could it be this temp table is misssing and therefor no data can be copyed into it?
The indexing service is on "Manual" (not started) and the virus scanner (AVG free edition) is not active (only on request bij the e-mail server). I will post the reports somewhat later cause the problem is not so easy to catch since the whole server drags down when it happens.
That looks like a search query without a timestamp being passed to it. Do you have a custom active threads link? The search engine has changed a bit in 6.4 and if you don't use the format found in the new links for "Past 24 hours", "Past 48 hours", etc, then it's going to do a full table scan when doing the search and copy to a temp table. Essentialy it will lock the table until it's done so every other query must wait for it to be done.
I've seen this on 2 other upgrades to 6.4, both having a custom link that points to recent posts that was not updated to the new 6.4 format.
why has this guy got so many connections? - 212.204.251.30
You definetly have too many connections in TIME_WAIT state similer to a denial of service attack, you need to make sure these registry settings are set, you will need to reboot too.
Look at my attachment rename it to .reg and run it, I am sure I havnt missed anything but PLEASE check the technet site before appling it and make sure YOU are happy with the settings.
BTW I think the TIME_WAIT connections is only compounding the main issue. Win2k will leave a connection like tis for 2 hours (7200000), I have mine set to 2.5 mins (150000)
212.204.251.30 is another (small) site running a phpbb on the same server (different database). I do not have a custom link to recent posts. We do have a custon header and a custon unregisterednav.tmpl and a registerednav.tmpl. Would you mind having a closer look at my database Rick when I send you my phpmyadmin password via e-mail? I have the feeling it must be database related. I have tried all other options with the help from Clint. I am getting pretty desperate since all visitors are starting to complaint about the slow responce of the server.
Actually looking a bit closer on that. That looks like a general search. That's one thing that we are going to need to change is the fact that right now if a user does a generic search it pretty much does scan through all rows in the post table and that can create this issue. But, this is the way .threads has been from the beginning so it shouldn't be a new issue. We probably will need to start building a search index table in an upcoming version to improve the search speed.
Well I have filed a support ticket at infopop, because even with all the help I got here, the problem is not solved. I think there is something wrong with the threads database since the other board runnng on the same server (phpbb) does not have the same problems. I think it must be upgrade 6.3 -> 6.4 related.
Infopop fixed the problem yesterday:-) It turned out to be a problem with some sort of table wich was not converted properly when upgrading to 6.4. This resulted in the search engine to do searches thrue the entire database (270MB) and that dragged the server down.
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.
