replication problem
hi all: I have three mysql database,tow run as master and the other one runs as slave. Some tables in the database have an autoincreament field named as 'rowid'. These tables have 100 records on master, but some of these tables on the slave only have thousands of record. The tables on the slave are not the same as those on master. When i use 'show slave status', i find that Last_Errno equals to 0. How to solve this problem.
Indexing? (Warning: relative newbie.)
Hi, all. I'm a long-time MySQL user who's only recently had to start learning some administrative stuff, largely because I finally have a decently-sized database. My database is about 100 GB; I'm using it -- via dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail is well-and-good with its IMAP front-end, I'm thinking of writing a Python front-end to do some queries directly against MySQL. But some of them take a l-o-n-g time. As an example, I've got a table with slightly over a million records; I'd like to be able to show (say) only IDs of messages under a half-MB. The query would look something like this: select physmessage_id,blocksize from dbmail_messageblks where blocksize 50; That query takes 50 minutes. A smidge long to wait. So I said, Huh. That's impressive. And I tried it without the physmessage_id: select blocksize from dbmail_messageblks where blocksize 50; That took 14 seconds. A bit more in my timeframe. Can I optimize this with indexing? Should I be using a different DB engine? Is there a site/book I should be learning DBA fundamentals from that might offer me direction for stuff like this? Sorry for all the newbie questions, but I haven't done serious database stuff since Foxbase/dBase III days. Things have changed a little since then. Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Indexing? (Warning: relative newbie.)
To answer your questions in no particular order, YES you can speed it up with indexing. You might want to first create an index on ( blocksize AND physmessage_id ). Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. Realistically, I can't see that taking more than a few seconds, at most, to execute. However, making the index might take a serious bit of time. Please let us all know how it does or does not work. Tim... -Original Message- From: Ken D'Ambrosio [mailto:k...@jots.org] Sent: Wednesday, June 24, 2009 11:07 AM To: mysql@lists.mysql.com Subject: Indexing? (Warning: relative newbie.) Hi, all. I'm a long-time MySQL user who's only recently had to start learning some administrative stuff, largely because I finally have a decently-sized database. My database is about 100 GB; I'm using it -- via dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail is well-and-good with its IMAP front-end, I'm thinking of writing a Python front-end to do some queries directly against MySQL. But some of them take a l-o-n-g time. As an example, I've got a table with slightly over a million records; I'd like to be able to show (say) only IDs of messages under a half-MB. The query would look something like this: select physmessage_id,blocksize from dbmail_messageblks where blocksize 50; That query takes 50 minutes. A smidge long to wait. So I said, Huh. That's impressive. And I tried it without the physmessage_id: select blocksize from dbmail_messageblks where blocksize 50; That took 14 seconds. A bit more in my timeframe. Can I optimize this with indexing? Should I be using a different DB engine? Is there a site/book I should be learning DBA fundamentals from that might offer me direction for stuff like this? Sorry for all the newbie questions, but I haven't done serious database stuff since Foxbase/dBase III days. Things have changed a little since then. Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexing? (Warning: relative newbie.)
Hey Tim, all On Wed, Jun 24, 2009 at 10:03 AM, Little, Timothytlit...@thomaspublishing.com wrote: Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. FYI: That only holds true for InnoDB, not for MyISAM. cheers, -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to fetch and calculate data from remote database and insert into local database
Hello I want to fetch and calculate the data from remote database(for example Japan) and insert them into my local database(for example usa), does mysql have such function to do it, or do I have to write a script using perl or other language to help achieving it? thank you in advance nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
composite vs single column secondary index in innodb
We have a composite primary key consisting of column a, column b, column c. We don't have a lot of variation on column a and it makes sense for us to cluster by a. Our queries are SELECT column c FROM table WHERE column a=something and column e=something. By creating a composite secondary index on column e (column a and column e), we're thinking we can reduce the amount of seek on the index on column e. My question is - is the column a value available already as bookmark lookups on single column index on e or would we better off creating a composite index on a and e if we always search by a and e? We're willing to accept some overhead on inserts for selects. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Indexing dynamics in MySQL Community Edition 5.1.34
Using MyISAM on a table loaded from 8GB of CSV, I am now adding some indices. In a separate shell I monitor the progress, alternately with `vmstat` and show full processlist. At first vmstat shows rapid progress; an example is # vmstat 5 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpdfree buffcache si sobi bo in cs us sy id wa st 1 6 0 8542108 256860 5394040000 0 152783 1314 298 3 4 60 32 0 1 6 0 8541000 256868 5394039200 0 147868 1301 287 3 4 68 25 0 1 5 0 8541380 256876 5394040000 0 150633 1310 277 3 4 72 21 0 1 6 0 8541108 256884 5394039200 0 152066 1307 271 3 4 71 21 0 1 7 0 8541116 256892 5394040000 0 151452 1312 311 3 4 64 29 0 1 6 0 8541992 256900 5394039200 0 192175 1402 295 3 4 66 26 0 1 6 0 8535684 256908 5394040000 0 108783 1227 276 3 4 69 24 0 1 8 0 8539116 256916 5394039200 0 155958 1318 262 3 4 82 11 0 1 6 0 8540860 256924 5394039200 0 166599 1340 328 3 4 66 27 0 1 9 0 8538512 256932 5394039200 0 165386 1336 319 3 4 62 31 0 1 6 0 8536776 256940 5394039200 0 175106 1358 303 3 5 66 27 0 2 0 0 8538884 256944 5394039600 0 187839 1402 305 3 5 70 22 0 1 1 0 8517060 256952 5394040000 0 188694 1379 307 3 4 66 27 0 1 10 0 8511604 256960 5394040000 0 175821 1335 294 2 5 69 24 0 1 10 0 8513340 256968 5394040000 0 164252 1335 300 3 4 65 28 0 2 0 0 8523012 256976 5394039200 0 151527 1318 305 3 5 60 33 0 1 10 0 8490152 256976 5394040000 0 178613 1352 301 2 5 67 26 0 2 0 0 8499576 256976 5394040000 0 142186 1319 302 2 5 69 23 0 1 10 0 8474280 256984 5394040000 0 185598 1348 301 2 5 57 36 0 2 9 0 8440676 256984 5394040000 0 166807 1334 306 2 5 53 39 0 1 9 0 8465228 256988 5394039600 0 114594 1268 306 1 6 56 36 0 1 9 0 16819736 256992 4554294400 0 185034 1342 301 1 6 56 36 0 1 9 0 20314428 257028 4213472400 0 186163 1371 282 1 6 56 37 0 1 9 0 20276856 257068 4217125200 0 166406 1342 281 2 5 59 34 0 1 9 0 20237672 257108 4220934400 0 166810 1333 252 2 5 56 37 0 At this point, and not for the first time, I stop vmstat and show full processlist. It says ++--+---+--+-+--+---+-+ | Id | User | Host | db | Command | Time | State | Info | ++--+---+--+-+--+---+-+ | 1 | root | localhost | cel_4x52 | Query | 542 | copy to tmp table | ALTER TABLE ntfelt ADD PRIMARY KEY (p, epoch, ssi, q, kind, ev, c), ADD UNIQUE INDEX pesqekvc(p, epoch, ssi, q, eqoch, kind, version, c), ADD INDEX tc(t, c), ORDER BY p, epoch, ssi, q, kind, ev, c | | 3 | root | localhost | NULL | Query |0 | NULL | show full processlist | ++--+---+--+-+--+---+-+ OK, so it is still indexing. Then I start up `vmstat` again, and it shows very different dynamics: # vmstat 5 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buffcache si sobibo in cs us sy id wa st 1 0 0 32429508 257248 3043925600 03076 0 0 100 0 0 1 1 0 32416124 257272 3045166800 0 2471 1020 111 6 0 94 0 0 1 0 0 32405096 257292 3046178000 0 2467 1017 109 6 0 94 0 0 1 0 0 32391828 257312 3047443600 0 2056 1019 107 6 0 94 0 0 1 0 0 32378684 257332 3048635600 0 2563 1040 109 6 0 94 0 0 1 0 0 32358224 257352 3050082400 0 3756 1038 109 6 0 93 0 0 1 0 0 32342600 257380 3051949200 0 3356 1035 112 6 0 93 0 0 1 0 0 32322140 257404 3053768800 0 3696 1023 108 6 0 94 0 0 I check
Re: Indexing dynamics in MySQL Community Edition 5.1.34
Actually, my characterization of the current state is wrong. It appears that one core is completely busy, I suppose MySQL does this indexing work in a single thread. Is it reasonable for indexing to be CPU bound? Thanks, Mike Spreitzer Mike Spreitzer/Watson/i...@ibmus 06/25/09 01:30 AM To mysql@lists.mysql.com cc Subject Indexing dynamics in MySQL Community Edition 5.1.34 Using MyISAM on a table loaded from 8GB of CSV, I am now adding some indices. In a separate shell I monitor the progress, alternately with `vmstat` and show full processlist. At first vmstat shows rapid progress; an example is # vmstat 5 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpdfree buffcache si sobi bo in cs us sy id wa st 1 6 0 8542108 256860 5394040000 0 152783 1314 298 3 4 60 32 0 1 6 0 8541000 256868 5394039200 0 147868 1301 287 3 4 68 25 0 1 5 0 8541380 256876 5394040000 0 150633 1310 277 3 4 72 21 0 1 6 0 8541108 256884 5394039200 0 152066 1307 271 3 4 71 21 0 1 7 0 8541116 256892 5394040000 0 151452 1312 311 3 4 64 29 0 1 6 0 8541992 256900 5394039200 0 192175 1402 295 3 4 66 26 0 1 6 0 8535684 256908 5394040000 0 108783 1227 276 3 4 69 24 0 1 8 0 8539116 256916 5394039200 0 155958 1318 262 3 4 82 11 0 1 6 0 8540860 256924 5394039200 0 166599 1340 328 3 4 66 27 0 1 9 0 8538512 256932 5394039200 0 165386 1336 319 3 4 62 31 0 1 6 0 8536776 256940 5394039200 0 175106 1358 303 3 5 66 27 0 2 0 0 8538884 256944 5394039600 0 187839 1402 305 3 5 70 22 0 1 1 0 8517060 256952 5394040000 0 188694 1379 307 3 4 66 27 0 1 10 0 8511604 256960 5394040000 0 175821 1335 294 2 5 69 24 0 1 10 0 8513340 256968 5394040000 0 164252 1335 300 3 4 65 28 0 2 0 0 8523012 256976 5394039200 0 151527 1318 305 3 5 60 33 0 1 10 0 8490152 256976 5394040000 0 178613 1352 301 2 5 67 26 0 2 0 0 8499576 256976 5394040000 0 142186 1319 302 2 5 69 23 0 1 10 0 8474280 256984 5394040000 0 185598 1348 301 2 5 57 36 0 2 9 0 8440676 256984 5394040000 0 166807 1334 306 2 5 53 39 0 1 9 0 8465228 256988 5394039600 0 114594 1268 306 1 6 56 36 0 1 9 0 16819736 256992 4554294400 0 185034 1342 301 1 6 56 36 0 1 9 0 20314428 257028 4213472400 0 186163 1371 282 1 6 56 37 0 1 9 0 20276856 257068 4217125200 0 166406 1342 281 2 5 59 34 0 1 9 0 20237672 257108 4220934400 0 166810 1333 252 2 5 56 37 0 At this point, and not for the first time, I stop vmstat and show full processlist. It says ++--+---+--+-+--+---+-+ | Id | User | Host | db | Command | Time | State | Info | ++--+---+--+-+--+---+-+ | 1 | root | localhost | cel_4x52 | Query | 542 | copy to tmp table | ALTER TABLE ntfelt ADD PRIMARY KEY (p, epoch, ssi, q, kind, ev, c), ADD UNIQUE INDEX pesqekvc(p, epoch, ssi, q, eqoch, kind, version, c), ADD INDEX tc(t, c), ORDER BY p, epoch, ssi, q, kind, ev, c | | 3 | root | localhost | NULL | Query |0 | NULL | show full processlist | ++--+---+--+-+--+---+-+ OK, so it is still indexing. Then I start up `vmstat` again, and it shows very different dynamics: # vmstat 5 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buffcache si sobibo in cs us sy id wa st 1 0 0 32429508 257248 3043925600 03076 0 0 100 0 0 1 1 0 32416124 257272 3045166800 0 2471 1020 111 6 0 94 0 0 1 0 0 32405096 257292 3046178000 0 2467 1017 109 6 0 94 0 0 1 0 0
