Marian Hettwer wrote:
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.
So, if so, why didn't I see it in your my.cnf file? Or did I miss it.
--low-priority-updates
--delay-key-write=ALL
I remember very well these two:
--skip-innodb
--skip-bdb
But not the above two...
I wouldn't say it's the same setup.
Umm... yes, okay, it's not the same.
Thank you!
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.
Well one is safe and does protect your data at the price of speed yes.
So, what do you put value on? A choice to make. I did mine.
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...
Except for io access on FFS vs Ext3. I am sure there is a good
difference here. But as I said, I am not qualify to answer that with
knowledge however. I know just what I need to know that FFS will help me
and be safe for me, even in crash oppose to others and that's all I need
to know. I pay a small price in performance a bit yes, but not like this
for sure. So something else play here. Still need to be isolated.
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 ;-))
Understood and granted. (:> Might be some flexibility in the server
configuration itself as pointed above however. You to decide what works
for you.
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...
I only pointed out choices and reasons. The choice is obviously yours.
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...
Until the reason is discover, we don't know do we?
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).
It would be interesting to know. As for the compiler options, I wouldn't
touch it, on OpenBSD anyway. The packages is very well done and works
very well out of the box. Trying to optimize this, you will only open an
other can of worms, but do as you wish. I say no however. (:> On less
you have a very strong reason to do so. To me it's just like a custom
kernel. Not worth the trouble, plus it doesn't always work better. But
if you have to, then it's possible assuming all is understood from what
can be changed and why...
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 ;)
Then do it for the fun of it and the learning as well. may be you might
find it easier to administrating those not bloody OpenBSD box instead! (:>
But do what works for you. I just never byte the argument that one is
not pay for doing things, etc.
Knowledge have no price really!
And I always find the payback to be much bigger! (:>
Best,
Daniel
