Small Database, High Load
I have a very heavy mysql database used website. Data is constantly being updated and at peak there is well over 1,000 people online, after it hits the 1,000 mark, the website starts to lag pretty bad.
My database is only small at around 90mb. We have a web server for the files, and a database server for the database. The load on the web server is fine. But the database server is pretty high.
Would replication reduce lag and load? if i was too add a second database server?
View Complete Forum Thread with Replies
Related Forum Messages:
Very High CPU Load 200+, CPU Usage 400 To 700%+
--- Top --- top - 12:18:47 up 2 days, 16:28, 1 user, load average: 45.78, 144.51, 156.97 Tasks: 171 total, 1 running, 170 sleeping, 0 stopped, 0 zombie Cpu(s): 38.3%us, 59.7%sy, 0.0%ni, 1.8%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Mem: 12303004k total, 11507936k used, 795068k free, 284492k buffers Swap: 2096472k total, 168k used, 2096304k free, 2000724k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 6161 mysql 15 0 930m 221m 3820 S 785.1 1.8 8:37.71 mysqld 1 root 15 0 10344 640 544 S 0.0 0.0 0:02.71 init 2 root RT -5 0 0 0 S 0.0 0.0 0:00.09 migration/0 3 root 34 19 0 0 0 S 0.0 0.0 0:00.02 ksoftirqd/0 --- End Top --- Below is the my.cnf I have been using: [mysqld] log-slow-queries = /var/log/mysql-slow.log old_passwords=1 max_connections=1000 max_user_connections=500 thread_cache_size=1024 key_buffer_size=192M join_buffer_size=1M myisam_sort_buffer_size=10M sort_buffer_size=2M max_allowed_packet=16M read_rnd_buffer_size=1M long_query_time = 5 skip-locking innodb_file_per_table ft_min_word_len=3 query_cache_limit=10M query_cache_size=10M query_cache_type=1 interactive_timeout=20 wait_timeout=20 connect_timeout=10 table_cache=2048 read_buffer_size=2M max_connect_errors=100000 thread_concurrency=8 low_priority_updates=1 concurrent_insert=2.........
View Replies !
High Average Load (50%)
I have a Windows 2003 server with MySQL 5 installed (5.0.19). The server has a 3ghz processor and 2gb of memory. The server has about 20 databases of the type MyISAM and there is almost no load trough queries. The mysqld-nt.exe process has an average load of 50% and goes up when a query comes in. Why is this? I have tried logging to see what MySQL is doing but I cannot find anything abnormal.
View Replies !
One Big Database Or A Lot Of Small Ones?
My boss is implementing a system where a lot of different sites will be using the same exact database structure. He has it in his head that things would be simpler with one database shared by hundreds, possibly thousands of sites. I feel like each site deserves it's own database, even if it involves more maintainence.
View Replies !
Backup A Small Database.
I'm looking to backup a small mysql database. The problem is mysqldump does not exist on the server I'm trying to export the database from. Does a php script exist which I could use to do essentially the same thing as mysqldump?
View Replies !
A Huge Database Or A Lot Of Small Databases
I have a question regarding database performance. Which of these two designs would be better: -Having one huge database with 50 tables or so (each table having millions of rows) or -Having a lot of databases with the same amount of tables, but each table would have only thousands of rows *The mysqlserver is running on a P4 3.0 ghz with 1 gb in ram, but I may upgrade my server soon. *Privileges to create databases is not a problem since I own the server
View Replies !
Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio? I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like INSERT INTO sometable VALUES (1,'image.jpg'); The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP. Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.
View Replies !
Small SQL & Php Webpage Field Database Task
In a webpage, imagine a little section where the user fills in his/her contact details to register. Among this section is a field called "Country". The task is to make this field automatically generate the persons current country of residence. I am told, that the IP ranges and the country to which they belong is in a CSV .xls file. I am also told that the best way to do this is to: 1) Insert the CSV into database table (MySQL). 2) Use php to get that country name onto the website field.
View Replies !
How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my learning period. Now, I want to reset my Yahoo Small Business MySQL Database to default settings or to reinstall it (I want it to be the same as it was newly activated). Note: I tried to repair my database with Yahoo's "Database Setup" page and the message "Database scheduled for repair" appeared for a few days but nothing happened. And, I can not login to my database using "phpMyAdmin" utility. If you can help me to reset my "Yahoo Small Business - MySQL Database" I will be very pleased.
View Replies !
Load Database
I have created a database and a table. I can access the database from phpMyAdmin. I am able to run a query and it works out fine. Now, I want to actually use the database in a site. I am pretty sure it needs to be a .php site, and I have searched all over google, but no luck finding any answers. How do I get a database to load in a site so that I can acutally use it?
View Replies !
Can't Load Database
I'm just starting out with MYSQL and know next to nothing about it. I'm utilizing a book called "Learning SQL" published by OReilly. I've created a user and I've created a database per the book's instructions. I'm having troube loading the sample data. It's saved in my C: drive. I type the command: as in source c:LearningSQLExample.sql and receive the error message 'Unknown command L. I don't understand what I'm doing wrong. Maybe it's the book.
View Replies !
Load .sql File Into New MySql Database
I am not sure how to proceed with this problem. I have just installed MySql onto my machine and that seemed to go ok. I have created a database with a username and password also. I have a file from a friend (for real!). It is a .sql dump file?, which looks like it has several commands in it such as: CREATE TABLE `games`, INSERT INTO `games` VALUES("values"), CREATE TABLE `results`, and INSERT INTO `results` VALUES ("more values"). How do I load this file into my empty database?
View Replies !
Handling Large Database, Spreading The Load
For speed and performance issues, I am thinking about using two servers to handle a single mySQL database. Is this possible? If so, how would I go about doing it? Lets say hypothetically that one of my tables has millions of rows, what would I do to be able to spread the load on my two servers?
View Replies !
Handler_read_rnd_next = 13k On SMALL Db
I have two very small DBs. One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this. Using phpmyadmin, I check the page with runtime info and I see Handler_read_rnd_next 13 k If I reload this page a couple times, it goes up to 14k. Most of the queries I use look like Code: SELECT c.color, b.percent FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c WHERE a.creature_id =1 AND a.creature_id = b.creature_id AND b.color_id = c.color_id And the EXPLAIN SQL for these comes up with Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1 To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not.
View Replies !
Small Databases
I am wondering if having a database for the client back office, a database for the static website and a database for website stats would be a good idea. The client database would hold credit card info, user info, reg info, project info, client messages and more. The website database would carry all the content for the pages of the public website like about us, contact, home page, news and such. The website stats database would keep track of user agents, new hits, returning hits, keyword searches made on the client websites and all that jazz. Should I make this all ONE giant database or three smaller database?
View Replies !
One Big Table Or Many Small Ones?
I am designing an interactive website consisting of a few pages and I want to track individual user progress. I was wondering if it would be better to create one big table for all values on all pages (Which may have a lot of columns, easily hundred or more), or created a separate table for each page (so I could have some 10 tables with 10 columns or more). Or does it not matter?
View Replies !
Big Select Vs Small Ones
I have an array of 10 IDs. I need to query the db for values associated with these 10 IDs. In a lot of places, I have seen people using a php foreach like this foreach (ids in array) { query = SELECT values FROM tables WHERE id = [id 1 in array] do query store values } we could build one big select statement like this: query = SELECT values FROM tables WHERE id = [id 1 in array] OR [id 2 in array] OR [id 3 in array].... until we have ids.
View Replies !
Under High Traffic
can MySQL handle very high traffic and requests? If I start out with MySQL, and it later struggles under very high traffic, it'll be a huge pain to fix. I'll have to recode everything to a new database environment and transfer all of the data to another database -- all in a panic because of downtime. I question this because I watched a video where the creators of YouTube discussed database management -- from what I remember, they said they developed the entire website under MySQL, but found soon out it couldn't handle their very high traffic.
View Replies !
High Availability In Mysql
Does anyone know what sort of HA features will be implemented in MySql ? What I exactly want to know is whether MySql will provide a HA solution similar to Oracle Real Application Clusters (RAC). RAC allows multiple instances to manipulate a single database on shared disks. If one instance fails, the others still can continue to provide the same service. (This is quite different from MySql Replication which maintains multiple copies of a single database.) It may be too much to ask for free software, but I really wish that this kind of features will be implemented in MySql.
View Replies !
Small Query Optimization
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
ORDER BY HIGH/LOW Count
here is my query, SELECT CT_UID, COUNT(*) - COUNT(DISTINCT CT_UID) FROM clocktime WHERE CT_TimeOut BETWEEN $WeekBegin AND $time GROUP BY CT_UID; All this query does is query a "detention" table and grabs a count of how many times a student has been sent to detention, see our school clocks students into a computer when they have "detention" and when they leave. The query now displays the data by CT_UID (the students id), how do i order the data by highest or lowest count?
View Replies !
Select_full_join Number Is High
The variable "Select_full_join" is showing a number of 537. This seems very high, especially when the recommendation is to have this at zero. I can get a full list of all queries generated on the site but how do I find out which ones aren't using an index in the join and how do I fix that?
View Replies !
Small Favor Gone Wrong
I have a VPS account and I am trying to move my sister's professional organizations site onto my server more or less as a favor (getting paid a little - but not really worth my time). It is being hosted by a professional hosting company that supplied me with ftp access to download the site for the move. I am running into a problem because I don't know how to set-up/use mySQL through UNIX. Previously I have used the Plesk interface provided with my VPS account. Plesk (or at least my hosting services configuration of it) won't allow me to create the user I need in order to import the database because the user ID is contained within the user PW. (this is the database coming from the vendor). The original host has been unhelpful at best so I thought before I go to them and ask for assistance (that I am pretty sure I won't get anyway) that I would post here to find out if there is any easy way through this roadblock or if any the unix commands are simple enough, how I would proceed that way.
View Replies !
Mysql Usage Is Very High
My mysql usage goes up to 40% of CPU. I think one of my mysql database is causing the issue. I store images in that tables. The select query takes nearly 0.4S to 1.5Sec to fetch a single row. mysql> desc images; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | bigint(20) | | PRI | NULL | auto_increment | | slideid | int(11) | YES | | NULL | | | filename | varchar(50) | YES | | NULL | | | fileinfo | varchar(60) | YES | | NULL | | | position | int(11) | YES | | NULL | | | image | mediumblob | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ mysql> select count(*) from images; +----------+ | count(*) | +----------+ | 5022 | +----------+ e.g query results: 1 row in set (0.31 sec) 1 row in set (1.44 sec)
View Replies !
High CPU Usage (99.9%) On RHEL4
i need your help on this issue. I am running a web server in my orginazation internally with mysql-4.1.12 and RHEL4. Now, its been only one day and using 'top' command its showing mysqld process using cpu 99.9% constantly. This is used by say around 4000 users. My my.cnf file : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid output of mysqladmin version .......... ... Threads: 53 Questions: 324297 Slow queries: 588 Opens: 39976 Flush tables: 1 Open tables: 64 Queries per second avg: 67.788 output of mysqladmin extended-status Variable_name | Value | +--------------------------------+------------+ | Aborted_clients | 742 | | Aborted_connects | 35 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 29401860 | | Bytes_sent | 1049908921 | | Com_admin_commands | 2352 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 1 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 40895 | | Com_change_master ...................................................................
View Replies !
High Volume Of Inserts
I'm working on a pretty simple log analyzing stat app, and I've come to a point where I will need to make a large volume (2000+) of inserts into a mysql database. Is there a way I can do this without crashing my server?
View Replies !
Group Votes Into Low, Medium And High
I have a table with votes: --------- | vote | | 5 | | 1 | | 9 | | 6 | | 2 | | 1 | | 3 | --------- And I would like to see number of votes between 1-3, 4-6, 7-10 This select doesnt work: SELECT count(vote<=3), count(vote>=4 AND vote<=6), count(vote>=7) FROM user_bfcore_vote;
View Replies !
High CPU Usage :: Setting Character Set
The following prg keeps cpu usage as 79% always .When i kill/stop the prg,then cpu comes down. main() { //code for mysql conn while(1) mysql_real_query (&mysqlcon, "SET CHARACTER SET 'utf8';", 25); } What might be the reason? Wil it be any config issues?
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
MySQL Upload Small Files
I'm uploading small files (>10K) and mysql is deleting it. The one field is a blob field and everytime i test upload a file thru phpMyAdmin within 2 seconds mysql deletes the row. Theres nothing in mysqld.log, there another log i should be checking out?
View Replies !
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
View Replies !
Structure Of Small Social Network
I need to setup a social network type mysql database, but not as intense as friendster, thefacebook, myspace, etc. i have a structure set up but it think it's weak and extremely limited: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | +---------+-----------+ i fear it's going to be a lot of redundant IDs in a column, which i think could be a problem when retreiving friends: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | | 1 | 9 | | 7 | 9 | +---------+-----------+ i want to do a "bob is friends with john" and a "jackie knows sally through richard" (aka friend of a friend). that's it and nothing more. how do i structure mySQL database to be able to this?
View Replies !
Performance :: Mysqld-nt.exe Is Quite High Number
i am having a problem at the moment with my pc. i keep getting performance issues when running mysql queries and when i go in to task manager mysqld-nt.exe is always quoted as a very high number on cpu. is there something i may have done incorrectly to cause this. i have been using php/mysql for a good while now and have never came across this problem before? it is the same regardless of which queries i am running (including those which always worked fine before).
View Replies !
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 ?
View Replies !
Optimization - High PHPMyAdmin Stats
I've noticed that after running a couple of low traffic sites (couple of hundred visitors a month), some of my PHPMyAdmin stats are very high. Slow_queries: 50 <- this is probably ok as the default is about 1 second per query I think Handler_read_rnd: 56 M Handler_read_rnd_next: 2,600 M <- 2.6 billion?? Qcache_low_mem_prunes: 5,862 k Create_tmp_disk_tables: 1,580 k select_full_join: 30k select_range_check: 214 sort_merge_passes: 26 k opened_tables: 1,743 k <- I only have about 20-30 tables table_locks_waited: 5,385 The type of queries I do is nothing out of the ordinary, just SELECT * FROM tbl ORDER By date LIMIT 1,5 The numbers in the limit are defined by page offsets. Testing the MySQL admin program on my offline testing machine, hitting refresh keeps bumping the Handler_read_* values up every time even though there are no SQL requests. Some of the warnings seem to be for table sorts but I need to sort the tables so there's not really anything I can do about that.
View Replies !
High CPU Usage On Win 2003 Server
I use a VPS server for 1 month. Windows2003, Mysql, PHP and ASP. It was very well to yesterday. But in this morning, i saw my server and CPU usage is %100. I restart server. I restart service. But it is same. I think; it can be "bad" scripting. I closed every web site on my server. And I opened a site with Mysql database. After 1-2 seconds, CPU usage is %100. It is a Vbulletin site (PHP+MYSQL). I said "This site has got a problem". And I closed that. And I opened different site (ASP+MYSQL). And after 2-3 seconds, CPU usage is %100. After different site. And same result. While i restore a database, CPU is %90-100. While i use Query Browser, CPU is %90-100. If i don't use MYSQL, CPU is OK. I opened site without MYSQL, CPU is %5-10. But if I run a site with MYSQL; CPU is %100. And these sites run in yesterday very good. I didn't change anything. I update MYSQL, but result is same. My system: Windows Server 2003 Enterprise Edition SP2 INTEL XEON 2.66 E5430 512 MB RAM I tried my-huge.ini, my-large.ini, my-small.ini, my-medium.ini. Mysql Version: 5.0.27 Mysql Client Version: 5.1.11 This server has got; IIS 6.0 with PHP-CGI (I use ASP and PHP sites) FileZilla Hmail Mailbee Enternix Control Panel Logparser Winrar My site count with MYSQL: 3 1 - A Vbulletin board (3.7.1) (1 GB MySQL Data) (Daily 5.000 uniqe visitors) 2 - A Joomla site (1.15) (30 MB MySQL Data) (Daily 300 uniqe visitors) 3 - An ASP news script site (MYSQL) (150 MB MySQL Data) (Daily 4.000 uniqe visitors) And if i run any site of that three; MySQL is go top %100...
View Replies !
High CPU Use (spikes) And Page Faults In VM
Our current my.ini has these settings default_storage_engine=INNODB sql-mode="STRICT_TRANS_TABLES.... max_connections=800 table_cache=2400 (tried 1200) tmp_table_size=16M thread_cache_size=100 (tried up to 1000) myisam_max_sort_file_size=100k ( this was 100G in ini, but 100k in Admin?) myisam_max_extra_sort_file_size=100k ( this was 100G in ini, but 100k in Admin?) myisam_sort_buffer_size=10M key_buffer_size=256M (total of our isam indexes = about 40M) read_rnd_buffer_size=256k sort_buffer-size=256k innodb_additional_memory_pool_size=20M innodb_flush_log_at_trx_commit=0 innodb_log_buffer_size=1M innodb_buffer_pool_size=500M ( tried 750M, 1000M ) (total of our inno indexes = 750M) innodb_log_file_size=16M connect_timeout=30 ( tried 20 ) wait_timeout=5 (tried 10, 30 ) skip-name-resolve skip-host-cache query_cache_type=2 query_cache_size=8k record_buffer=256k
View Replies !
Several Small Calls Vs One Large Call
I am working on a php interface to a mySQL db which will pull lots of rows from the database. Assuming I am selecting a hundred rows by their auto_incremented ID's, is it better to do this with a loop of 100 "SELECT ... WHERE id=" calls, or to do it with one single "SELECT ... WHERE id IN(...)" call, with IN() holding a list of the hundred items.
View Replies !
Detecting High CPU Usage Queries
I know I can log slow queries, but what about ones that require a huge amount of CPU power to process? I have a lot of those lately it seems and I am trying to track down the culprits.
View Replies !
Mysqld Process Running High CPU
My Mysql server is running at around 60% CPU even when there's virtually no queries going through - I turned on logging and have been watching the queries and looked at mysqladmin processlist but there is nothing unusual - this just started happening. I tried using myisamchk which didn't find any problems with my tables.
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
MySQL Values (for Variables) That Seem To Be To High
phpMyAdmin shows my some values for variables in the status are marked red. I assume, that these values might be to high. Section Handler: Handler_read_rnd 414 k Handler_read_rnd_next 316 M Temporary data Created_tmp_disk_tables 3 k Key cache Key_reads 78 k Joins Select_full_join 3 k Tables Opened_tables 6 k Table_locks_waited 5 Now I've got a lot of questions: 1) For Handler_read_rnd it says "You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly." How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this? 2) For Handler_read_rnd_next it says "Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have." Same questions as in 1): How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this? 3) For Created_tmp_disk_tables it says "If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based." The tmp_table_size value it 33,554,432 right now. How would I increase this? 4) Key_reads: "The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests." My key_buffer_size is 16,777,216, Key_read_requests is 3M. So the cache miss should be 78 k / 3072k??? What am I supposed to do here? 5) Select_full_join: "The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables." Is there an easy way to check the indexes of all tables? 6) Opened_tables: "The number of tables that have been opened. If opened tables is big, your table cache value is probably too small." table cache is 64. What size should it be? How would I increase this? 7) Table_locks_waited: "The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication." Is there a way to find out which queries / tables should be optimized?
View Replies !
Help With A Small Query Optimization Using An Index. THANKS!
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed. The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
Using Views In A Fairly High Demant Environment
I am making a forum type system in which the only interface to the database is through stored procedures. Thats not the point though. I have run into a minor problem with my system that could be rectified if my request for the threads in a forum was drawn from a "view" table. Would a view be quite a burden on the systems performance if used in a situation like that?
View Replies !
Large .sql File (break Down To Small Parts)
i have an oscommerce installation that generates a 200mb sql backup file. i want to try and rebuild the installation on my local pc (localhost) to emulate and play with, unfortunately the 200mb file is difficult to handle. phpymadmin seems to crash. i have console access on the host however. is there a way i can break down the sql file into smaller increments for easier uploading?
View Replies !
Memory Usage Remains High After Large Delete
I just did a large delete on an InnoDB table - deleted 27 million rows and took 80 minutes. Memory usage remained at 34.3% during the delete (which is consistent with innodb_buffer_pool_size setting), but memory usage on the mysqld process hasn't dropped even after the delete has completed (it's been over an hour). Any ideas why the memory usage would not drop back to normal after the delete is finished? Is there something else I should run after a large delete like this? Server is running Fedora Core 6 with MySQL 5.0.27.
View Replies !
Mysql Select High Priority And Delayed Queries
I have a large database that has indexes on it. It also has alot of reads on the select statements of the database. I also have alot of people inserting new articles to the database through a submission form where they can only add one article per time. The problem is that when someone inserts an article into the database it has to insert the info and also update the indexes that need to be changed. Now when this is happening I think it is halting the select queries, making it sometime before a visitor can view a page that uses select queries. I have now implemented the "SELECT HIGH_PRIORITY" tag in many of my normal select queries on the articles pages. Is doing this just fine or will I also need to do something to the "INSERT" queries like "INSERT DELAYED" Also, the inserts are inserted into the same table as the select queries are done. I don't quite understand all of this, and have read about it on Mysql pages, but can't quite get it when it comes to speeding up things using high priority and delayed. I have the right indexes in place, so they are fine.
View Replies !
|
TRACKER
