Giant database vs unlimited databases
Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. Thanks, Mohammad Ask a question on any topic and get answers from real people. Go to Yahoo! Answers and share what you know at http://ca.answers.yahoo.com
MySQL Performance Analysis tools
Hi. Using MySQL on Linux, I'd like to analyze the performance and know how resources (memory, threads) are used during a period of time. Do you know any tool to carry it out? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Performance Analysis tools
sar will give you some basic information about what happens on the system... (see e.g.: http://linux.die.net/man/1/sar)... -B |-Original Message- |From: thomas Armstrong [mailto:[EMAIL PROTECTED] |Sent: Monday, November 19, 2007 6:42 AM |To: mysql@lists.mysql.com |Subject: MySQL Performance Analysis tools | |Hi. | |Using MySQL on Linux, I'd like to analyze the performance and know how |resources (memory, threads) are used during a period of time. | |Do you know any tool to carry it out? Thank you very much. | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Giant database vs unlimited databases
[snip] The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. [/snip] Not true and it has been proven time and again by the likes of Yahoo and others that size. We routinely use MySQL for large data stores (upwards of half a billion records in a single table) and with proper management we have performance equal to or better than the above mentioned products without the overhead required by either of those. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
On 11/19/07, Mohammad wrk [EMAIL PROTECTED] wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. I think the statement that MySQL is not as powerful as the other products probably is unfounded in the sense that you mean it. On a given platform, searches are typically going to be O(N) or O(log N) depending on how you arrange the indexes and queries. http://en.wikipedia.org/wiki/Big_O_notation What you are trying to do is design your database so that all the queries are O(log N) rather than O(N). It is possible that Oracle can perform more adeptly than MySQL at certain operations (I don't know this, and the stats could easily go the other way). But the difference probably wouldn't exceed 2:1 in favor of either product, and you'll still be left with the O(N) vs. O(log N) design issue. I'd go with the design that does not create new tables. Dave.
Re: Giant database vs unlimited databases
How much power do you want? We migrated from Oracle to MySQL because to get enough power from Oracle 8/9i, we had to buy an extremely powerful machine. We had oracle on a sun solaris 9 box, and got X amount of power out of it. On a similar machine we installed MySQL and we got XX amount of power out of it. We choose MySQL because we can get more power out of it on a simple server than we did with Oracle. And MySQL was more flexible. Additionally, data management is easier for us on MySQL. We house dozens of organizations on MySQL servers. Each org either shares a db server, or they get their own db servers. In fact we are now installing MySQL on xen/linux systems and are able to reallocate memory and CPU to xen servers if they need more, rather than having to upgrade hardware in machines. When CPU amd memory start to top on a machine, we look at the queries and optimize them. That usually fixes anything that slows down the server. Our typical xen linux system is 8GB dual-ranked memory Intel 3GHz 1333FSB Quad-core CPU 4MB-Cache. You want to get memory that matches the CPU FSB, or as close to it as possible. We use the RHEL (Red Hat Enterprise Linux). I think many people find other non-intel systems perform better for Oracle - But that is because those systems are optimized at sale point. If the Intel system hardware is configured well, it will perform just as good with MySQL. And you can always migrate from MySQL to Oracle later if you really find that you need to. Tell your employer, if they want to pad the bottom line, they should use inexpensive MySQL and spend some of the savings optimizing the Web Application. Use Java grid technology, and load balance your read-only SQL queries (look at Free Sequoia - https://forge.continuent.org/projects/sequoia ) -RG Mohammad wrk wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. Thanks, Mohammad Ask a question on any topic and get answers from real people. Go to Yahoo! Answers and share what you know at http://ca.answers.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
Mohammad wrk wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. Funny, I thought you asked the question, should I separate my customers into their own databases, or use one big DB? Not MySQL sucks, Oracle is better. :) Issues I would ask about on this: 1. Is there a chance that given their separation, these DBs will ever diverge in design because of differences between customers? 2. Could they ever need to be separated for legal reasons? (like one bad query causing customer data be compromised) 3. Is there any other reason you may do something vastly different from one customer to another? If you answer yes to any of these, then you might be best off separating dbs. But, if you never want to, or expect for any of these things to happen, you will just be creating headaches for yourself. Backup, replication, and the need for cross DB queries, will all be a pain in comparison to a single DB. I am sure there is more to consider, but these are the points that come to my mind right away. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
Thanks everyone for the responses. Will put me on the right track here..something that was rolling through my head but I couldn't really define. I will be blogging about this later as I think it is fairly important, but often not understood by beginning/mid-level dbas. thank again, Keith William Newton wrote: Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Leopard
Steffan A. Cline wrote: Starting mysqld daemon with databases from /usr/local/mysql/var /usr/local/mysql/bin/mysqld_safe: line 426: 77090 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file $err_log 21 STOPPING server from pid file /usr/local/mysql/var/Phat-G5.local.pid 071117 12:21:39 mysqld ended Is this some kind of shell error? No, a segfault (see end of second line) indicates either a bug in MySQL or one of the libraries it uses (unlikely) or an incompatibility between them. This being an OS less than 1 month out of the gate, I'd bet on the latter. For now, try installing the version from Fink instead. It'll have to rebuild itself from source, which will avoid many of the possible incompatibility problems. http://fink.sf.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Leopard
on 11/19/07 12:34 PM, Warren Young at [EMAIL PROTECTED] wrote: Steffan A. Cline wrote: Starting mysqld daemon with databases from /usr/local/mysql/var /usr/local/mysql/bin/mysqld_safe: line 426: 77090 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file $err_log 21 STOPPING server from pid file /usr/local/mysql/var/Phat-G5.local.pid 071117 12:21:39 mysqld ended Is this some kind of shell error? No, a segfault (see end of second line) indicates either a bug in MySQL or one of the libraries it uses (unlikely) or an incompatibility between them. This being an OS less than 1 month out of the gate, I'd bet on the latter. For now, try installing the version from Fink instead. It'll have to rebuild itself from source, which will avoid many of the possible incompatibility problems. http://fink.sf.net/ I have always known fink to have this available but wanted to avoid it. I suppose I could just revert to using the init script but from what I have read it seems that the launchd is the better option. Yes, I saw the segfault but after looking into it, it seemed that it must be something I am missing. It would appear that for some reason those run time variables are not getting set. I built MySQL 5.045 from source and it will run fine, just not from launchd. Simply launching mysqld_safe works fine. Using the mysql.server start works too. So, other than using Fink, is there anything else that might seem obvious? I am willing to ride this one out and see what other options there are to try. Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : [EMAIL PROTECTED] GOOGLE: Steffan.Cline Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Leopard
Steffan A. Cline wrote: I built MySQL 5.045 from source and it will run fine, just not from launchd. My previous post was made with the assumption that you were using the official binaries, and that they had not yet qualified them on Leopard. I suggested Fink because it's an easy way to ensure you build from source, not because I think Fink is in some essential way better. The fact that you did build from source invalidates my whole line of reasoning. Was this machine upgraded to Leopard, or freshly installed? And if upgraded, did it have a previous version of MySQL on it before? If so, environment differences when running under launchd may be causing the linker to pick up old incompatible dynamic libraries. Try a 'make uninstall', then go back through /usr and /var by hand to ensure no traces remain, then reinstall. If MySQL's Makefiles don't support 'make uninstall', just do a by-hand removal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Leopard
on 11/19/07 2:58 PM, Warren Young at [EMAIL PROTECTED] wrote: Steffan A. Cline wrote: I built MySQL 5.045 from source and it will run fine, just not from launchd. My previous post was made with the assumption that you were using the official binaries, and that they had not yet qualified them on Leopard. I suggested Fink because it's an easy way to ensure you build from source, not because I think Fink is in some essential way better. The fact that you did build from source invalidates my whole line of reasoning. I tried this because I heard that there were issues with the installers not working. I did it from source so that I could build a Leopard friendly 64bit PPC version. Was this machine upgraded to Leopard, or freshly installed? Fresh install. And if upgraded, did it have a previous version of MySQL on it before? If so, environment differences when running under launchd may be causing the linker to pick up old incompatible dynamic libraries. There was no instance of it but as I mentioned earlier that the only trouble I had was that the mapping of the mysql vs _mysql user was different. Try a 'make uninstall', then go back through /usr and /var by hand to ensure no traces remain, then reinstall. If MySQL's Makefiles don't support 'make uninstall', just do a by-hand removal. I'll hit this one up tomorrow if I don't find a solution later on. It seems surprising that no one else has discussed this issue so far. Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : [EMAIL PROTECTED] GOOGLE: Steffan.Cline Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Optimization
I need to check a date_time value in a number of tables for up-to-date-ness. The following query is part of a larger PHP script that runs as a cron job every 10 minutes: query select case # If within 2 minutes, do nothing. when (unix_timestamp() - unix_timestamp(date_time)) 120 then 'ok' # If between 2 and 60 minutes old, send an email each time the script is called (q 10 min). when (unix_timestamp() - unix_timestamp(date_time)) = 120 (unix_timestamp() - unix_timestamp(date_time)) 3600 then 'email' # If over an hour old, send out one email per hour. when (unix_timestamp() - unix_timestamp(date_time)) = 3600 (unix_timestamp() - unix_timestamp(date_time)) % 3600 2999 (unix_timestamp() - unix_timestamp(date_time)) % 3600 3600 then 'email' end as 'test' from mytable order by date_time desc limit 1; /qyery This seems to run OK, but I'd be interested if anyone sees any way to improve it. Thanks, --David.
Re: ERROR 3 (HY000): Error writing file (Errcode: 5)
Daevid Vincent [EMAIL PROTECTED] wrote: mysql use mydbB; mysql CREATE TABLE foo ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL, PRIMARY KEY id (id) ); ERROR 3 (HY000): Error writing file './mydbB/foo.frm' (Errcode: 5) mysql use mydbA; mysql CREATE TABLE foo ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL, PRIMARY KEY id (id) ); Query OK, 0 rows affected (0.04 sec) [EMAIL PROTECTED]:/var/lib/mysql# ll total 28748 drwxrwxrwx2 mysqlmysql8192 Nov 16 22:46 mydbA drwxrwxrwx2 mysqlmysql4096 Nov 16 22:50 mydbB -rw-rw1 mysqlmysql 5242880 Nov 16 22:46 ib_logfile0 -rw-rw1 mysqlmysql 5242880 Nov 13 22:07 ib_logfile1 -rw-rw1 mysqlmysql18874368 Nov 16 22:46 ibdata1 drwx--2 mysqlmysql4096 Nov 13 22:07 mysql drwx--2 mysqlmysql4096 Nov 13 22:06 test Does there already exist a foo.frm file in mydbB? If so, are its permissions unusual and/or is there a hardware disk error? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
Hi Eric, In the case of a yes answer to the second question below, can't we still use something like VPD (Virtual Private Database) in MySQL? Thanks, Mohammad - Original Message From: Eric Frazier [EMAIL PROTECTED] To: Mohammad wrk [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, November 19, 2007 7:42:13 AM Subject: Re: Giant database vs unlimited databases Mohammad wrk wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. Funny, I thought you asked the question, should I separate my customers into their own databases, or use one big DB? Not MySQL sucks, Oracle is better. :) Issues I would ask about on this: 1. Is there a chance that given their separation, these DBs will ever diverge in design because of differences between customers? 2. Could they ever need to be separated for legal reasons? (like one bad query causing customer data be compromised) 3. Is there any other reason you may do something vastly different from one customer to another? If you answer yes to any of these, then you might be best off separating dbs. But, if you never want to, or expect for any of these things to happen, you will just be creating headaches for yourself. Backup, replication, and the need for cross DB queries, will all be a pain in comparison to a single DB. I am sure there is more to consider, but these are the points that come to my mind right away. Thanks, Eric Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail. Click on Options in Mail and switch to New Mail today or register for free at http://mail.yahoo.ca
Can't start mysqld
Greetings I just installed mysql and a few other packages but it gives me these erros: [EMAIL PROTECTED] zabbix-1.4.2]# service mysqld start mysqld: unrecognized service __ [EMAIL PROTECTED] zabbix-1.4.2]# mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) __ I am using Fedora 7x86 and mysql 5. Someone please help -- A church is a hospital for sinners, not a museum for saints. Abigail Van Buren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start mysqld
did u try this /etc/init.d/mysql start On 11/20/07, sizo nsibande [EMAIL PROTECTED] wrote: Greetings I just installed mysql and a few other packages but it gives me these erros: [EMAIL PROTECTED] zabbix-1.4.2]# service mysqld start mysqld: unrecognized service __ [EMAIL PROTECTED] zabbix-1.4.2]# mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) __ I am using Fedora 7x86 and mysql 5. Someone please help -- A church is a hospital for sinners, not a museum for saints. Abigail Van Buren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start mysqld
On 20/11/2007, sizo nsibande [EMAIL PROTECTED] wrote: Greetings I just installed mysql and a few other packages but it gives me these erros: [EMAIL PROTECTED] zabbix-1.4.2]# service mysqld start mysqld: unrecognized service __ [EMAIL PROTECTED] zabbix-1.4.2]# mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) __ I am using Fedora 7x86 and mysql 5. Someone please help -- A church is a hospital for sinners, not a museum for saints. Abigail Van Buren Sorry about that guys, just fixed the problem, seems as though I had not installed the server :) -- A church is a hospital for sinners, not a museum for saints. Abigail Van Buren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
