Marian Hettwer wrote:
Starting by looking at errors and then making sure a replication setup
doesn't have any errors is always a good thing before saying it doesn't
work. So, when no errors happen, may be many things will work just fine.
I haven't said that it doesn't work. I said its bloody slow. Thats a
huge difference...
Well, may be some errors fix might take care of some speed issues. But I
can tell you that it is not slow. But sorry if I miss understood you
from the start. Look to me that you considered it unusable. If not, then
I stand corrected.
060915 17:33:29 [Warning] Could not increase number of max_open_files to
more than 8096 (request: 8192)
- --> You mentioned something about that later in your mail. Could be a
problem, eh?
Go read it again. I think I pointed it many times so far.
If you still have issue with this, I will be glad to point you in the
right direction, but do your homework first and try it out. The answer
was provided very clearly and repeated as well and IS in the document
about it as well.
Hold your breath. I read it, I changed the parameter down to 4096 and
should be fine now.
Good thing.
060915 17:33:29 [Warning] mysql.user table is not updated to new
password format; Disabling new password usage until
mysql_fix_privilege_tables is run
- --> Yeah well, I could run mysql_fix_privilege_tables, however, I
bet it
has nothing todo with my problem.
That's not fix privilege. Men, go read please. Look for old_password.
And again, this has most likely nothing to do with performance, so I
stick with the old password scheme and nevermind (for now).
I only pointed out the problem and assumptions done along the way and
why. Up to you do take action or not on it. Your systems, your choices. (;>
Like it's been said before.
"You are the master of your domain!"
I don't take credit for that. (:>
060915 17:33:29 [Warning] Can't open and lock time zone table: Table
'mysql.time_zone_leap_second' doesn't exist trying to live without them
060915 17:33:29 [Warning] Neither --relay-log nor --relay-log-index were
used; so replication may break when this MySQL server acts as a slave
and has his hostname changed!! Please use
'--relay-log=babelfish45-relay-bin' to avoid this problem.
- --> As I'm not about to change the hostname, I'll fix that problem
later.
That is not the host name here. Go read the manual. They tell you to
configure the my.cnf to use a log file reflecting your host name, not to
change your host name. I think spending some time reading will help you
work on the software you want to use. This is well explain in the log as
well as in the manual.
They even tell you what to use:
--relay-log=babelfish45-relay-bin
Where does it say hostname needs to be changed?
It said, if I don't configure the my.cnf accordingly, and then change my
hostname, I'll be screwed.
May be.
"so replication may break when this MySQL server acts as a slave and has
his hostname changed!!"
Just pointed it out as you said you needed to change your host name
instead of starting from the beginning with the proper configuration
inside my.cnf. That's all I said.
Sorry if I miss understood the meaning of that part as well that you wrote.
One way to fix, is using relay-log in my.cnf, but again, I can skip this
as the whole setup is a Proof of Concept, nothing more. It's not in
production so stay cool.
I am.
Again, this has nothing to do with the performance issue encountered.
Yes, I do know that it's not a clean setup and I should do it right.
If it deserved to be setup, then it deserver to be done right. Even for
testing, if not, then what are you testing really?
OK, may be that's just me, sorry!
Look lie at a minimum this works.
I haven't said that it doesn't work... I said it's working, but its slow.
Sorry again. I miss understood the meaning of:
"As soon as replication starts, mysql gets very unresponsive:"
Plus it was a comparaison between how well it work on Linux and how
"unresponsive:" it is on OpenBSD.
I really need to improve my understanding of the English language
obviously. Sorry again for not understanding it the first time! (;>
I stand corrected!
I have no clue how big your database might be or not. Nor how many
tables, etc.
all in all it's 175 MyISAM files, but only a small part of them are
actually open and in use.
As you see above, only 11 tables are open. But some of them are rather
large (400 - 600 MB).
But look like form previous errors that it try to use table that are not
available. So, if you really want a good mirror, you need to make sure
it will replicate all the tables it needs, or are link together, or the
replication process will stop, only the bin log files will keep growing.
Whut? It doesn't say that it can't replicate, because a table is
missing. I think you're mixing something up. More over, I would see this
error in "show slave status\G".
OK. Simply test for you. Try to update a table on the master, like your
timezone just for fun and see what the slave will do? Or worst, if the
timezone table is not fix and then it try to update a records that end
up in a conflict on the slave.
Any guess as to what you will see in your logs may be.
Then query might well be done on that server, one out of many like you
said and some users will not get the proper data, but then you will try
to find out why. Might find it right away, or days later.
I don't know. Do you check your "show slave status\G" every day, or as
the start of any problems?
Just trying to help, that's all.
Clue on that is if you have more then one relay-bin file on the slave,
then it is safe to assume the replications stop. Not the copy over of
the data, but the update of the tables.
the tables are updating, the replication is running.
Didn't say it wasn't replicating. I was providing a quick clue as to
what you will see if it wasn't. That's all.
And as I said, access to MySQL itself is pretty slow.
As in: getting a "show slave status\G" needs between 4 and 14 seconds,
or a "mysqladmin proc stat" needs up to 16 seconds.
And this has really nothing to do with "how big is your database" or
"how many open tables do you have". Not at all.
I don't think I said it was normal. That's why I asked for the error
logs first. See, may be it's just me, but before I say something is not
working properly or is slow, etc. I make dam sure I have no errors
before I clam that. If I see errors, then I correct my problem until I
see none and then when all look good to me, no errors, etc and I still
have a problem, then research it, read the doc again and try a few
things and if all failed, then I asked why it might be.
I corrected the errors which were severe (max-open-files), but I would
correct any error (as in old_passwords).
That would be a good thing. So, if you see no more errors and do some
quick tests, then you know you can start looking else where then.
Just trying to help.
Look to me there is still plenty of place that work can be done before
saying it's not working.
D'Oh. Again, I never said it's not working.
Sorry, I miss understood the opening of the threads. My bad!
It's still doing exactly that. IO_THREAD gets data from master,
SQL_THREAD is working at nearly 50 queries / second to write all the
data it got.
May be tables are lock, may be IO are slow, may be drives are slow, may
be the drivers for the network card you use is not working well, may be
limits are reach ( hint about open table above), may be errors are
slowing the process down, may be errors on new password format for each
query on slave slow down the process instead of using the old_password
standard that was changed between version here.... Get it?
That's way to much of a speculation... but okay, I saw horses throwing
up in the kitchen, so I wouldn't be too surprised either if it has
something to do with old_passwords *sigh*
It was only a suggestions. No errors mean something else to look for
when all is not working as one wish. But as long as there is errors,
then the setup is obviously wrong to start with.
Make sense no?
Oh it is there. Besides, I used a tarball to get a starting point to
start the replication. This tarball is probably 1 hour behind in
replication. So I should have seen a replication drift of approx 1 hour.
Which I did. And while the box is replicating and trying to catch up, of
course I should be able to connect to MySQL and do some queries.
Sure you can. I my case, I don't even start with a tarball. I turn up
new slave and let them catch up from a long time ago. I do have dump
regularly for backup, but I don't even bother to do that for a new slave
and it catch up very quickly, well many hours, but it does catch up no
problem and does work very well as well.
thats far to time consuming for our setup here. If the client has a dump
which is like 24 hours ago, the poor client needs at least 24 hours to
catch up... nope, not an option.
The tarball way is okay (and is also an option according to mysql)
I didn't say it wasn't an options. I simply said that I didn't do it.
That's all, as I don't see a problem catching up from slave to master
even with multi GB of logs. I guess mine are just faster, that all. But
yes you sure can start with tar ball. But one stupid reason why I don't
is because as explain in the docs, sometime the format of their database
changed. So, doing it from the SQL query with a dump with the --opt is
fine for me and I know that even if they changed the format, then my
data will end up being good in what ever format they use.
See, I didn't go back in the doc to look, but may be, just may be they
might not recommend to use tarball as a mean of moving databases and
tables in between versions. Just a stupid idea I have I guess.
Sure, if I move from a new server, same platform, i386 for example where
both run 5.0.22, sure doing a tarball is much faster and sure is OK as well.
But I do recall reading some places that they changed the way some
variable types where store in the database (file) and they also added
new type as well. So, how a new version will deal with the same variable
type that changed in the store file between 4.1 and 5.0. I can't say, I
never tested it.
But to me it is safer to start from the data. Call me paranoid and
that's OK.
3 rows in set (0.00 sec)
Not in my setup. It doesn't matter wether I use "show processlist" from
mysql, or wether I use mysqladmin.
So, there is place for improvement then. We just need to find where.
Start by getting ride of all the errors you have and then will see what
might be next.
ack.
Thank you.
Thanks for pointing out. I'll fix that right now.
open-files-limit is now at 4096
Good, but also check how you start MySQL as well ok?
did it.
Great!
Not that I think you hit that limit here as you didn't say anything
about error 9, but should one day start to have a lots of tables and
come close to this limits, then you will not know why that is.
I will know now...
Good, but still check how you started MySQL ok?
did it.
Great!
It looks like doing a "sudo mysqld_safe" as my user has the same effect.
mysqld is running as _mysql
Although mysqld_safe is running as root. hm hm...
I'll better go with the correct way, changing to _mysql. Thanks for
pointing out.
You are welcome. See details above for using of class on login.
Let see when no errors are present in the near future.
ack.
Hopefully it will help. if not, then will see why that might be, but
anyway, I hope this did help you some regardless.
Best,
Daniel
