Config Mysql For Huge Data But Single User
I´m new to mysql and want to know how I set up the my.ini for best performance.
I´m running a PC with dual core and 3GB RAM, Windows XP, XAMPP
I want to configure mysql for best performance for a singele user acess.
I just want to do some sql-query on a huge database (25GB) - only one query at once.
At the moment my query nedds about two hours....
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Huge Single Quert Vs Forloop
i m writting a complex query which link up bout 7-8 tables. By now, i cant see any slowdown on the query coz the db stil fresh. But i knwo this query wil going to slow down my system when times go by. I also know i could optimize the table and i do try my best on it. So i wonder should i modify it into smaller, less complex query, which can use a forloop to loop the smaller query and get back the same result. Question: - forlooping the smaller query is the best solution to avoid a single-complex query? - I know the less-complex query wont bang my server in one shot(complex query). But will forlooping it giv me a worse result?
MySQL Config
I have installed MySQL on Windows98se twice under different paths, and then deleted the second installation. When i run MySQL as usual using the line "C:Program Files uspheremysqlinmysqld-opt.exe" , i get the following error msg: C:Program Files uspheremysqlinmysqld-opt.exe: Fatal error: Can't find messagefile 'C:Program Files usphere2mysqlshareenglisherrmsg.sys' notice that it runs from "nusphere" but tries to find the errmsg.sys file under the other folder "nuspehere2" which i have deleted. I tried looking for a config file so that i can force MySQL to look in the proper "nusphere" path, but i could not find any. I have also tried to look for an entry in the registry but came out with nothing aswell. Have I missed something? Any ideas on how to solve this?
MySQL Config
What config modification would give me a good speed increase for a site that has a lot of online users on forums (so a lot of DB change)
Mysql Config
i have a mysql php script on www.rizzler.se/sizeofmysql.php that checks the size of my sql, as you can se on the link above nothing is shown on the page thou. BUT as i upload the EXACT same script to another server that has the same settings as mine. same mysql database name and password etc http://lunarplejs.brinkster.net/sizeofmysql.php there it works but not on my server and i wonder why? my server say that mysql is running. i can connect to it with mysql query browser and so on, is this cause of my PHP.ini settings ?
Installation And Config Of MYSQL
Installed Mysql in Redhat 9.0 workstation. I have installed MYSQL,Appache,PHP. PHP,Appache installation is fine and intergration is also working fine. I have installed and configured MYSQL Problem : I cannot log into MYSQL database either from root or user.(local host) The error is 2002: unable to connect to local host thru socket "varlibmysoc" but cannot locate the mysql.sock in /tmp And the service is sqldb is also not working and cannot locate in services.
MySQL Config Error
I have been tasked to create a CMS for a website, however first I must install it locally to my laptop. I have chosen Joomla! as my CMS, and I understand that in order to run that I must first install Apache, MySQL, and PHP (in that order). Apache installed fine, as did MySQL (the most recent version), however, it will not let me configure it or run the config tool at the end of the installation, citing the message: "the side-by-side configuration is incorrect." Does anybody have any idea why this keeps happening and what I can do to resolve it?
MySQL Config Not Running
i'm trying to set up MySQL with IIS 7.0 on Windows Vista Home Premium. After you install MySQL, there's a check box to launch the config app. if i leave it checked, the app does not load. if i try to run the app by double-clicking its icon it gives me an error saying, "The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log for more detail." I cannot find the application event log, though.
MySQL Config File
I see that there are several MySQL configuration files on my machine: Quote: /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf How do I know which one my install is using?
MySql Username / Password And Config
Firstly USERNAME AND PASSWORD? You define them where? Edit them where? http://localhost will not work on my computer, how can I make MySql run from 127.0.0.1 ?
Single Row Restriction With No Data Manupilation...
I am running into a problem with an application that doesn't work too well with mutliple rows. Therefore, I need a script that will return a SINGLE row. This is what I'd like to do: I have a table (cash) that has 3 fields (date, id, balance). I would like to be able to retrieve the balance for the last 5 days for a unique id. Ideally the result would look like this: date1, balance1, date2, balance2, etc. I did get this to work with temporary tables, but then the script was run by the app, I got a "Can not issue data manipulation statements with executeQuery()" error. This obviously indicates the other constraint... using executeQuery, and thus no data manipulation.
Very Huge Database In MYSql
I am having very huge database (12GB) in mysql, with php i am using this data but i am not able to retrieve it. It’s giving time out every time because of its size. Is there any way to optimize this data?
Selecting Data From Single Table... Subqueries
I have a table with a list of books, their genres (type), and price. I have attached it as an image. I would like to select all books whose price is higher than all books of type HOR. Basically, I think that this means: find the book of type HOR that is the most expensive. Then, select all other books whose price is greater than that. In this example, the most expensive HOR book is 19.60. Therefore, I need to select all books with price > 19.60 and not of type HOR. I tried using a subquery like this: SELECT * FROM BOOK WHERE PRICE > (SELECT MAX(PRICE) FROM BOOK WHERE TYPE='HOR' GROUP BY TYPE) However, I get an error that says: Quote: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX( PRICE ) WHERE TYPE = 'HOR' GROUP BY TYPE ) LIMIT I looked up that error in the mysql doc but it didn't have much info in there: Quote: Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d
Huge Problem With Mysql Locking Up
I was hoping for some urgent help. One of my websites is having big problems with MySQL. Its a popular website and MySQL hasn't been able to cope. The site recently froze, here is a processlist from the time it started to freeze, its very messy: http://www.trailblasters.co.uk/sitepoint/main.htm The query that shows up the most has been removed and the site is running ok again but I sense it will freeze again at some point. UPDATES are run very regularly, could this be the problem? How can I overcome this issue?
MYSQL Causing A Huge Load
I just got my new VPS with liquidweb up and running and I'm experiencing a huge amount of load, a lot i think is due to mysql queries, so I think I'm going to need to do some serious optimization of my queries. I'm pretty new to this stuff.. Can anyone make sense of this SHOW STATUS and SHOW VARIABLES info, and let me know what the issue may be? SHOW STATUS Variable_nameValue Aborted_clients0 Aborted_connects0 Binlog_cache_disk_use0 Binlog_cache_use0 Bytes_received13775592 Bytes_sent180075883 Com_admin_commands541 Com_alter_db0 Com_alter_table0 Com_analyze0 Com_backup_table0...............
Does MySQL Support User-defined Data Types?
My group is in the process of migrating our Interbase databases to MySQL. We have numerous SQL scripts that use Interbase's CREATE DOMAIN statement (essentially creating user-defined data types) such as: CREATE DOMAIN PRIMARY_KEY AS INTEGER; CREATE DOMAIN FOREIGN_KEY AS INTEGER; CREATE DOMAIN USERNAME AS VARCHAR(20) DEFAULT USER; CREATE DOMAIN WHEN_ENTERED AS TIMESTAMP DEFAULT 'now'; CREATE DOMAIN WHEN_MODIFIED AS TIMESTAMP DEFAULT 'now'; CREATE DOMAIN NAME_TYPE AS VARCHAR(50); CREATE DOMAIN FULL_NAME_TYPE AS VARCHAR(101); CREATE DOMAIN DATE_TYPE AS CHAR(8); CREATE DOMAIN TIME_TYPE AS VARCHAR(20); CREATE DOMAIN COMMENT_TYPE AS VARCHAR(250); CREATE DOMAIN YES_NO AS CHAR(1) CHECK (VALUE IN ('Y', 'N')); CREATE DOMAIN TRUE_FALSE AS CHAR(1) CHECK (VALUE IN ('T', 'F')); I've searched the manual and couldn't find an MySQL equivalent, but it is a large book and I certainly could have missed it. I know we could just do a lot of replacing text and there are workarounds, but my boss wants to keep the original scripts as intact as possible and she asked me to look into this.
Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something... (I'm using PHP and MySQL) When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site. However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?
MySQL Database - Adding New Data Source 'Access Denied For User'
I am trying to install MySQL on my machine at the moment. I have downloaded the file mysql-essential-5.0.21-win32 from the MySQL website and installed it. I then downloaded MySQL Control Center, which came zipped in a folder called mysqlcc-0.9.2-win32. I have installed both applications and I have a MySQL database which I have acquired from elsewhere. I copy/pasted it into my C:Program FilesMySQLMySQL Server 5.0data directory, but when I try to add a new database in MySQL CC it doesnt allow me. I have also installed the ODBC for MySQL (called mysql-connector-odbc-3.51.12-win32). However, when I try to add a new data source in the Control Panel I get this error: [MySQL][ODBC 3.51 Driver]Access denied for user 'versiontwo'@'localhost' (using password: YES) versiontwo is the name of the database. the database has a password.
Performance Between Multiple INSERT Statements Vs Single Statement With Lots Of Data
$sql1 = "INSERT INTO mytable VALUES ("zzz","xxx")"; $result = mysql_query ($sql1); ... $sql1000 = "INSERT INTO mytable VALUES ("zzz1000","xxx1000")"; $result = mysql_query ($sql1000); vs $sql = "INSERT INTO mytable VALUES ("zzz","xxx"), ... ("zzz1000","xxx1000")"; $result = mysql_query ($sql); is there any performance difference between the 2? btw, there could 1000-5000 row inserts.
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
PhpMyAdmin Config.inc.php
I must have a wrong setting in the config file because when i view a database i get the Notice "Notice: Undefined index:... i just can't find where the error is. Everything else works great though.
What Is A Config File ?
The following message shows me an error at my Config Wizzard "The template configuration file cannot be found message "C:Archivos de ProgramaMySQL Server 5.0MySQLinmy-template.cnf" Any thoughts?
Server Instance Config
After installing MySQL 4.1.13a, Server Instance Config Wizard runs. At the end of the wizard it wants to execute, to finalize the configuration. Though once it gets to the point of executing the Service. It fails.
Problems Running With Instance Config Wizard
I can't seem to run the Instance Config Wizard for MySQL 5.0 on my computer. I am running windows Vista and each time I attempt to run the Wizard it responds with a dialog box: "The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log for more details." How do I resolve this issue?
Huge Pagefile
Am running mysql 5 on a win2k server. Installed with defaults, no changes. Have 8 databases running in the single instance. Have 2GB ram, 2 2.8GHz processors on the server and after about 2 weeks of running have a 1.8GB pagefile. Neither of the CPUs is running higher than 5% with peaks at about 30%. The database is supporting multiple websites with moderate traffic. The biggest table in each of the databases has only about 35,000 rows. Needless to say, the bigger the pagefile gets, the slower the respnse time. Any ideas on what settings I can make to keep the pagefile size down? Right now am rebooting the server once a week, but that's just a band-aid..
Huge Site
I wonder if MySQL should work with tables having milions of rows, e.g. you have image uploading site and you store daily around thousands of photos and regularly querying them. What are the techniques of this? Is it possible to do this with mysql? Do you know some books, articles about this? I thought about indexing or caching(of course if possible) and I want to learn something about this.
Huge Log Files
The database is completely backed up and nothing has been lost; the problem is the giant log files MySQL creates ate up our available disk space (all MySQL queries are logged, and with that worm hitting our forum we probably had about 8 gigabytes of logs). I deleted them all, but for some reason most services (e.g. MySQL, FTP, email) continue to report the disk as full, while I am free to copy files all over without problem using SSH. "du -h" executed in / indicates that we now have used 800 MB of our 10 GB disk usage quota. The most detailed error message I get is "/jail1: write failed, file system is full";
Huge Table
I have a table with over 500k customers in it. When my program loads the select statement takes so long the user never gets fully logged in. Is this the right forum to post a select statement for possible advice? It is a mySQL database.
Huge Table
I want to create a table to store tens of millions of records. Is my create table statement suitable for the job? CREATE TABLE `table_format` ( `id` int(11) default NULL, `name` varchar(100) default NULL, `addresses` char(200) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 AVG_ROW_LENGTH=200 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED is fixed row format making average row format parameter obsolete?
Data Dictionary User Tables?
Is there a way to get a good synoptic view of tables,cols,indexs, etc. by querying internal dictionary tables as you can in Oracle & SQL Server? Also is there a way to script an entire database out to DDL and insert scripts?
Get Data Using User Selected Date
I'm developing web site using php and mysql. In there user has to select a date, and data belongs to that date should print.My date field type is datetime. This is my code and it is not working. GDate-> user selected date PHP Code: SELECT Rainfall,Date FROM rainfall WHERE CAST(Date as DATE)='$GDate' AND SID='$GSID'
Huge Server Configuration
I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM and 1TB harddisk space. All the tables size are over 10GB and they have over 100mm records. Could some one help me get an appropriate mysql configuration(my.conf)file for the machine. I understand ther are lots of factors depends on it to get a steady working configuration. All I need is some model configuration. I think later on I can tune thar up.
Huge Database Problem
I have a database with 8.500.000 (aprox.) rows and I want to select a row like this: SELECT *,((DEGREES(ACOS((SIN(RADIANS(36.517)) * SIN(RADIANS(f4)) + COS(RADIANS(36.517)) * COS(RADIANS(f4)) * COS(RADIANS((-4.883)) - RADIANS(f5)))))*69.09)*1.6) as km FROM locdb order by km limit 0,1 (I've divided the lines for better reading). The syntax selects the nearest city acording to the latitude (36.517) and longitude (-4.883). The latiude and longitude colums on my table are f4 and f5, respectively. The problem is that (obviously) it takes about a minute to do it. Has anyone an idea how to improve the table or the syntax to get a better results?
SELECT On A Huge Table
I am using a 512MB server with a mysql 4.1.13. I have a table with 5 milion row ( table has a varchar 255 ). I need to download all the rows into memory ( from a C client ). After executing the SELECT query, I use mysql_store_result to take the data, but after one or two minutes, the server crashes and restarts and the clien connection is lost. I tried to use mysql_use_result, but strangely, the client doesn't load anything with that :/ ( it returns immediately ) In both cases I use mysql_fetch_rows to load de resultset. I have tried to put max_allowed_packets to 200MB, it doesn't change anything.. Does someone have and idea to load such table without crashing the server ?
Copy A Really Huge Table
I have a really huge table that I need to copy however using the Export function in MySqlAdmin does not work because it "times out". All I really need to do is generate an SQL script that will, drop the table if it exists, create the table and its structure, and then insert the data line by line. Usually when you perform a command in MySqlAdmin through Internet Explorer the MySQL command line is displayed with the results. However, this does not happen when you export. Anyone know how to do this from the MySQL command line? In a nutshell: I need to backup a table to an SQL script file.
Select With HUGE Criteria
what the best method is for doing a select where i have say 40,000 items of criteria. eg select * from clients where 1 or 2 or 3 or 4 ... or 40,000. I can buid the query up no problem using php, but the query would be huge. Is there a better way of doing this or should it be no problem for mysql.
Huge Server Load
This is the TOP stats from my vps: http://img28.imageshack.us/img28/3528/clipboard014rg.gif As you see server load is too much and its getting more then mysql service stops and everything is normal. This happens for 2 days, hosting company made a Linux kernel update maybe that can be the reason. My VPS load was always too high and there is only 1 site got 10.000 unique visitors. I did so many optimizations on the mysql queries and also php. I cant afford a dedicated server at this time and I dont think it's not so much traffic.
Deleted Root User From Mysql.user !!!
While trying to grant access to root user, i deleted all entries for root from mysql.user. I've tried to use root% (anonimous built-in administrator user) but to no good). Is there another admin user i could use to run mysql monitor and insert root records to mysql.user? I've read Could I use a copy of the mysql.user table? I'm using version 5.0.16-standard on Linux Fedora Core 3. Before ruinin the root user, i made a backup of a databasefrom the same server (not mysql database); i was wondering if that backup contains the mysql.user information somehow?
Using Non Root User To LOAD DATA INFILE
I am trying to set up a web app that will allow admin users to upload .csv files. those files are then processed and imported into the MySql db. I keep getting a 42000Access denied for user 'username' to database 'db' error. I have checked the user's permissions, and they have as much access as i can give them to the particular db.
Huge Databases And Nightly Backups
We have a huge database of several gigs. During our nightly rsync backup, performance is terribly degraded. Anyone have any hints on how to alleviate this?
One Huge Table As Appose To Many Small Ones
i have a very large XML DB (60gb+ and growing on a daily basis) which holds complete life cycles of stock option , i used to hold it all in folders with each folder named like the option paperId so it seemed logical to me to hold it now in diffrent tables for each paper even tough the are all the same exact table , now i'm having some problems with hibernate (one of the biggest reasons i left XML) and so many tables , is it more effiecent to keeeping data like that ? ofcours i will have to change the PK from datetime to paperId+DateTime if i decide to change to one big table ... how can i calculate diffrence in retrieving data ?
Altering Huge Innodb Table
I need to add an index to an Innodb table 110gb big. When I last tried it took forever and made the db unusable to visitors. My solution was to create another table with the same structure with the extra index and copy everything over with a php script. This is working so far but is taking a long time and still hitting the performance for visitors. Is my solution the best way or is there another?
Huge Database With 800 Customers / 3.500 Products
I need for a webshopproject all customers with all products in one file. I have already both informations in two different ones. Pls. find below the relevant info. product ProductID|pricegroup| customer knr|pricegroup|%calc|price It is possible that one product is related to 800 customers with 800 different prices. In my thinking the new DB should look like pricefile ProductID|knr|pricegroup|%calc|price.
|
TRACKER
