| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
Comments:
<0> or mysql_pconnect
<1> humm
<1> let me check this
<2> taec: best way is to setup replication and then do your mysqldump on the slave
<1> ;)
<2> this way you will also have a hot standby in case the masterserver decidecs to commit suicide
<2> otherwise you have mysqlhotcopy for use by myisam tables
<1> mysql_connect
<1> using mysql_connect
<2> mysqlhotcopy is a perlscript that will lock the tables and flush them (flush any writebuffers that is) and then in the filesystem copy the binary files of each table and then release the lock for each table
<3> Apachez, Excellent. Replication is something that we're planning in 1-2 months time, we just don't have the resources to put towards it at the moment... I want to evaluate all the options though. Are those two basically it?
<2> so if your tables are small and you have fast disks and not that much diskio wait time then the mysqlhotcopy should be significant faster than mysqldump
<0> set your wait-timeout to something like 10 seconds then L|NUX
<1> where ?
<1> in my.cnf ?
<2> but note that the files are being copied in the filesystem
<2> meaning that mysqldump is a more failsafe way to keep backups since the data will be in textformat in mysqldump while with mysqlhotcopy the tables are copied as files and are still in its binary format
<3> Apachez, yup, understood... thanks!
<3> much appreciated.
<2> but in your case i would go for a replication method
<2> since it solves your lock issues when the backup occurs AND you will have a hot standby (minimizing the downtime in case the master commits suicide :P)
<4> anyone know offhand of why I'd have problems with vpopmail on mysql 5, it works for the first few hours,but after that, attempted imap connections fail and give mysql "gone away" error, pop3 continues to work though
<5> hello
<1> problem is its running too much mysqld processes though
<1> root@sl1 [/home/aliurdu/public_html/home]# ps -ef | grep mysql | wc -l
<1> 62
<2> and third, the replication will be more accurate for backup than the 1 hour mysqldump method you use today
<5> when using SELECT ... WHERE id IN (1, 2, 3); is it guaranteed that the rows will be returned in order of the ids specified in the IN (...) part?
<2> meaning you wont in worst case loose 1 hour of data, you will loose like 1 millisek of data in worst case
<2> ronino: no
<5> Apachez: okay
<3> Apachez, replication won't suffice as a backup unfortunately, we'll need to replicate and backup then as well. But I take your point :)
<2> ronino: since WHERE col IN () is equal to WHERE col = x OR col = x2 OR col = x3...
<2> if you need to make sure which order they gets to the client you should use ORDER BY
<5> But i can use something like this: WHERE id IN (1, 2, 3) ORDER BY id IN (1, 2, 3); can't I?
<2> taec: i didnt mean to only use replication as backup
<2> of course you would still need to run mysqldump
<2> BUT
<2> imagine your master dies today 12:32 CET
<2> what happends then?
<2> with today method you will then have lost 32 minutes of data
<3> Yes, very true.
<2> with replication you just change the ip of the slave so it becomes the new master and you have not lost any data at all
<0> not sure L|NUX , you should play with your my.cnf settings
<2> (or you can do this automatic but i prefer manual methods so you know that the master has died and such thigns otherswise there is a risk of master dies, then slave dies then master dies again and you then have no idea which server has the most accurate data)
<3> Apachez, another option would be binary logging, no?
<2> binary logging will decrease performance
<3> Apachez, use the `log-bin' directive to give be a transaction log and I can go point-in-time recovery, without actually having a slave setup. Obviously there's a slight performance hit with every query, but really, the database isn't under that much load.
<2> and you would need to run through that log to make up 2 date contents of the tables
<2> which is not exactly a good method to do :P
<2> "Sorry dudes the db is down, we are reading back the binary log, ETA 4 weeks" :P
<3> I'm not sure I follow? point-in-time recovery can be done with mysqlbinlog?
<2> the binary log is for logging
<2> not for backup of the tables contents
<2> see binary log as a debug feature
<3> Apachez, excellent thanks, you've been really helpful!
<2> so in your case i would go to your boss and give him the resons for buying a secondary server
<2> pointing the main features of replication which brings no halts in production when the backups occurs
<2> and second that the slave will be a hot standby in case master dies
<2> and third that you will have up 2 date data on both servers compared to todays method where you will loose 1 hour in worst case
<2> and the slave doesnt have to be that monster as the master
<2> you will get a great server today for $1000 or less (rackmounted that is)
<6> hi guys, i am having some issues with a query on a tree like structure in mysql 4.1 Outcome, expected outcome and create statements are here http://pastebin.com/588688 I would really appreciate some ***istance as i've been trying for a day or so and i keep going round in circles
<3> MySQL is complaining about a missing .MYI file for a table... can that be removed through the client somehow (i.e. the software/users don't have access to the server)
<2> MYI contains index
<2> i dont think it can be completely removed through client
<2> unless mysql removes it if you drop all index including primary key
<2> but i doubt it since it will be created even if you dont have any primary key if im not mistaken
<0> that's correct
<7> row counts etc. are stored in the index header
<3> REPAIR table doesn't seem to be supported in MySQL 4.0 ... is there any way of repairing it, or is it lost to the gods?
<0> myisamchk from teh command line
<2> repair exists in 4.0
<2> no need to use myisamchk
<2> also note that when you use myisamchk you should shutdown the mysql process first
<2> or its recommended otherwise there might be further cirruption of the table if its in use
<8> I have a db with figures lik 4.33 etc, Now I like to divide 4.33/2. Can anyone tell me the way to do the query ?
<3> Aha, dodgy typing, d'oh.
<8> I tried SET `price`/2
<8> which obviously is wrong
<2> SELECT
<2> not SET
<8> and keep the /2 ???
<7> select (price/2) from tablename;
<8> You mean SELECT (price/2) from `price` ??
<9> anyone knows any good tutorial about IIS asp and mysql?
<8> well no luck on that suggestion
<6> ndice: http://www.google.co.uk/search?q=asp+mysql+IIS&start=0&ie=utf-8&oe=utf-8&client=firefox-a&rls=org.mozilla:en-US:official
<6> StormS: your table is called price ?
<6> usually its something like select (price/2) from products where product_code = 927423; or something like that
<6> obviously this depends on what you call tables and fields
<10> taec: make sure you still have the frm and MYD file, you can use repairt ... use_frm
<8> Nah fixed it
<6> ok i have two select queries , and the results of which i want to self join together by common id's. so (select .. from table 1) as t1 JOIN (select .. from table1) as t2
<6> but instead of getting t1.menu_id AND t2.menu_id
<8> SET `price` = `price`/2
<6> i just want menu_id
<6> and for it to just give me the menu_id's where they cross over
<11> StarScream, think you should read up on the different kind of joins.
<11> Some only join if there is a value to join if im not mistaken
<12> hi
<6> UltimateB|buried: http://pastebin.com/588688
<13> hi
<13> SELECT *
<13> FROM `kw`
<13> WHERE 1
<13> ORDER BY `bid` / `result` DESC
<13> LIMIT 0 , 40
<13> this req is very slow
<13> Query took 0.2489 sec
<13> ORDER BY `bid` DESC Query took 0.0006 sec
<10> you have index on bid ?
<13> i created `bid` and `result` as index
<13> yes
<10> but bid / result cannot use index
<6> heliostech: try doing an EXPLAIN on the query
<13> how does it works ?
<6> that should help you to see where its slowing down, but lokus is correct
<13> explain SELECT *
<13> FROM `kw`
<13> WHERE 1
<13> ORDER BY `bid` DESC
<13> LIMIT 0 , 40
<13> ?
<6> yeh
<13> 1 SIMPLE kw index NULL bid NULL 548
<6> its not using the indexes, like lokus said
<13> 1 SIMPLE kw ALL NULL NULL NULL NULL 548 Using filesort
<10> it read all the rows, sort them and return the 40 rows from desc
<10> the other one, uses the sorted index, read from the max to lower value, read 40 rows and done
<13> how do you know that
<10> knowledge and experience
<13> Using filesort what that ?
<10> sorting
<13> 1 SIMPLE kw ALL NULL NULL NULL NULL 548 Using filesort
<13> is
<13> EXPLAIN SELECT *
<13> FROM `kw`
<13> WHERE 1
<13> ORDER BY `bid` / `result` DESC
<13> LIMIT 0 , 40
<10> stop pasting so many lines
<13> i have to create an index
<10> no index can help this query
<13> chan is empty :-D
<13> alright]
<13> sorry
<13> i have to create an index for sorted data ?
<10> if you want fast, add a column to store the value of bid/result, index this column and it will be fast and use the index
<13> i have to fill the bid/result column during insertion ?
<13> in php for example ?
Return to
#mysql
or
Go to some related
logs:
#gentoo
#lisp
ext3 nolargeio
FC6 dv1000
public aways + stupid
marvell 88e8001 fedora
#lisp
#perl
belkin fd7050 ubuntu
#qemu
|
|