Thoughts on MySQL 4.x.x?

[dzeanah]

We ran through a problem that was recently diagnosed to a badly performing/badly configured sendmail daemon, and in the process of rtying to fix it we installed a copy of MySQL 4.0.9. Actually, I kind of wanted to anyway after reading the eweek (http://www.eweek.com/article2/0,3959,293,00.asp) review on it:Overall, Oracle9i and MySQL had the best performance and scalability (see charts, images 1 and 2 in slideshow (http://www.eweek.com/slideshow/0,3018,sid=0&s=1590&a=23120,00.asp)), with Oracle9i just very slightly ahead of MySQL for most of the run.Anyway, afterward I noticed that the load on my server dropped to nearly nothing -- from an average of 1.5-2 with 300 users online to .2-.4 now. I mean an incredible reduction.

I think it's because a lot of the queries are being repeated, and aren't having to hit the disk. Here:[root@internal root]# mysqladmin extended-status
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Questions | 4519135 |
| Qcache_queries_in_cache | 5025 |
| Qcache_inserts | 1529226 |
| Qcache_hits | 2076735 |
| Uptime | 163928 |
+--------------------------+------------+
[root@internal root]#
Now, I'm far from a SQL guru, but doesn't the above suggest that almost half of the DB queries were answered by checking existing results in the cache, rather than hitting the disk for it? :eek:

So far, I'm impressed. George/EVA2000 called it "unproven" though. Anyone have any issues with v4 that I should look out for?

[dzeanah]

In case you don't know what I'm talking about : From the eweek article:
MySQL 4.0.1's new, extremely fast query cache is also quite notable, as no other database we tested had this feature. If the text of an incoming query has a byte-for-byte match with a cached query, MySQL can retrieve the results directly from the cache without compiling the query, getting locks or doing index accesses. This query caching will be effective only for tables with few updates because any table updates that clear the cache to guarantee correct results are always returned.

[ccd1]

Cute. Is it fully vB compatible?

[dzeanah]

Originally posted by baragon0
Cute. Is it fully vB compatible? That's what I'm asking. The board is running great -- makes me glad I went to the effort to rebuild the server a few days ago -- but I'm curious about whether there are any issues I should be aware of...

[JeffJ]

Originally posted by dzeanah
That's what I'm asking. The board is running great -- makes me glad I went to the effort to rebuild the server a few days ago -- but I'm curious about whether there are any issues I should be aware of... I'm using mysql 4.0 gamma and it screams. I love it.... migrated two weeks ago and have not had a simgle problem.

[hamy]

dzeanah,
Do you mind sharing the way you installed it and your my.cnf? How does it work with huge databases?
Tanx in advance.

[dzeanah]

Originally posted by hamy
dzeanah,
Do you mind sharing the way you installed it and your my.cnf? How does it work with huge databases?
Tanx in advance. Actually, my co-admin put it up. I'd guess it was compiled locally as we are using 4.0.9gamma and the latest rpm that I can find on rpmfind.net is 4.0.1.

Have no idea how it works on huge databases -- ours is maybe 150M so far. Ask me in a few months -- that 150M happened since new-years's eve. ;)

As for server details, they're in this (http://www.vbulletin.com/forum/showthread.php?threadid=63085) thread.

Anyone else trying MySQL 4.whatever?

[dzeanah]

Yeah, I'm monopolizing the thread, but I'm still excited.

Turns out this thing dropped disk access enormously too. Was seeing a consistent 1 block written per 30 blocks read via vmstat before the upgrade. Now it's somewhere between 1:4 and 1:7 (hasn't been long enough to tell more than that).

[plsoucy]

Hi,

The load diminution with MySQL 4 seems pretty interesting, however I know that it is still in beta and not officially ready for production for now.

If I upgrade to MySQL 4.09 and run into problems, will it be easy to go back to 3.23? What will I exactly need to do? Uninstall 4.09 and install 3.23.54a? Anything else?

Thanks,

Pierre-Luc Soucy

[TommyBALL]

Originally posted by plsoucy
The load diminution with MySQL 4 seems pretty interesting, however I know that it is still in beta and not officially ready for production for now.It's not in beta any more... it's now in gamma ;) :p

[hamy]

Originally posted by plsoucy
Hi,

The load diminution with MySQL 4 seems pretty interesting, however I know that it is still in beta and not officially ready for production for now.

If I upgrade to MySQL 4.09 and run into problems, will it be easy to go back to 3.23? What will I exactly need to do? Uninstall 4.09 and install 3.23.54a? Anything else?

Thanks,

Pierre-Luc Soucy

The best way of upgrading is upgrading server and client so that if anything goes wrong you may just uninstall them and go back to the older version I think.

Cheers

[eva2000]

well vB has been developed and tested only on mysql 3.x branch and NOT on 4.x so although vB may work with 4.x, there could be some issues that may pop up with vB/mysql 4.x

i'd recommend for problem free vB usage, mysql 3.23.54a

[hamy]

This is a very good point eva2000. Agreed 100% . But does vb plan to work with Mysql 4?

[eva2000]

Originally posted by hamy
This is a very good point eva2000. Agreed 100% . But does vb plan to work with Mysql 4? i'm not a developer, but i'd say in future, yes

remember mysql 4.09 is still a gamma release....

[legolas]

Originally posted by plsoucy
The load diminution with MySQL 4 seems pretty interesting, however I know that it is still in beta and not officially ready for production for now.

That isn't true.
4.0.9 is "gamma" which in Mysql AB-speak means it's production-ready, and no longer beta.

Many sites are using mysql4 now, including Yahoo.
http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:129514:200301:fnndidnbebodhdkgmifh

[Floris]

Originally posted by legolas
That isn't true.
4.0.9 is "gamma" which in Mysql AB-speak means it's production-ready, and no longer beta.

Many sites are using mysql4 now, including Yahoo.
http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:129514:200301:fnndidnbebodhdkgmifh

gamma does not mean final or gold or stable. Which means it is ready enough and can be used, but not adviced to be used in a production real life content site.

[eva2000]

Originally posted by xiphoid
gamma does not mean final or gold or stable. Which means it is ready enough and can be used, but not adviced to be used in a production real life content site. my understanding is gamma, means not ready for production use, but for testing ??? i'm not a dev though :o

[Floris]

Originally posted by eva2000
my understanding is gamma, means not ready for production use, but for testing ??? i'm not a dev though :o That is why I typed 'ready enough'.

[TECK]

Originally posted by eva2000
well vB has been developed and tested only on mysql 3.x branch and NOT on 4.x so although vB may work with 4.x, there could be some issues that may pop up with vB/mysql 4.x

i'd recommend for problem free vB usage, mysql 3.23.54a VB won't install in 4.0.9gamma, PHP4.0.3 and IIS5.1. It will give you a connect failure, persistent or not.
To bad, the queries cache sounds really cool.
If anyone have a tip for my.ini file, please let me know.

[MUG]

Originally posted by TECK
VB won't install in 4.0.9gamma, PHP4.0.3 and IIS5.1. It will give you a connect failure, persistent or not.
To bad, the queries cache sounds really cool.
If anyone have a tip for my.ini file, please let me know. Works fine for me. Apache/1.3.27, PHP/4.3.0, MySQL 4.0.9-gamma

[NTLDR]

Originally posted by TECK
VB won't install in 4.0.9gamma, PHP4.0.3 and IIS5.1. It will give you a connect failure, persistent or not.
To bad, the queries cache sounds really cool.
If anyone have a tip for my.ini file, please let me know.

Perhaps its the way the Win32 version was compiled?

[TECK]

Originally posted by NTLDR
Perhaps its the way the Win32 version was compiled? I started a new thread...

[hamy]

Originally posted by TECK
VB won't install in 4.0.9gamma, PHP4.0.3 and IIS5.1. It will give you a connect failure, persistent or not.
To bad, the queries cache sounds really cool.
If anyone have a tip for my.ini file, please let me know.

Worked fine for me as well :) . PHP 4.3.0 , Mysql 4.0.9 .

[Achaeon]

Worked fine for me as well :) . PHP 4.3.0 , Mysql 4.0.9 .

Any further word on this from VBulletin now that MySQL 4.x.x is out of gamma?

[Scott MacVicar]

kier and me have both upgraded our local servers without problem and Chris should be upgrading these forums tonight.

[Achaeon]

kier and me have both upgraded our local servers without problem and Chris should be upgrading these forums tonight.

Awesome, I'll keep an eye on this forum then and if all is well I'll try it out on webhostingtalk.

[Chris Schreiber]

I upgraded my server as well to 4.0.12, along with another HUGE vBulletin installation.... the upgrade was simple, and the results are very, very impressive. I have yet to see any problems with vBulletin running with this version. Like Scott said I'll likely upgrade vb.com tonight as well.

After you upgrade, remember to run the "mysql_fix_privilege_tables" script to add the new permission table changes to your "mysql" database (this is the only added thing I had to do outside of what a normal point-release upgrade requires). This isn't required, but highly recommended.

Also, check for and rename any parameters in your /etc/my.cnf file that have been renamed. The old ones will still work but are depreciated... for example sort_buffer is now sort_buffer_size. There's a good list of variables you should change right here, along with an overview of the enhancements you'll see from this version: http://www.mysql.com/doc/en/Upgrading-from-3.23.html

And for best performance, make sure you add the "query_cache_size" parameters. By default, this was set to 0 until I added the parameter into my my.cnf file. Users on an active dedicated server should set this parameter to 32M or 64M to start with.... I'll post again if even higher settings might be useful for people that have the extra memory to spare.

[Floris]

I will contact Sander from xxlwebhosting with a request to look into upgrading to the mysql 4 stable and the cpanel if it already has it. Sounds promising so far! I already need to ask him to see if he needs to upgrade his kernel due to that big butt exploit.

[Kier]

MySQL 4 seems speeeeeedy :)

[eva2000]

I upgraded my server as well to 4.0.12, along with another HUGE vBulletin installation.... the upgrade was simple, and the results are very, very impressive. I have yet to see any problems with vBulletin running with this version. Like Scott said I'll likely upgrade vb.com tonight as well.

After you upgrade, remember to run the "mysql_fix_privilege_tables" script to add the new permission table changes to your "mysql" database (this is the only added thing I had to do outside of what a normal point-release upgrade requires). This isn't required, but highly recommended.

Also, check for and rename any parameters in your /etc/my.cnf file that have been renamed. The old ones will still work but are depreciated... for example sort_buffer is now sort_buffer_size. There's a good list of variables you should change right here, along with an overview of the enhancements you'll see from this version: http://www.mysql.com/doc/en/Upgrading-from-3.23.html

And for best performance, make sure you add the "query_cache_size" parameters. By default, this was set to 0 until I added the parameter into my my.cnf file. Users on an active dedicated server should set this parameter to 32M or 64M to start with.... I'll post again if even higher settings might be useful for people that have the extra memory to spare.
good to hear Chris... :)

[Floris]

Good,
I will request an upgrade then :)

[Chris Schreiber]

We're running MySQL 4.0.12 here now too :)

[diettalk]

Any hints to us mysql newbies before we attempt to upgrade?

[legolas]

Don't forget to back up your database(s), although I upgraded to 4.0.9 on one and 4.0.10 on another machine and had no problems to speak of.

Once you get it running, like chris said, make sure you enable the query cache. Our stats show a bit more than half of cachable queries hitting the cache (so a bit less than half of the queries end up inserting stuff into the cache).

Chris, do you actually see those 32 or 64 MB of qcache utilized? Most I've ever seen is either 4 or 8 mb (I just remember it wasn't more than half of the 16mb qcache).

[diettalk]

I tried to install. I assume I will need to uninstall the old mysql (which was redhat 8 default) and install these, correct?

Error messages...

warning: MySQL-client-4.0.12-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-1.11-10
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1017-3
libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-8.0.7

[Chris Schreiber]

Chris, do you actually see those 32 or 64 MB of qcache utilized? Most I've ever seen is either 4 or 8 mb (I just remember it wasn't more than half of the 16mb qcache).

Nope it's not using the whole 64MB of cache, so I would recommend using 16MB for most sites, or maybe 32MB for larger sites.

[ahbao]

I tried to install. I assume I will need to uninstall the old mysql (which was redhat 8 default) and install these, correct?

Error messages...

warning: MySQL-client-4.0.12-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-1.11-10
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1017-3
libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-8.0.7

try this

Go to http://www.mysql.com/downloads/mysql-3.23.html (http://www.mysql.com/downloads/mysql-3.23.html) and download the RPM for "Dynamic Client Libraries" (it should be named MySQL-shared-3.23.56-1.i386.rpm).

Run this command:
rpm -ivh --force --oldpackage MySQL-shared-3.23.56-1.i386.rpm

Then re-install your MySQL 4.0 shared rpm (use -i not -U):
rpm -ivh --force MySQL-shared-4.0.12-0.i386.rpm

it works when I got the libmysqlclient.so.10 error during the httpd restarting

[thenetbox]

http://www.mysql.com/press/release_2003_10.html

:D

I will try installing it on my server today or tomorrow.

[LancerForums]

I want to try and install MySQL 4.0.12 from the RPMs available on their site. Would I just run MySQL-server-4.0.12-0.i386.rpm and then MySQL-client-4.0.12-0.i386.rpm, do I just run one of those, or do I have the wrong files all together? I'm on a RH Linux server.

I've backed up my my.cnf so I can re-implement that in case it is overwritten. I also plan to add the cache line as well.

Thanks,

Mark

[Jadelit]

After you upgrade, remember to run the "mysql_fix_privilege_tables" script to add the new permission table changes to your "mysql" database (this is the only added thing I had to do outside of what a normal point-release upgrade requires). This isn't required, but highly recommended.

How would you do this in WindowsXP?

C:\Network\MySQL\scripts\

[thenetbox]

I ran 3 files

MySQL-server-4.0.12-0.i386.rpm
MySQL-client-4.0.12-0.i386.rpm
MySQL-devel-4.0.12-0.i386.rpm

I did rpm -Uvh for each one

I want to try and install MySQL 4.0.12 from the RPMs available on their site. Would I just run MySQL-server-4.0.12-0.i386.rpm and then MySQL-client-4.0.12-0.i386.rpm, do I just run one of those, or do I have the wrong files all together? I'm on a RH Linux server.

I've backed up my my.cnf so I can re-implement that in case it is overwritten. I also plan to add the cache line as well.

Thanks,

Mark

[Dontom]

I also installed mysql 4 today. there were no problems (my db is nearly 2 gigs).

Load seems to be lower now, but it is to early to say by how much.
Tom

Mysql4 Step by Step: http://forum.rackshack.net/showthread.php?s=&threadid=19869

[Scott MacVicar]

did you make sure that query_cache_size had a value set in my.cnf?

[fury]

Indeed. I installed 4.0.11-gamma on my system and had no idea the query cache wasn't enabled by default. I saw a huge difference the instant I set it.

[Dontom]

did you make sure that query_cache_size had a value set in my.cnf?

yes i put
set-variable = query_cache_size=16M
into my.cnf, everything shows up perfect in the extended status script...
tom

[mikie]

Also, check for and rename any parameters in your /etc/my.cnf file that have been renamed. The old ones will still work but are depreciated... for example sort_buffer is now sort_buffer_size. There's a good list of variables you should change right here, along with an overview of the enhancements you'll see from this version: http://www.mysql.com/doc/en/Upgrading-from-3.23.html



According to your upgrade link, above the following is true:

myisam_bulk_insert_tree_size is now bulk_insert_buffer_size
query_cache_startup_type is now query_cache_type
record_buffer is now read_buffer_size
record_rnd_buffer is now read_rnd_buffer_size
sort_buffer is now sort_buffer_size
warnings is now log-warnings
err-log is now --log-error (for mysqld_safe)

However after i upgraded my.cnf and restarted myql i got errors all over the place and mysql would refuse to start.

030407 14:00:05 mysqld started
/usr/sbin/mysqld: ERROR: unknown variable 'record_buffer_size=2M'
030407 14:00:05 mysqld ended
030407 14:00:05 mysqld ended

Now if record_buffer is now record_buffer_size why is 4.012 spitting out these errors?

What am i missing here?

[eva2000]

According to your upgrade link, above the following is true:

myisam_bulk_insert_tree_size is now bulk_insert_buffer_size
query_cache_startup_type is now query_cache_type
record_buffer is now read_buffer_size
record_rnd_buffer is now read_rnd_buffer_size
sort_buffer is now sort_buffer_size
warnings is now log-warnings
err-log is now --log-error (for mysqld_safe)

However after i upgraded my.cnf and restarted myql i got errors all over the place and mysql would refuse to start.

030407 14:00:05 mysqld started
/usr/sbin/mysqld: ERROR: unknown variable 'record_buffer_size=2M'
030407 14:00:05 mysqld ended
030407 14:00:05 mysqld ended

Now if record_buffer is now record_buffer_size why is 4.012 spitting out these errors?

What am i missing here?
post your my.cnf contents

[Gwynar]

Does this like alright?

[mysqld]
datadir=/usr/local/var/
socket=/tmp/mysql.sock
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = read_buffer_size=1M
set-variable = sort_buffer_size=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
skip-innodb
set-variable = query_cache_limit = 1M
set-variable = query_cache_size = 32M
set-variable = query_cache_type = 1

[mysql.server]
user=mysql
basedir=/usr/local/

[safe_mysqld]
open_files_limit=8192

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer_size=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[eva2000]

Does this like alright?

[mysqld]
datadir=/usr/local/var/
socket=/tmp/mysql.sock
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = read_buffer_size=1M
set-variable = sort_buffer_size=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
skip-innodb
set-variable = query_cache_limit = 1M
set-variable = query_cache_size = 32M
set-variable = query_cache_type = 1

[mysql.server]
user=mysql
basedir=/usr/local/

[safe_mysqld]
open_files_limit=8192

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer_size=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
try removing all

set-variable =

from your my.cnf and restart mysql

also to make sure you're using 4.0.1.2 type the output you get from running this command via ssh

mysqladmin -u root -p version

[Philip]

According to your upgrade link, above the following is true:

myisam_bulk_insert_tree_size is now bulk_insert_buffer_size
query_cache_startup_type is now query_cache_type
record_buffer is now read_buffer_size
record_rnd_buffer is now read_rnd_buffer_size
sort_buffer is now sort_buffer_size
warnings is now log-warnings
err-log is now --log-error (for mysqld_safe)

However after i upgraded my.cnf and restarted myql i got errors all over the place and mysql would refuse to start.

030407 14:00:05 mysqld started
/usr/sbin/mysqld: ERROR: unknown variable 'record_buffer_size=2M'
030407 14:00:05 mysqld ended
030407 14:00:05 mysqld ended

Now if record_buffer is now record_buffer_size why is 4.012 spitting out these errors?

What am i missing here?

it's supposed to be read_buffer_size, rather than record_buffer_size

4.0.12 works fine with the new ones on my end.

[eva2000]

it's supposed to be read_buffer_size, rather than record_buffer_size

4.0.12 works fine with the new ones on my end.
he has read_buffer_size in the my.cnf he posted

[Philip]

I was refering to that error:

....
030407 14:00:05 mysqld started
/usr/sbin/mysqld: ERROR: unknown variable 'record_buffer_size=2M'
....

[Gwynar]

Just wanted to make sure that you didn't confuse me for mikie... I think eva had asked him to post his my.cnf but I sort of stepped in and posted mine before he did.

Sorry for the confusion.

[Philip]

k, I was just trying to help mikie... Glad we're all on the same page now :D

[Gwynar]

It's all good. :)

I probably should have made it clear that I wasn't the same person that had initially asked for help with their my.cnf file.

[Mr. X]

How would you do this in WindowsXP?

C:\Network\MySQL\scripts\

Thats what I need to know too.

Secondly, why does phpinfo show that the client version of mySQL is 3.x when I just upgraded to the latest? What else do I have to do? I just ran the installer, and so far so good afaik. It reports the latest version numbers in the winadmin tool etc.

[seanf]

That's the version of the PHP tools used to connect to/manipulate MySQL, not the MySQL version

Sean :)

[Brian Briscoe]

MySQL 4.1.0 Alpha was just released. :D

Upgrade Time! ;)

[timo_copts]

i setup to MySQL 4.1.0
and vb board run good put every short time i got this error

Database error in vBulletin 2.3.0:

Invalid SQL: SELECT COUNT(*) AS threads FROM thread
mysql error: Can't open file: 'thread.MYI'. (errno: 145)

mysql error number: 1016

Date: Friday 19th of September 2003 07:29:46 PM
Script: http://www.*****.net/forum/forum/index.php
Referer:
i tink today the server turn off bz cutting power??
any solve to repair thios error els
c:mysql/bin/mysqlcheck -r (d.b.name) thread???