-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Daniel,
Daniel Ouellet wrote: >> Okay... but by looking in iostat, it looks like pretty low traffic. 1 to >> 2 MB/sec. A higher number of transfers per second, though. > > > You are right! Yes But the question is also, is there something else > then... > > A few ideas below. Sure not all apply for sure, but just to show you > that assuming it's the same setup and from 4.1 to 5.0 makes no > difference, or have no impact might not always be true. > Well lets see... >> Well... it's getting data from the master all the time, so I guess, it >> will be in waiting for i/o all the time. >> However, this is by "design" (if you like to speak of design in regards >> to MySQL). >> And still I should be able to connect to mysql and do a show slave >> status\G quite fast (not waiting 6 seconds to complete that task...). >> >> It gets even worse, if I try to do a select on some database. Yeah, the >> database could be locked while I do that, but since there are 50 queries >> / second coming in, the database still should have enough time to answer >> (in between being locked). > > > May be. But it may depend on many things including file system use too. > Does you Linux version actually writes the data to the drive, or to > cache and flush time to time. Meaning faster to process locks if you do > use any? If it crash, do you actually lost some data that were not > written to disk in that case? If it crash on OpenBSD, the data > will/should be there. I am not saying this is THE reason, but consider > it however. > This could be a likely course. I'm not that familiar with the internals of Linux's VM. All I know is, we're using ext3 on those Linux boxes and yes, a hard crash will most likely render at least some tables (those who were opened? *g*) unuseable... Granted, it is an advantage if OpenBSD doesn't destroy the MyISAM files, however, this is a MySQL replication setup with backups and everything. The client replicants are available in quite a large number. You could speak of a read-only load balance cluster of MySQL machines. If one dies? Who cares, reinstall the machine, get your backup and back to work :) > Also some design in MySQL might affect you too if you do use locks and > you might here, I don't know the data you use: "WRITE locks normally > have higher priority than READ locks to ensure that updates are > processed as soon as possible. This means that if one thread obtains a > READ lock and then another thread requests a WRITE lock, subsequent READ > lock requests wait until the WRITE thread has gotten the lock and > released it. You can use LOW_PRIORITY WRITE locks to allow other threads > to obtain READ locks while the thread is waiting for the WRITE lock. > You should use LOW_PRIORITY WRITE locks only if you are sure that > eventually there will be a time when no threads have a READ lock." > I'll keep that in mind, thanks. > Also something that may well apply to you as you refer to timezone table > that you do not replicate over. Did you consider this when mixing 4.1 to > 5.0: > > #If the master uses MySQL 4.1, the same system time zone should be set > for both master and slave. Otherwise some statements will not be > replicated properly, such as statements that use the NOW() or > FROM_UNIXTIME() functions. You can set the time zone in which MySQL > server runs by using the --timezone=timezone_name option of the > mysqld_safe script or by setting the TZ environment variable. Both > master and slave should also have the same default connection time zone > setting; that is, the --default-time-zone parameter should have the same > value for both master and slave. Note that this is not necessary when > the master is MySQL 5.0 or later. This is some new info to me, and it looks like I really should fix this timezone issue. Thanks for pointing out. > > Anyways, many others issues you should/need to consider when mixing, or > trying to mix version of master/slave 4.1 to 5.0: > > http://mysql.speedbone.de/doc/refman/5.0/en/replication-features.html > > Then do you use trigger as well? I am almost sure this doesn't apply to > you, but needs to be consider when mixing version for replications setup. Nope, no triggers. > Some more issues with mixing 4/1 version as master to 5.0 as slave: > > If the master uses MySQL 4.1, you must always use the same global > character set and collation on the master and the slave, regardless of > the MySQL version running on the slave. (These are controlled by the > --character-set-server and --collation-server options.) Otherwise, you > may get duplicate-key errors on the slave, because a key that is unique > in the master character set might not be unique in the slave character > set. Note that this is not a cause for concern when master and slave are > both MySQL 5.0 or later. I did this. if using the wrong collation / character set, the MySQL 5.0 replicant won't even start to replicate... > > > Also for speed improvements on slave: > > http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html > > and a few more: > > Q: How can I use replication to improve performance of my system? > > A: You should set up one server as the master and direct all writes to > it. Then configure as many slaves as you have the budget and rackspace > for, and distribute the reads among the master and the slaves. You can > also start the slaves with the --skip-innodb, --skip-bdb, > --low-priority-updates, and --delay-key-write=ALL options to get speed > improvements on the slave end. In this case, the slave uses > non-transactional MyISAM tables instead of InnoDB and BDB tables to get > more speed by eliminating transactional overhead. > This is indeed basically what we do here... Standard replication setup. >> >> Okay, flame me, but, the same replication setup like it is in use here >> on a Debian Woody with Linux 2.4.31 takes 0,00 seconds to do a select >> count(*) foo; while OpenBSD needs from 0,83 to 7,56 seconds to complete >> the request :-/ >> Strange... > > > I wouldn't say it's the same setup. > Umm... yes, okay, it's not the same. > One use 4.1 and one use 5.0 and I can't speak to this as I haven't check > in a very long time, but does Woody and OpenBSd do use the same file > systems and that may be there isn't something else slowing you down > here. I only asked as I do not know the answer, but this is a > possibility. I know just to well that many on Linux use a different file > system for speed at the price of data integrity. Does this have any to > do with the speed difference you try to isolate. May be, then may be > not. I can't answer this with knowledge as I do not know for sure and I > wouldn't want to say something wrong either. But food for thought however. Yes indeed. Our Linux boxes are using ext3 and OpenBSD is using what? FFS? I don't know all the differences between those two, but I guess there are more than enough. > >> mysql> select count(*) from foo; > > > I wouldn't expect to see that big of a difference, but any of the points > above apply to this setup here? To see .8 seconds and then almost 8 > seconds later, but with just may be 100 records more might point you > else where, or some suggestion above might help as well. Some might > query from cache, or not written to disk yet and other might query from > real disk access after the lock is release... > First I thought my index is broken, so I did a repair table foo, but that has no effect at all (as in, my index was all right). I wouldn't expect to see such a big difference either... > I sure don't see this in any of my slave, but again all my volume insert > on the master are with delay insert. And if I do need, fast read access, > I can always use SELECT HIGH_PRIORITY as well. But never needed so far. > I can't change how the data is stored within the master, just can't do. My boss would probably kill me ;-)) Anyway, we need the data inserted at the master as fast as possible available at the slaves. Insert foo; select foo; And of course we don't want to have those selects on our master. Think of a web platform, users clicking around all the time, some inserts are generated and of course changes must be displayable... >> The Linux box is running the same hardware like the OpenBSD box. Only >> difference is that the Linux box is running MySQL 4.1.14 whereas OpenBSD >> runs 5.0.22 > > > Might be a good amount of difference. > True, but not as big as 0,03 seconds for a query vs. 8 seconds. Never ever... >> Granted, you can't compare those two systems. >> On the other hand, the Linux box is in production, taking the 50 queries >> / second from replication while handling another 50 queries / second >> due to being in production. Counts up to 100 queries per second avg. > > > Are you sure the configuration is the same for both. > the my.cnf is basicly the same. I only changed the max-open-files parameter for OpenBSD. Actually i added this parameter. It wasn't in use on our MySQL 4.1 boxes. > May comparing the results of: > > /usr/local/libexec/mysqld --verbose --help > (skip the top long part about the description of the variable, but > still good readin) then compare from both your linux and OpenBSD, you > might find interesting things. (:> > > And also: > > mysqladmin -p variable > > and > > mysqladmin extended-status > > And by doing a real comparison between the three results, will you know > if the setup is really the same. After that, if they are exactly really > the same and you still see big difference between to identical setup > with the two different systems, but running the same version, then may > be there is something else that might be affecting it, like the file > system use, etc. > > Just a thought to know if they are really the same... > If time permits and I can get back to MySQL on OpenBSD, I'll definetly will compare the global variables and of course I'd try to use the same MySQL version (and I'd like to take a look into the compile options, as they do affect the performance of MySQL). > >> Any more ideas? Should it be all related to the replication setup and >> Disk I/O ? > > > May be, may be not. I wouldn't say for sure yet. If you really want to > know the exact answer as to why obviously. > >> I do know that MySQL is a bitch in regards to I/O and VM. >> 'tis no fun to handle huge files: >> - - -bash-3.1$ ls -l /usr/local/mysql/data/*relay* >> - - -rw-rw---- 1 _mysql _mysql 197288820 Sep 18 11:45 >> /usr/local/mysql/data/babelfish45-relay-bin.000032 >> - - -rw-rw---- 1 _mysql _mysql 31 Sep 18 09:59 >> /usr/local/mysql/data/babelfish45-relay-bin.index >> - - -rw-rw---- 1 _mysql _mysql 72 Sep 18 11:45 >> /usr/local/mysql/data/relay-log.info >> - - -bash-3.1$ >> >> Yeah, the relay binlog is _that_ big... > > > Nothing there out of this world. The limit on my log files is 1Gb and > are process no problem. > > Hope this give you some to work with anyway. > > If not, at a minimum a few things to think about and to look at if you > really want to get the answer. all very helpful information. I'll have a look into it as soon as I have the time to do so... Unluckily I'm not getting paid for evaluating MySQL on OpenBSD (although I'd like to), but getting my salary for administrating those bloody Linux boxes ;) best regards, Marian iD8DBQFFD5jggAq87Uq5FMsRAslXAJwOxuWOnvjYIQl+R5Zlai0it+SKoACcD6Bx 54XkvaCDJMTnbUnkefxef9U= =oyZg -----END PGP SIGNATURE-----
