Re: Need advice on a good setup for generic queries
mo...@fastmail.fm (mos) writes: At 08:06 PM 7/12/2009, Morten wrote: If you can get rid of the DateTime and switch to just Date it may speed up the indexes. While not as pretty it's more compact to convert timestamp values into an bigint. For example: seconds since epoch. If you know the ranges to put in the query then store them this way and thus save on some storage, and therefore improve performance. May be worth considering? ... These queries which involve easily indexable fields (status_id, assignee_id, company_id) and multiple conditions on different ranges are what's difficult. The table is about 2.500.000 records and grows at a daily rate of about 50.000 records (that number is growing though). Once an action has been closed, it gets status closed and is no longer of interest. 70% of the records in the table will be status closed. As mentioned if you are not interested in closed queries get rid of them. put them in another table. That reduces the number of rows and hence the query time. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication, Stored Proceedures and Databases
g...@primeexalia.com (Gary Smith) writes: ... In database G we have 150+ stored procedures. 150k stored procedures? Sounds rather large. Do you really need this? What's the best approach to fix this problem? Is it as simple as adding the appropriate USE statement inside of the stored procedure right before the insert/update/delete/whatever? I'd suggest row based replication. In your previous post you mentioned you were using 5.1.35 so you can do that. One of the reasons for using RBR is precisely to make life clearer when replicating from one server to another. The rows changed on the master will be changed on the slave. You don't need to depend on the effect of the stored procedure on master and slave being the same. Simon -- 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 dynamics in MySQL Community Edition 5.1.34
mo...@fastmail.fm (mos) writes: At 12:37 AM 6/25/2009, you wrote: ... my.cnf based on my-huge.cnf, expanding key_buffer to 8G, myisam_sort_buffer_size to 256M, and putting tmpdir on the fiber channel disk. You mean key_buffer_size don't you and not key_buffer? If you are using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than 4gb. That's not entirely true. Later versions of 5.0 (above 5.0.56?) also allow key_buffer_size to be greater than 4GB and we are using that on several machines. Earlier versions of 5.0 did indeed have this problem. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
This is in 5.0.68 and 5.1.34. I'm trying to cleanup some old data in a table which looks like the following: CREATE TABLE `transaction_history` ( `customer_id` int(10) unsigned NOT NULL default '0', `transaction_id` int(10) unsigned NOT NULL default '0', `first_timestamp` datetime NOT NULL default '-00-00 00:00:00', `last_timestamp` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`transaction_id`,`first_timestamp`,`customer_id`), KEY `customer_id` (`customer_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql SELECT * FROM transaction_history LIMIT 10; +-++-+-+ | customer_id | transaction_id | first_timestamp | last_timestamp | +-++-+-+ | 10 | 31536827 | 2009-01-22 13:25:26 | 2009-01-22 13:40:21 | | 306636 | 31536827 | 2009-01-22 13:43:56 | 2009-01-22 13:44:02 | | 10 | 31536827 | 2009-01-22 13:50:24 | 2009-01-22 13:50:46 | | 306636 | 31536827 | 2009-01-22 13:50:53 | 2009-01-22 13:59:13 | | 304142 | 31536827 | 2009-01-22 14:53:00 | 2009-01-22 14:53:00 | | 306636 | 31536827 | 2009-01-22 15:03:59 | 2009-01-22 15:03:59 | | 10 | 31536827 | 2009-01-22 15:06:15 | 2009-01-22 15:09:01 | | 306636 | 31536827 | 2009-01-22 15:09:41 | 2009-01-22 15:10:32 | | 10 | 31536827 | 2009-01-22 15:10:42 | 2009-01-22 15:19:48 | | 306636 | 31536827 | 2009-01-22 15:30:41 | 2009-01-22 16:01:28 | +-++-+-+ 10 rows IN set (0.02 sec) I need to identify the rows to be deleted and was planning on doing something like: mysql EXPLAIN SELECT * FROM transaction_history WHERE (`transaction_id`,`first_timestamp`,`customer_id`) IN ( ( 31536827, '2009-01-22 13:25:26', 10 ), ( 31536827, '2009-01-22 13:43:56', 306636 ) ); ++-+---+--+---+--+-+--+--+-+ | id | SELECT_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+--+-+ | 1 | SIMPLE | transaction_history | ALL | NULL | NULL | NULL | NULL | 73181118 | Using WHERE | ++-+---+--+---+--+-+--+--+-+ 1 row IN set (0.00 sec) As you can see MySQL is ignoring or not recognising the primary key definition in the where clause and thus planning on doing a table scan. The simple approach is recognised correctly: mysql EXPLAIN SELECT * FROM transaction_history WHERE (`transaction_id`,`first_timestamp`,`customer_id`) = ( 31536827, '2009-01-22 13:25:26', 10 ) OR (`transaction_id`,`first_timestamp`,`customer_id`) = ( 31536827, '2009-01-22 13:43:56', 306636 ); ++-+---+---+--+-+-+--+--+-+ | id | SELECT_type | table | type | possible_keys| key | key_len | ref | rows | Extra | ++-+---+---+--+-+-+--+--+-+ | 1 | SIMPLE | transaction_history | range | PRIMARY,customer_id | PRIMARY | 16 | NULL |2 | Using WHERE | ++-+---+---+--+-+-+--+--+-+ 1 row IN set (0.02 sec) So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I propose valid and SHOULD the optimiser recognise this and be expected to just find the 2 rows by searching on the primary key? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I propose valid and SHOULD the optimiser recognise this and be expected to just find the 2 rows by searching on the primary key? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On Thu, May 14, 2009 at 04:45:44PM -0700, Scott Haneda wrote: It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. I have it set up and working in test. I will redo it again once I get a better handle on it. I am still a little confused on one aspect. In the mysql sample cfg file, the section that has: #Replication Slave there is a very clear OR to use either #1 OR #2. I did the suggestions of #2, issuing #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; on the slave. Sounds fine. I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = xx master-port = 3306 # End slave config No. not necessary as the information is stored in the master info file. Am I correct in that this is not needed. I know I for certain need server_id, but is that all I need, and I have redundant data? I figure also better to not have raw user and pass in a cnf file if it is not needed. The server-id IS needed and MUST be different on each server. ... log-bin = /usr/local/mysql/var/bin.log This can be in the datadir just fine. If you server is very busy with updates some people recommend putting this on a different filesystem to spread the I/O. Depending on your setup that may or may not help. If you don't need it now don't bother. log-slave-updates Only needed if you have a daisy-chained replication environment you need this. Without it the salve will only store the commands run on the slave itself thus missing the commands run on the original master. If you want to make a slave from the SLAVE server then without this option you won't pick up all the replication commands. auto_increment_increment = 10 Unless you are running master-master replication ignore this. replicate-do-db = somedbname1 replicate-do-db = somedbname2 required if you don't want to replicate all the dbs on the server. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On Fri, May 15, 2009 at 12:48:18AM -0700, Scott Haneda wrote: Also, how do I set the slave to be read only? I set read-only in my.cnf and it made all databases read only. SET GLOBAL read_only = true; and as you've done in the my.cnf file. Unless the user has SUPER rights he can't change things in the database. There are some minor exceptions: - you can create temporary tables - you can run ANALYZE TABLE These are normally not an issue. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 1. Ensure binlogging is enabled on the master. 2. Ensure you setup grant permissions so the slave can connect to the master. 3. Configure on the slave the replication (which databases need to be replicated) 4. Get the master and slave in sync (via rsync, load/dump or whatever) 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
replying only to the list... On Wed, May 13, 2009 at 10:19:21AM -0700, Scott Haneda wrote: 3. Configure on the slave the replication (which databases need to be replicated) This is where I need a little clarification, is the only thing I need to do is adjust my.cnf to have in the [mysqld] section server-id = 2 That's the minimal configuration. You may need to specify which databases need to be replicated or which tables. By default everything is replicated which is probably fine. 4. Get the master and slave in sync (via rsync, load/dump or whatever) Is this mandatory? There is not a lot of data, hundred rows or so, can I use LOAD DATA FROM MASTER; ? I think that only works in MySQL 4, and have never used it on our production servers (5.0). Yes, checking the MySQL 5. documentation it says: http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html --quote-- 12.6.2.2. LOAD DATA FROM MASTER Syntax LOAD DATA FROM MASTER This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL. --quote-- If you've only got hundreds of rows then just do a normal mysqldump. The problem is that if you have a database with GB or hundreds of GB of data then this process is really slow, and if at the same time you can't afford to stop your master then that makes life harder. Seems most instructions say to use a dump. This does not make a lot of sense to me, I am setting up replication, is it not the point to be able to pull the data down? Why does it need priming like this? For 5.0 and above because the you can't load DATA from master, so just use the dump, and don't change the master while you are doing this. 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master I can do this now, gives back a position. It seems to change over time. Since it is a moving target, if I am using LOAD DATA FROM MASTER; I take it I need to lock the tables while the first load is happening? If you're using 5.0 you shouldn't be using LOAD DATA FROM MASTER. Do a mysqldump and load from that, or if you use some sort of unix with snapshotting possibilities then make a (lvm) snapshot of the filesystem and copy that. That's what we typically do at work and it leaves the master down for just a second or so. The later copy can take place while the master is running. 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. This just tells the slave where to start replicating from. That is which statements or rows in the binlog to download from the master and apply on the slave. 7. Run: START SLAVE This starts the replication process. While not part of my plan, if the master goes down and I want to start using the slave as the master while I am fixing the master server What is the best way to do this? Can the slave be treated like a master by just pointing any client to the slave assuming I set a user to allow it? You can do this from the point of view of the database users but then the slave will be more up to date than the master and if you've not configured things properly and don't have the right information you won't be able to get the master back in sync. So you can't just switch between boxes without taking special care. With the slave temporarily becoming the master, the data will of course change. When I bring the master back online, what is the best way to reverse sync and get back to where I was? Probably take the entire thing thing down, copy the database from the current temp live slave that has been used as a master, and go from there? If the end that may be necessary. You can configure master / master replication but as I said you have to be careful with this as it can be quite critical how you actually setup your tables. If you don't do things correctly it won't work. I think it is documented however in the MySQL manual so I'd suggest you read that. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: splitting large tables vertically
kimky...@fhda.edu (Kyong Kim) writes: I was wondering about a scale out problem. Lets say you have a large table with 3 cols and 500+ million rows. Would there be much benefit in splitting the columns into different tables based on INT type primary keys across the tables? To answer your question properly requires more information: 1. Expected table structure. Can you show the current CREATE TABLE xxx\G output? 2. Expected use cases to extract data? 3. Do you expect to delete data frequently, or are you only inserting data, or is there a mix of inserts and deletes? If so provide more info. I've come across situations where a large table like this caused lots of problems. There were lots of concurrent delete batches (cleaning up) and at the same time lot of inserts. At the same time there were large groups of selects to collect certain sets of data for presentation. Perhaps you are doing something similar? If you do something similar you may find that it's extremely important to get the keys right especially the primary keys so that data retrieval (for SELECTs or DELETEs) is as fast as possible (using clustered indexes [PRIMARY KEY in innodb]). If not or if the queries overlap you may find performance degredation a big issue as Innobase manages the locks to ensure that the concurrent statements don't interfere. You can also use merge tables sitting on top of MyISAM per year or per whatever data in each table. That avoids you having to find data for 2009 as you look in table xxx_2009, so this can be a big win. MyISAM has the inconvenience that if the server ever crashes recovery of these tables can be very timeconsuming. Innodb has a larger footprint for the same data. So it's hard without more information on the structure and the use cases to answer your question. In fact if you have the time, try out and benchmark different approaches and see which is best for your requirements. Just remember that as the data grows the initial measurements may not be consistent with behaviour you see later. Also if you are looking at a large amount of data like this appropriate server tuning can influence performance significantly. Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Still going in cicrles
compu...@videotron.ca (michel) writes: I set up MySQL and when I try to start it it fails telling me that I need to run 'mysql_upgrade'. Show us the full error output and provide information on the version of MySQL you are using. When I run 'mysql_upgrade' it runs 'mysqlcheck' which is supposed to only be run when the server works ... No, mysql_upgrade does call mysqlcheck to see if things need adjusting. Look at the documentation http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html Look at http://dev.mysql.com/doc/refman/5.1/en/upgrade.html which gives information on upgrading MySQL. However be careful as IMO this documentation can be a bit confusing and is incomplete. Nevertheless it's a good starting point. If however you don't think you are upgrading then you need to provide more information on how you are installing MySQL so we can determine why MySQL thinks that it needs to do an upgrade. My guess would be that you have multiple mysql binaries on your server and are not running the version you expect. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Still going in cicrles
compu...@videotron.ca (michel) writes: I might have it fixed! I tried /home/qsys/mysql-5.1.32/libexec/mysqld and I would get /home/qsys/mysql-5.1.32/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090510 0:19:54 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090510 0:19:54 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist So you had built the binaries by didn't have an initial mysql database created? From reading around I tried running /home/qsys/mysql-5.1.32/bin/mysql_install_db /home/qsys/mysql-5.1.32/libexec/mysqld and now I get 090510 0:32:38 [Note] Event Scheduler: Loaded 0 events 090510 0:32:38 [Note] /home/qsys/mysql-5.1.32/libexec/mysqld: ready for connections. Version: '5.1.32' socket: '/home/qsys/mysql-5.1.32/mysql.sock' port: 3305 Source distribution This looks correct. I would suggest that the developers team might change the error messages. Indeed, I'll create a bug report for this. http://bugs.mysql.com/44765 Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: splitting large tables vertically
kimky...@fhda.edu (Kyong Kim) writes: I don't have all the details of the schema and workload. Just an interesting idea that was presented to me. I think the idea is to split a lengthy secondary key lookup into 2 primary key lookups and reduce the cost of clustering secondary key with primary key data by using a shorter INT type surrogate key. Another downside is the possible need of foreign keys and added complexity of insertions and multi-column updates. Have you found primary key lookups to be at least twice as fast as secondary key lookups with VARCHAR type primary key in InnoDB? The whole idea is based on the assumption that it is. That's why you really need to be more precise in the data structures you are planning on using. This can change the results significantly. So no, I don't have any specific answers to your questions as you don't provide any specific information in what you ask. Also, MyISAM conversion is an option too. Have you found the table maintenance to be a significant overhead? I've experienced MyISAM table corruptions in production and I'm more inclined to go with InnoDB for its reliability. This is a fairly important table. Well disk (and memory) usage can also be important so as it seems InnoDB storage is less efficient this may actually degrade performance. Until you are more concrete it's hard to say what will work best for you. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: innodb rollback 30x slower than commit normal?
nik...@doppelganger.com (Nikita Tovstoles) writes: We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED. We use Hibernate and optimistic concurrency, so periodically concurrent write attempts cause app-level Exceptions that trigger rollbacks (and then we retry tx). We've added app-level caching and turned down our tomcat NIO thread count to just 8 (very little contention inside the app) but now we're seeing that rollbacks appear to be up to 30x slower than commits?! Is that normal? Here's a typical TX: Set autocommit=0; Select * from users where name=bob; Update users set visit_count=X where id=bobId and version=Y Commit; Set autocommit=1; When this tx is executed about 100 times/sec, appserver latency is about 10-15 ms per http request (including db time). However, when instead of commit a 'rollback' is issued, the latency spikes to 600-1100 ms (nearly all of that time in appserver appears to be spent waiting on db). So is that expected cost of a rollback? InnoDB is heavily optimised and assumes that a transaction will commit successfully. As such it's not optimised to do the rollback, and as such a rollback *IS* very expensive. I've seen similar behaviour on some servers I use at work so what you are seeing is I think normal. Can anything be done to speed it up? I'm not aware of anything so I think you have to accept it and make sure that where possible you try to avoid situations where you need to rollback. That's not always possible of course but sometimes the scope of the transaction can be narrowed and that should help a bit. However in your example you could easily do a single atomic update involving the SELECT and UPDATE. That would be much easier as you would either run the combined UPDATE or not. Perhaps that would work for you? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
abhishek@gmail.com (Abhishek Pratap) writes: I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. You said in a later post that you have thousands of events. If they are already in a table then use that, otherwise put the events into a temporary table and join the 2 tables together. Something like this simple example: mysql select * from events; +-+ | event_ts| +-+ | 2009-05-09 10:29:00 | +-+ 1 row in set (0.00 sec) mysql select * from table_name; ++-+-+ | id | start_ts| end_ts | ++-+-+ | 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 | | 2 | 2009-05-10 10:00:00 | 2009-05-10 11:00:00 | ++-+-+ 2 rows in set (0.00 sec) mysql select t.* from table_name t, events WHERE event_ts = start_ts and event_ts = end_ts; ++-+-+ | id | start_ts| end_ts | ++-+-+ | 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 | ++-+-+ 1 row in set (0.00 sec) Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with MySQL prompt
prajapat...@gmail.com (Krishna Chandra Prajapati) writes: You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. It's a shame that the prompt can't be defined dynamically based on characteristics of the current connection. That would make it much easier to distinguish which db instance you are talking to and only have a single configuration. Is there not an entry in bugs.mysql.com for this? I had a quick look but couldn't find one. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQl and LVM
[EMAIL PROTECTED] (Shain Miley) writes: I am trying to plan we in advance our methods for backup and recovery of our new MySQL replication cluster. After doing some research it looks like a lot of people are using LVM snapshots as their backup solution. We currently have two MySQL servers with 2 300 GB (Raid 1). What I am confused about is the best disk layout to use at this point. Do I need to create a separate volume for the snapshots? Can anyone provide any suggestions on disk layout for two disks of this size? The filesystem layout is largely irrelevant. Basically what you want is to have all your mysql files on a separate LVM filesystem. So you could do this by creating a new filesystem and mounting it at /var/lib/mysql. Then install MySQL. Once you have the filesystem mounted you can use mysql as normal. To take snapshots do the following: 1. stop mysql 2. make a snapshot LV of the volume mounted at /var/lib/mysql 3. start mysql 4. mount the snapshot and back it up to a real filesystem or to tape or whatever. 5. unmount the snapshot and remove it. 4. Is very important as if you don't do this eventually the snapshot will run out of space and suddenly it will lose its contents. It's only a temporary staging area. Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtualizing MySQL
[EMAIL PROTECTED] (Shain Miley) writes: I am looking into the idea of setting up 10 - 15 virtualized instances of MySQL. The reason for this is as follows...we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. In order for our development team to do their work...they must have access to some Mysql resources that are close to the production environment. I am not currently in a position to provide each developer two MySQL servers (one master and one slave with 16 to 32 GB of RAM) for testing...or obvious reasons...mainly cost ;-) So I have been thinking about how best to provide such resources, at this point I am thinking that I can use OpenVZ to help me out a bit. I was wondering if anyone had any thoughts on this issue...should I just run 10 instances of MySQL on the same server...are there other options? I am concerned with trying to ensure that the metrics, resources, workloads, etc from these development servers has some sort of relevance to our production environment...otherwise we are testing apples and oranges...which the dev team will clearly point out...and in a way I know we are...but I would like to minimize the effects My only concern would be that if you have busy mysql instances that they will interfere with each other. We used to have a couple of busy mysqld processes running on the same Linux server only to find that the performance characteristics were worse than 1/2 of the performance of having each instance on a separate server. Both mysqld instances were busy and so fought each other for I/O and for CPU often at the same time. If this might be an issue for your virtual servers may not be an ideal solution as most of the free virtualisation options don't control sufficiently the hardware resources distributed to each virtual machine. YMMV. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Setup Question
[EMAIL PROTECTED] (Shain Miley) writes: Hello all, I was wondering if anyone had any good insight into running the 32 bit and 64 bit versions of MySQL? We are going to be using a replication setup within my organization very shortly. We intend to a have at least one master (writable) DB and several (let's say 3 for this excersise ) read-only DB's. One suggestion that I got was to use 64 bit version of MySQL so that we can make better use of our servers memory as we are using servers that have 16 - 32 GB of RAM. Yes, use the x86_64 bit version as you won't be limited in memory by the 32-bit architecture. MySQL seems to work pretty well with the 32-bit version but using it with more than 4GB of RAM is going to be a problem. The 64-bit version doesn't have any trouble with 32GB (not tried more). Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why different engines in one database?
[EMAIL PROTECTED] (hezjing) writes: When and why we create tables in different storage engines within a same database? Take for example a normal inventory application that provides CRUD operation to - USER table (e.g. create new user) - STOCK table (e.g. when there is new stock arrives) - CUSTOMER table (e.g. create new customer) - SALE table (e.g. when a stock is bough by a customer) I think it is always a best choice to use InnoDB since many applications are transactional. For transactional stuff, yes, InnoDB is probably best. How would one wants to create a USER table in MyISAM engine and SALE table in InnoDB engine? Can you give some example? MyISAM is [almost] the original table type that came with MySQL so it's still supported. It also has a smaller footprint on the filesystem than InnoDB. There are a few things you can do with MyISAM which can't be done with InnoDB (merge tables[1] comes to mind) and therefore it can sometimes be better to use a different storage engine. As long as you are aware of the advantages and limitations of the different engines you should be fine. Simon [1] If your sales table was huge it might make sense to have a sales table by month: sales_200810 sales_200809 sales_200808 ... (all the above tables HAVE to be MyISAM tables) and use a merge table sales_all being a combination of the above tables. Many people might suggest using a view for this but the implementation in MySQL of merge tables is more efficient than views which is why it's frequently used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master-master setup
[EMAIL PROTECTED] (Carl) writes: I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the edits and deletes would work (primary key is autoincrement): (Serial) (Serial) TransactionServer A Server B Add to server A1 Replicated 1 Add to server A2 Add to server B (before record 2 2 is replicated) Replicate to server B ? Replicate to server A? Does replication control the order in which transactions are applied so that somehow the replication from server A to server B is applied before the insert to server B? You need to set 2 variables to ensure you don't have problems. # when you have 2 master servers auto_increment_increment = 2 # each server has a different offset (values in this case 1,2) auto_increment_offset= 1 This way each master will generate unique ids Note: doing this means that you will get gaps in your ids as each server uses its own value to generate new ids and these increment by auto_increment_increment every time. Be aware that if the updates to the tables are very frequent it's quite possible that replication delay may mean that the data on both servers is not the same. The only way to ensure that this is avoided is to use explicit WHERE id IN (1,3,43,5,...,nn) clauses so that you are absolutely certain that the changes applied on one master will be produced on the other one. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
[EMAIL PROTECTED] (Mike Aubury) writes: I'm probably being a bit stupid - but I'm trying to determine (in code) the length of the string in the schema for a given table. So - for example : create table a ( blah char(20) ) I want to return '20', but I'm getting '60' when I use mysql_list_fields.. (Always seems to be 3x longer that I'm expecting)... Am I missing something ? (or should I just divide by 3!!) Is the table or database using UTF-8? I think that if it is MySQL will allocate space for each character and is forced to allocate 3x20 bytes as a UTF-8 character can be up to 3-bytes in length. SHOW CREATE TABLE a\G should show if this is the case. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Consulting
[EMAIL PROTECTED] (Database System) writes: There were no any error message on console or in error log. The symptoms are 1) the binary log file goes to /data/mysql/data/ dir, not as defined in /data/mysql/log/ 2) the binary log files name start with mysql-bin, not as I defined in my.cnf I created directories as following and changed the owner to mysql account /data/ /data/mysql/ /data/mysql/log/ /data/mysql/data/ Perhaps rather late for a reply to this thread but, ... datadir = /data/mysql/data log-bin = /data/mysql/log/binlog will do what you want. Don't change basedir. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Configuration and improvement advice.
[EMAIL PROTECTED] (Josh Miller) writes: I have recently become responsible for a LAMP site which has a decent MySQL install (v5.0.24a). The database is around 40GB with a single master to single slave replication scheme, although all activity goes to the master at this time, with the exception of backups which are taken from the slave. I have several tables which are fairly large, one has 120 million records, and I need to migrate these tables to InnoDB from MyISAM to reduce the number of table locks that occur on a daily basis which bring down the site's performance. What is the best way to perform this migration? Should I simply take an outage and alter table to set the engine type to InnoDB, or should I rename the table, and select into a new table? It depends on this table usage. If you can, then the ideal situation might be to create the new table with a temporary name and fill it in the background, and finally update for any changed values during the process. This process might be time-consuming for 120,000,000 rows but may work. If you use replication beware of the delays that may arise from doing this in anything but small enough chunks. What are the upper limits of MySQL performance in terms of data set size using MyISAM vs InnoDB? Be careful: the InnoDB footprint of this table may be much larger than your existing MyISAM footprint. I've seen issues with this especially as you'll be needing to adjust the the innodb_buffer_pool_size and key_buffer values during this process. Thus you _may_ suffer a performance problem, not because of the engine change but because of the increased memory requirements. Consider also the use of innodb_file_per_table which makes the resulting files easier to manage. A different solution might be to make a new slave, convert the table(s) on the slave to InnoDB, finally promoting it to be the new master. You'd also need to rebuild your existing slave. This avoids downtime to the site except for the master switchover period. It also gives you time to tweak all values while doing the conversion from MyISAM to InnoDB. Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke all on *.* ??
[EMAIL PROTECTED] (Pawel Eljasz) writes: there is a user with following grants: GRANT USAGE ON *.* TO 'ff'@'localhost' IDENTIFIED BY PASSWORD 'x' is it possible to: revoke all on *.* from $above_user OR revoke usage on *.* from $above_user ? DROP USER [EMAIL PROTECTED]; Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlshow shows list of blank (not empty) tables (FAQ?)
On Sun, 2 Sep 2001, Sinisa Milivojevic wrote: What does SHOW GRANTS presents for that user ?? [sjmudd@phoenix sjmudd]$ mysql --host=phoenix Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 3.23.36 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql show grants for sjmudd; +---+ | Grants for sjmudd@% | +---+ | GRANT ALL PRIVILEGES ON negatives.* TO 'sjmudd'@'%' WITH GRANT OPTION | +---+ 1 row in set (0.00 sec) mysql I think this looks ok? Also, there was a bug in 3.23.36. Can you try .41 ?? Ok. I'll try upgrading to this version and see if this removes the problem. thanks for the help. Regards, Simon -- Simon J Mudd, Madrid SPAIN. email: [EMAIL PROTECTED] Tel: +34-91-408 4878, Mobile: +34-605-085 219 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlshow shows list of blank (not empty) tables (FAQ?)
[EMAIL PROTECTED] (Sinisa Milivojevic) writes: Also, there was a bug in 3.23.36. Can you try .41 ?? I've just upgraded to 3.23.41-1 (rpm) and things now seem fine. [sjmudd@phoenix sjmudd]$ mysqlshow --host=phoenix ++ | Databases| ++ | bounces| | cv | | ipaddress | | mysql | | negatives | | new_ip_address | | ricdb | | slash | | test | ++ [sjmudd@phoenix sjmudd]$ mysqlshow --host=phoenix negatives Database: negatives +---+ | Tables | +---+ | negatives | +---+ [sjmudd@phoenix sjmudd]$ Looks like I was affected by the bug in .36. Thanks very much for your help with this. Regards, Simon -- Simon J Mudd, Madrid SPAIN Tel: +34-91-408 4878 email: [EMAIL PROTECTED] Mobile: +34-605-085 219 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlshow shows list of blank (not empty) tables (FAQ?)
On Sat, 1 Sep 2001, Sinisa Milivojevic wrote: Simon J Mudd writes: I've not been using mysql for some time but did have it running for a couple of small databases I use at home. It doesn't appear to work now, the most obvious sympton being that msyqlshow shows a list of blank table names. cut I'm sure that I'm doing something wrong which is in a FAQ but can't find the answer at the moment. Any suggestions are greatly appreciated. Run: SHOW DATABASES This gives me: [sjmudd@phoenix sjmudd]$ mysql --host=phoenix Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 to server version: 3.23.36 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql show databases; +--+ | Database | +--+ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | +--+ 15 rows in set (0.00 sec) mysql and see if you get any output. It could be a bug in mysqlshow. looks like mysql is at least being consistent: [sjmudd@phoenix sjmudd]$ mysqlshow --host=phoenix +---+ | Databases | +---+ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | +---+ [sjmudd@phoenix sjmudd]$ Also check that your server was not started with --safe-show-database. It is using the standard MySQL /etc/rc.d/init.d/mysql SysV startup script. The database information does appear to be available as knowing the database and table names allows me to access the information. Perhaps it is a permissions problem? -- snip [sjmudd@phoenix sjmudd]$ mysql --host=phoenix Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 to server version: 3.23.36 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql use negatives; Database changed mysql select count(*) from negatives; +--+ | count(*) | +--+ | 314 | +--+ 1 row in set (0.09 sec) mysql show tables; Empty set (0.01 sec) mysql - snip - Having not been subscribed to this list for some time, maybe I'm missing out on something really obvious. Regards, Simon -- Simon J Mudd, Madrid SPAIN. email: [EMAIL PROTECTED] Tel: +34-91-408 4878, Mobile: +34-605-085 219 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlshow shows list of blank (not empty) tables (FAQ?)
I've not been using mysql for some time but did have it running for a couple of small databases I use at home. It doesn't appear to work now, the most obvious sympton being that msyqlshow shows a list of blank table names. [root@phoenix mysql]# rpm -q redhat-release redhat-release-7.1-1 [root@phoenix mysql]# rpm -q MySQL; rpm -V MySQL MySQL-3.23.36-1 [root@phoenix mysql]# mysqlshow +---+ | Databases | +---+ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | +---+ [root@phoenix mysql]# The only thing I've noticed looking back at the mysql documentation is that there appear to be new MyISAM tables which are different to the ones I remember seing before (ISAM tables). My machine has /var/lib/mysql point to /home/mysql and the directory structure is the following: [root@phoenix mysql]# ls -l /home/mysql total 26 drwx--2 mysqlmysql1024 Jun 21 2000 ampr_database drwx--2 mysqlmysql1024 Feb 24 2000 bounces drwx--2 mysqlmysql1024 Oct 17 1999 cv drwx--2 mysqlmysql1024 Mar 22 1999 ipaddress drwx--2 mysqlmysql1024 Feb 9 1999 mysql srwxrwxrwx1 mysqlmysql 0 Aug 31 18:38 mysql.sock drwx--2 mysqlmysql1024 Nov 4 2000 negatives -rw-r--r--1 root root12264 Aug 31 17:46 negatives.tgz drwx--2 mysqlmysql1024 May 18 2000 new_ip_address -rw-r--r--1 mysqlmysql 347 Aug 31 18:50 phoenix.ea4els.ampr.org.err -rw-rw1 mysqlmysql 5 Aug 31 18:38 phoenix.ea4els.ampr.org.pid drwx--2 mysqlmysql1024 Jul 18 2000 ricdb drwx--2 mysqlmysql3072 Feb 19 2000 slash drwx--2 mysqlmysql1024 Apr 22 21:54 test [root@phoenix mysql]# ls -l /home/mysql/mysql total 67 -rw-rw1 mysqlmysql 0 Feb 9 1999 columns_priv.ISD -rw-rw1 mysqlmysql1024 Feb 9 1999 columns_priv.ISM -rw-rw1 mysqlmysql8778 Feb 9 1999 columns_priv.frm -rw-rw1 mysqlmysql1309 Aug 31 18:22 db.ISD -rw-rw1 mysqlmysql3072 Aug 31 18:22 db.ISM -rw-rw1 mysqlmysql8982 Feb 9 1999 db.frm -rw-rw1 mysqlmysql 0 Feb 9 1999 func.ISD -rw-rw1 mysqlmysql1024 Feb 9 1999 func.ISM -rw-rw1 mysqlmysql8641 Feb 9 1999 func.frm -rw-rw1 mysqlmysql 0 Feb 9 1999 host.ISD -rw-rw1 mysqlmysql1024 Feb 9 1999 host.ISM -rw-rw1 mysqlmysql8958 Feb 9 1999 host.frm -rw-rw1 mysqlmysql 0 Feb 9 1999 tables_priv.ISD -rw-rw1 mysqlmysql1024 Feb 9 1999 tables_priv.ISM -rw-rw1 mysqlmysql8877 Feb 9 1999 tables_priv.frm The negatives.tgz file is copied from the negatives directory. When copied over to a FreeBSD machine running the mysql version below the data shows up correctly. [ mysql Ver 10.12 Distrib 3.23.26-beta, for -freebsd4.2 (i386) ] The mysql error log file shows no errors. I'm sure that I'm doing something wrong which is in a FAQ but can't find the answer at the moment. Any suggestions are greatly appreciated. Thanks in advance. Simon -- Simon J Mudd, Madrid SPAIN. email: [EMAIL PROTECTED] Tel: +34-91-408 4878, Mobile: +34-605-085 219 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
