SOLVED: Problem with *very* slow replication, FreeBSD 6.2
An update for those actually paying attention. I have been fighing unusual performance issues with replication between FreeBSD 6.2 machines. The unusual part is that while replication would never top 10 writes per second (even while the master was taking hundres of writes per second), the slave always reported zero seconds behind. This is on servers with less than 1% CPU used. The actual problem was not with writing the binlog, or the slave SQL thread, but the actual transfer of the binlog across the network. After days of running, the slave would be many Gigs behind the master. While debugging I tried many things including updating from 5.1.19 to 5.1.22, rebuilding with WITH_PROC_SCOPE_PTH=yes, and even rebuilding using linuxthreads. None of this worked. The problem was rfc1323... Window scaling *SHOULD* have improved performance given that this is a jumbo frame GigE network. For reasons I don't understand, with rfc1323 enabled the data transfer rate for replication is limited to a ~ 200Kbyte/sec (I do not see the same slowdown for http or scp transfers). To verify I rebuilt both systems back to default (native threads), re-inited the Master-Master replication loop, shutdown one of the servers and inserted several million records on the live system (about 1.8Gbyte of binlog). On restarting the second system it read the binlog into the relay log at 20 - 25 Mbyte/sec. The seconds behind master value showed sane values, and it processed the relay-log backlog at about 6600 writes/sec until finished. Further testing included 3,000 inserts/sec to each of the servers (6,000/sec total) with the master/master replication loop active. During a run of 10,000,000 inserts to each server replication was never more than 2 seconds behind. On Tue, 30 Oct 2007, Christopher E. Brown wrote: On Thu, 25 Oct 2007, [EMAIL PROTECTED] wrote: Not sure that I get the whole picture. We have been running replication since about 4.0 and we have been through several upgrades and are now at 5.0.27. The 'show slave status' always gives us an accurate reflection of where it is at which is usually 0 seconds behind. Occasionally, it falls behind if the master is really busy (2200 q/s with about 70% being updates/deletes/inserts). At those times the slave tops out at about 1200 q/s of which most are db mods of some kind and some selects since we have reports running against the replica and it will fall behind temporarily. Can you send show slave status and show master status as well and typical mytop outputs for master and slave? That might let me be able to provide more help. Bob Unfortunatly I had to tear down replication as it was causing problems with the master. (The master will not delete binlogs that a slave is still loading, when the slave is 40 file behind disk gets short). CPU load was near zero on both systems (98% idle or better). Disk load is minimal. The slave is always up to date with relay file processing and reporting zero seconds behind. In short, everything looks fine. What happens is that the master - slave binlog feed runs very slow (no more than abount 10 writes/sec). So, afer a few days the slave is still reporting zero seconds behind, and it is zero seconds behind the relay log. The problem is that while the master is currently writing binlog 650, the slave is actually zero seconds behind the feed, but the binlog feed has fallen 20 - 30 files behind (our binlog rolls at 256M). Since there is no load issue, I expect there is a timing or trigger issue with the master side proc doing the binlog dump, or the slave side receiving it. I can stop/start replication and/or reload both servers, it still holds. I see the replication restart, with the slave running zero seconds behind the relay log, the binlog feed starts up right where it left off but the feed only runs at about 10 writes a second. Are your running native or LinuxThreads? This is smelling like threading issue to me (we are running FreeBSD 6.2 with native threading and 5.1.19). The exact same setup was pre-built on Linux systems (2.6.x Slackware) before being built out on the production systems (FreeBSD 6.2). During the testing 1000 writes/sec were no problem (small/simple table, fits in memory). When I forced a backlog of approx 2GB by shuttong down the slave on restart the binlog - relay log feed ran at over 25MB/sec until caught up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warning about truncated data
Hi, I'm reloading a backup and I'm getting loads of warnings like : +-+--++ | Level | Code | Message| +-+--++ | Warning | 1265 | Data truncated for column 'page_public' at row 1 | +-+--++ I'm using 5.0.32-Debian_7etch1-log, according to these entry it has been fixed : http://bugs.mysql.com/bug.php?id=25815 But it's really unclear wheter it really truncate data, or if it's just a spurious warning. anyone had the same issue ? Thanks, -- http://www.myspace.com/sakuradropsuk : credit runs faster http://www.w-fenec.org/ Rock Webzine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to emulate a DATE() in MySQL 4.0?
Hello mysql, I have a PHP script that I have written and it runs beautifully on current MySQL and PHP sites BUT, it barfs on MySQL 4.0 because I am using the DATE() to extract the Date from a timestamp. Does anyone remember how to do an equivalent function in 4.0? I have searched through hundreds of pages and can not find anything that looks like it might work. Essentially I need to extract the -MM-DD from a timestamp field that I populated in a PHP script so I can do a diff against a current timestamp. Here is the query that works on current and falls over dead on 4.0: SELECT count(SimulatedTimeSet) as count_live FROM `Simulator` WHERE DATE( `SimulatedTimeSet` ) = DATE(CURRENT_TIMESTAMP) I appreciate in advance any clue to get me in the right directions other than upgrading the system. I don't have any control over that one... TIA -- Best regards, mikesz mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: append on insert
You would need to have an auto_increment field and your primary key would be the item id (or what you call it) field + the auto_increment field. Mysql will then handle incrementing the field if there is already a record with the same item id. Usually the auto_increment field will contain 1. This gives you a unique sequence for each group of records. I'm almost positive this only works on MyISAM fields since InnoDB gets the increment number once on startup not on insert. On Nov 2, 2007, at 8:31 PM, Kevin Waterson wrote: I have a table of item I wish to have the value of the item incremented if there is a duplicate. I looked at ON DUPLICATE KEY UPDATE but this modifies the exsisting value. If the item is my-item and this already exists, I need to make it my-item-2 or even my-item-123 where 123 is the key. Kind regards Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grouping
It sounds to me like you might be trying to find the standard deviation or the variance, which are functions you can use right in your query. On Nov 2, 2007, at 7:37 AM, Octavian Rasnita wrote: Hi, I have a table with the following columns: symbol date value I want to select all the symbols (grouping by symbols) between 2 specified dates, and calculate the percent of change for each symbol. The percent of change is (the value from the last date of the symbol - the value from the first date) / the value from the first date. For example I have: SMB1, 2007-01-01, 1000 SMB1, 2007-03-15, 2100 SMB1, 2007-10-10, 1300 ... (other symbols) And the result of the select should be: SMB1, 0.3 Is it possible to do this with an MySQL query? Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to emulate a DATE() in MySQL 4.0?
[EMAIL PROTECTED] wrote: Hello mysql, I have a PHP script that I have written and it runs beautifully on current MySQL and PHP sites BUT, it barfs on MySQL 4.0 because I am using the DATE() to extract the Date from a timestamp. Does anyone remember how to do an equivalent function in 4.0? I have searched through hundreds of pages and can not find anything that looks like it might work. Essentially I need to extract the -MM-DD from a timestamp field that I populated in a PHP script so I can do a diff against a current timestamp. Here is the query that works on current and falls over dead on 4.0: SELECT count(SimulatedTimeSet) as count_live FROM `Simulator` WHERE DATE( `SimulatedTimeSet` ) = DATE(CURRENT_TIMESTAMP) Try FROM_UNIXTIME(). Have you read the manual section on date and time functions? There are quite a few. You don't need to read hundreds of pages. http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: append on insert
Kevin Waterson wrote:
I have a table of item
I wish to have the value of the item incremented if there is a duplicate.
I looked at ON DUPLICATE KEY UPDATE but this modifies the exsisting value.
If the item is my-item and this already exists, I need to make it my-item-2
or even my-item-123 where 123 is the key.
For the record, what you're doing sounds like an abuse of databases, but...
INSERT INTO item (`key`)
SELECT CONCAT('my-item',
(SELECT IF(COUNT(*) = 0, '', CONCAT('-', COUNT(*)))
FROM item WHERE `key` LIKE 'my-item%'));
Before anyone points out the problems with this, of which there are
several, remember I'm only giving Kevin enough rope to hang himself ;-)
Kevin, this design is not first normal form and will cause you trouble.
Consider what will happen if you insert my-item, my-item, and then my-ite.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which is faster when deleting rows? In() or Separate Delete stmts?
If I have a large table with 20 million rows, is it going to be faster to use one delete statement like: delete from mytable where rcdid in(20,300,423, 9) to delete 10-100 random records using the primary index RcdId or should I use separate delete statements for each RcdId as in: delete from mytable where rcdid = 20; delete from mytable where rcdid = 300; delete from mytable where rcdid = 423; ... delete from mytable where rcdid = 9; I'm concerned the IN() clause will cause a full table scan and that will take quite a while to find the rows even though I'm using the primary key RcdId. TIA Mike MySQL 5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Account Problems. Can't register server with new account
Good evening. I have mysql cluster installed and I'm trying to start the mysql server with a new user account recently created, and I'm having trouble accessing the server with the new account. [EMAIL PROTECTED] bin]# ./mysql -u usuario1 -p Enter password: ERROR 1045 (28000): Access denied for user 'usuario1'@'localhost' (using password: YES) The new user does exists and I know the problem is not password related. mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select User from user; +--+ | User | +--+ | mysql| | root | | usuario1 | | | | mysql| | root | +--+ Thanks in advance -- View this message in context: http://www.nabble.com/Account-Problems.-Can%27t-register-server-with-new-account-tf4744425.html#a13566959 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is faster when deleting rows? In() or Separate Delete stmts?
Hi mos wrote: If I have a large table with 20 million rows, is it going to be faster to use one delete statement like: delete from mytable where rcdid in(20,300,423, 9) to delete 10-100 random records using the primary index RcdId or should I use separate delete statements for each RcdId as in: delete from mytable where rcdid = 20; delete from mytable where rcdid = 300; delete from mytable where rcdid = 423; ... delete from mytable where rcdid = 9; I'm concerned the IN() clause will cause a full table scan and that will take quite a while to find the rows even though I'm using the primary key RcdId. You can convert it into an equivalent SELECT and use EXPLAIN to see if it's using a table scan. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign keys on non-unique columns (problem)
Hi, I have a problem with my foreign keys. I have the following two tables: CREATE TABLE keylist ( KeylistId INTEGER NOT NULL, UserId INTEGER NOT NULL, PRIMARY KEY (KeylistId, UserId)); CREATE TABLE user ( UserId INTEGER NOT NULL PRIMARY KEY, AdditionalKeylist INTEGER); A keylist stores multiple user IDs for each keylist ID. A user has a reference to one keylist to keep multiple additional keys. (My key is the same as a user ID.) Now I have added this foreign key constraint: ALTER TABLE user ADD FOREIGN KEY (AdditionalKeylist) REFERENCES keylist (KeylistId) ON DELETE SET NULL; Which is supposed to mean the following: When I delete a keylist and there's no remaining row with this keylist ID, then find the users that are referencing it and set their AdditionalKeylist value to NULL so that they doesn't keep an invalid reference. The problem: When a keylist ID exists twice and I delete one of them, the user's AdditionalKeylist value is set to NULL immediately, although another keylist ID instance exists. I have read through the MySQL documentation about foreign keys and understand that referencing a non-unique column (i.e. not a candidate key) is not standard SQL and that InnoDB doesn't exactly do what I want (it ignores the remaining relevant rows). From SQLite (which doesn't currently enforce foreign keys) I have learned a trigger that can do the same (haven't tested it yet, though), but to extend it to that look for other instances check, I need it to understand the WHEN part of my trigger, which seems to be commonly known but unsupported (and undocumented) by MySQL 5.0. Also, MySQL requires uncommonly high privileges to create a trigger which is not an option in the field (I'm planning to release my application for use on common web space). I hope you understand my problem. There's two potential solutions which both don't work for me. Is there a third? Can I create this kind of referential integrity on the DBMS level at all? Is my design bad? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: append on insert
This one time, at band camp, Baron Schwartz [EMAIL PROTECTED] wrote:
INSERT INTO item (`key`)
SELECT CONCAT('my-item',
(SELECT IF(COUNT(*) = 0, '', CONCAT('-', COUNT(*)))
FROM item WHERE `key` LIKE 'my-item%'));
Kevin, this design is not first normal form and will cause you trouble.
Consider what will happen if you insert my-item, my-item, and then my-ite.
I am aware of the break in 1NF but need use the item_name as a key. I was hoping
MySQL had some sort of sequence available to auto append to similar keys.
I could of course check if the key exists, and then change the value in the
application layer to my-item-2, but was hoping to do it in a single query.
Kind regards
kevin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys on non-unique columns (problem)
On 03.11.2007 22:52 CE(S)T, Yves Goergen wrote: Is my design bad? I should explain why I do it this way at all. There's some other tables in my system that need to keep a list of keys (i.e. user IDs) for several actions. A message (one of the tables) has one keylist for read access, one for alter access and a third one for reply access. The lists (if not NULL = empty) contain a list of authorised users to perform the respective action. (If NULL, everybody's allowed.) In an older design, I had a separate table for each list type, which was 6 tables altogether. Instead of a keylist.KeylistId, there was e.g. a MessageReadAccessKeys.MessageId referencing message.MessageId. First, this makes 5 more tables and second, I doubt that it would solve my non-unique foreign key problem. What I need is to store those keys for several tasks, object types and instances of them. What I would like to have is the DBMS keeping those references valid. I guess my last chance is implementing this check in my application (which I already had before I chose to entirely rely on referential integrity and then deleted these few lines). Please tell me if there's a better way. PS: I searched a little more and found out that PostgreSQL also forbids foreign keys referencing non-unique columns (like in SQL92) due to serious bugs in the past, which is one more reason why I don't want to keep it this way. (The whole thing should be somewhat portable...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql claims table does not exist. but it does
On Oct 31, 2007 3:22 AM, robert rottermann [EMAIL PROTECTED] wrote: Hi there, I have a strange problem. I am using Server version: 5.0.45 SUSE MySQL RPM I made a backup of a database by copying its directory like so: mv urulu urulu_X then I recreateded the database, played a little with it and then dropped it again. the I moved the old database in place again. now when I want to access the tables of the database I get an error that the tables do not exist, alltough the showtables lists them correctly. what can I do to fix that? to explayin thin a bit better, here a dump of an mysql dialog Hi List, For what it is worth, I have been using linux-based stuff for the last 7 years. Each and every time I have a problem with something not existing it has boiled down to a matter of permissions. 100% in my experience. HTH, -- http://kimbriggs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
