-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Daniel Ouellet wrote: > Marian Hettwer wrote: >> >> 060915 17:33:29 [Warning] /usr/local/libexec/mysqld: ignoring option >> '--low-priority-updates' due to invalid value 'ON' >> >> - --> Seems like that parameter doesn't exist anymore in MySQL 5.0 ... >> I'll look into it... > > > 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... > >> 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. > >> 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). > >> 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. 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. 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. > >> 060915 17:33:29 [Note] /usr/local/libexec/mysqld: ready for connections. >> Version: '5.0.22-log' socket: '/tmp/mysql.sock' port: 3306 OpenBSD >> port: mysql-server-5.0.22 >> 060915 17:33:29 [Note] Slave SQL thread initialized, starting >> replication in log 'foo-bin.000040' at position 358083515, relay log >> './babelfish45-relay-bin.000004' position: 37101832 >> 060915 17:33:29 [Note] Slave I/O thread: connected to master >> '[EMAIL PROTECTED]:3306', replication started in log 'foo-bin.000040' at >> position 358083543 > > > Look lie at a minimum this works. > I haven't said that it doesn't work... I said it's working, but its slow. > >>> 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". > 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. >> 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). > > 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. >>> >> 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* >> 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) >>> >> The statement I was talking about was "show slave status" or "show >> processlist". Both statements have nothing to do with locked tables or >> the like. > > > And you sure can get the results quickly as well: > > mysql> show processlist; > +-----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------+------------------+ > > | Id | User | Host | db | Command | Time | State > | Info | > +-----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------+------------------+ > > | 1 | system user | | NULL | Connect | 8596289 | Waiting for > master to send event | NULL | > | 141 | system user | | NULL | Connect | 0 | Has read > all relay log; waiting for the slave I/O thread to update it | NULL > | > | 187 | root | localhost | NULL | Query | 0 | NULL > | show > processlist | > +-----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------+------------------+ > > 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. >>> I just wonder if you expected it to be all mirror and ready as soon as >>> you issue the start slave? >>> >> You probably haven't read my first email correctly. Once again, the >> "mysqladmin proc status" is taking very very long. This has nothing to >> do with catching up with the master. I do know that it'll take some >> time :) > > > Start by getting ride of all the errors you have and then will see what > might be next. > ack. >>> >> 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. >> >>> 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. >>> >> 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. ./Marian iD8DBQFFD5vugAq87Uq5FMsRAv3LAKCvNdMN+qkHnCD8sUJUzYAlT/NGdwCfSHVZ nySg0IHyK2xzXc4mY1gVm60= =h0mk -----END PGP SIGNATURE-----
