Optimize All Tables In A MySQL Database
how exactly it helps?
mysqlcheck -o -u...
Like what it does to "optimize" them? Does it really help?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
MySQL Doubt: More DB's & Less Tables || More Tables Less DB's
im planning on designing a webservice, it will have a lot of data, spread in many tables. The problem is that there will be tables created constantly (around 5 each day..) All tables/data will be accessed equally, so I dont know how to set up the system, to create multiple databases, and balance the number of tables equally on each database, or create less databases and have a lot of tables on each one. Which one has more efficiency?
View Replies!
View Related
Convert All Mysql Tables To Utf8
I have a vbulletin (3.7.2) board with many mods installed (nearly 70) without a problem. However, upon installing one new mod I got a database error stating: MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' How do I change ALL tables in one shot (or sql query via phpmyadmin) from the two different collations to utf8? Also, is utf8 suggested or better than latin1?
View Replies!
View Related
Optimize MySQL
I recently got my first dedicated server and now have it all up and running. I would like to optimize mySQL for improved performance. Server running: CentOS 5, Tomcat, mySQL 5 Most queries: Single line queries (SELECT * FROM table WHERE name='John') NOTE: Most of my queries include the return of at least one blob (size range 100kb to 1Mb) Server Specs: Dual Core Intel Pentium 3Ghz, 4GB RAM, 500Gb hard disk Below is my current my.cnf file: Code: [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 max_allowed_packet=16M skip-innodb skip-bdb [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid I've looked through the mySQL documentation but there is no substitute for experience when it comes to these things.
View Replies!
View Related
Mysql Deleted Several Tables Under High Server Load
we have mysql 5 setup and this morning at around 10:07 tables started disappearing as they were being accessed by different clients. Databases that had 40 tables now had 30, etc. Only the tables that were attempted to be accessed were gone. This is the first time something like this has happened. The following output was given: This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=208 max_connections=500 threads_connected=156 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1096188 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xaf82930 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xb143932c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x816b1a0 0xaf1898 0x20 0x81ac49d 0x8182914 0x8189010 0x8189df1 0x818a738 0x818ae5c 0xaeb371 0x9c4ffe New value of fp=(nil) failed sanity check, terminating stack trace! Please read [url]and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xaf36620 = SELECT * FROM `sessions` WHERE `PHPSESSID` = '5e6775cd3c6f187d8c575127ba73be19' thd->thread_id=113407 The manual page at [url]contains information that should help you find out what is causing the crash. mysqld: my_new.cc:51: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed. Number of processes running now: 0 070427 10:07:49 mysqld restarted 070427 10:07:50 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 070427 10:07:53 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 227822203. InnoDB: Doing recovery: scanned up to log sequence number 0 227822203 070427 10:07:53 InnoDB: Started; log sequence number 0 227822203 070427 10:07:54 [Note] /usr/sbin/mysqld: ready for connections.
View Replies!
View Related
Check My Mysql Config - Optimize Or Upgrade
we've currently got a 2 server setup, apache+mysql Mysql server is a Xeon 3.2Ghz with 2Gb ram, scsi 73gb raid1 disks Server has been running a while, steadily increasing in traffic. whilst 90% of the time it's running ok (load around 0.5 - 1.0, cpu around 25-50% busy mainly mysql, disk i/o around 3% busy) during heavy traffic times everything slows right down At the moment the mysql box is only running mysql so we've allocated about 1.5gb out of the 2gb to mysql. At present, when we get busy the apache server is queing up requests as it waits for the mysql server. Query log shows most queries are <1sec, any other's are being optimised but due to the nature of our website there are a few pesky queries (only occasionally being slow though). question is, can this config be tuned any more or is it time for an upgrade - mainly ram to add more innodb buffers? Here's the result of tuning-primer.sh Code: SLOW QUERIES The slow query log is enabled. Current long_query_time = 2 sec. You have 9 out of 159346 that take longer than 2 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is enabled The expire_logs_days is not set. The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually Setting expire_log_days will allow you to remove old binary logs automatically See http://dev.mysql.com/doc/refman/4.1/en/purge-master-logs.html WORKER THREADS Current thread_cache_size = 384 Current threads_cached = 15 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 150 Current threads_connected = 4 Historic max_used_connections = 18 The number of used connections is 12% of the configured maximum. Your max_connections variable seems to be fine. MEMORY USAGE Max Memory Ever Allocated : 1 G Configured Max Per-thread Buffers : 5 G Configured Max Global Buffers : 803 M Configured Max Memory Limit : 6 G Physical Memory : 1.99 G Max memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 82 K Current key_buffer_size = 5 M Key cache miss rate is 1 : 1862 Key buffer fill ratio = 100.00 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is supported but not enabled Perhaps you should set the query_cache_size SORT OPERATIONS Current sort_buffer_size = 16 M Current read_rnd_buffer_size = 7 M Sort buffer seems to be fine JOINS Current join_buffer_size = 4.00 M You have had 0 queries where a join could not use an index properly Your joins seem to be using indexes properly join_buffer_size >= 4 M This is not advised OPEN FILES LIMIT Current open_files_limit = 11095 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_cache value = 5467 tables You have a total of 732 tables You have 155 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 255 M Current tmp_table_size = 256 M Of 21109 temp tables, 35% were created on disk Effective in-memory tmp_table_size is limited to max_heap_table_size. Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 7 M Current table scan ratio = 929 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 117 You may benefit from selective use of InnoDB. If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1' Server is mainly Innodb due to previous row-locking issues.
View Replies!
View Related
Reloading A Mysql Database
My server admin linked me to this: [url] I'm using a cpanel based server, but I can't get this to work. It requires I be in a directory I figure (this step isnt included) so I type cd /home/username/public_html/ And then i follow the commands. but it doesnt work. Basically i need to take a mysql database that I uploaded to my directory, and load it back into a database. :@
View Replies!
View Related
Get Mysql Database From Shell
I have a problem getting mysql to start, so I am planning to setup a new server and move all the mysql databases to a new server. The problem is I normally get mysql databases when mysql is running by using mysql command lines. Now that I cannot start mysql, I cannot use that method. Is there a mysql directory that contains all the mysql databases for me to tar it up and untar it on a different? If there is a directory that contains all the mysql databases, do I need to exclude some files/folders so it doesnt conflict with the files/folders on my new mysql server?
View Replies!
View Related
MySQL With Huge Database
ways to improve the database performance in the situation when I have to modify a large table (several million rows), by e.g. adding a column. Currently this would take several hours which is too slow. The bottleneck is disk I/O. I am considering either partitioning the table over several innodb files on several disks, or going to a RAID-5 or RAID-10, it this will give me better write performance. The database is 130GB large, and the problem table (which I make period changes to) is the largest table on the server. I cannot have downtime of 3 hours each time I make a change and adding blank fields (to be used later, when a new field is needed) is not an option. Each time I add a column, the cpu goes into high (80%) io wait state for about 3 hours. I have a hack which would allow me to split the large table into multiple smaller tables based on some criteria (for example, forumID or such). Here are a couple of things but would like to know which is best, and am open to new ideas. The ideas so far: 1. Split the table into 3 or 5 smaller tables each on it's own disk. The disk IO would then not be so bad, and it might only take 1 hour to perform the table change. But this might not work because the changes to the database (as in adding a column) might be serial, meaning only 1 disk is being written to at a time. (Then again, maybe it will work if I launch 3 different scripts, one to update each table at once). 2. Do RAID 5 or 10, and have 3 or 5 disks. This again might not help at all because of the above issue with MySQL writing serially. I am using latest MySQL 5.0.45 with InnoDB engine on Debian etch Linux
View Replies!
View Related
How To Secure Access To MySQL Database
What can I do to secure access to my MySQL database to stop it from getting compromised from hackers? Is there any way I can restrict access to the MySQL database to my IP only? The database is for an IPB 2.3.4 forum and the username/password are stored in a conf_global.php file that is CHMODed 0444.
View Replies!
View Related
Big Mysql Database 4gb
i been trying to move my server.. But mysql database is quite big and restoring takes ages 10 12 hours.. is there anyway i can make it quick because database dump takes 10 15mins but restore takes 12hours and it should be a way to make that quick
View Replies!
View Related
Managed Database Hosting (Mysql)
if there are any recommendations on managed database hosting services. This will be used for a fairly large project running on Mysql DB. Due to the size and complexity of the database a lot of resources are being used, so I prefer to find a company which specializes in dB hosting.
View Replies!
View Related
[mySQL] Copy Colomn From 1 Database To Another
I'm trying to copy the contents of a column from 1 database to another. I need to do this as the contents in the current database have got messed up after an upgrade. The DB structure table forum_msgs It contains a column called "topic_id". Basically I need to copy over the topic ID fields from the "old" database into the "current" database into table forum_msg replacing any values that are currently in the column "topic_id" I tried this but it doesn't seem to work correctly. Any ideas how it should be done? insert current.forum_msgs ( topic_id ) select topic_id from old.forum_msgs
View Replies!
View Related
Alterntive Way For Downloading A MySQL Database
A friend of mine has an account with a hosting company that recently became unstable and he wants to move his site away. I downloaded the files, but it now seems impossible to download the databases in the conventional ways. I tried downloading them using their control panel (vDeck) but I get an error "File Permissions do not allow to download backup". Then I tried to export it using PHPMyAdmin but the files either doesn't download at all, or it downloads inccomplete with the last lines having something like the following: Cannot modify header information - headers already sent by (output started at /usr/local/apache/htdocs/phpMyAdmin-2.6.3/export.php:121 I tried to contact their support but they have no clue. Is there any way to download the database? I thought of creating a script that would manually itterate through the tables and create an export fie similar to what phpMyAdmin creates. I don't know if this is possible, and I hope there are better ways.
View Replies!
View Related
MySQL Cannot Connect To Database Via PHP
I got this weird problem with my VPS which has MySQL 4.1.22 running on it. Everything works fine for sometime and then out of a sudden, it stop recognizing the mysql users and refused to allow my PHP scripts to access the database. It seems that I needed to restart mySQL server to resolve the issue. Then out of the blues, the same problem occurs. I am running Cpanel on a VPS and there isn't any overloading as the database access is pretty light on my 512mb VPS. I contacted my web host about the issue and it seems like all they do is to restart the mySQL. This cannot be a solution as it happens at least once or twice a day depending on how soon I could catch the problem. All the time this problem occurs, the MYSQL Server is still active and working. I can even access it through PHPMyAdmin but not through the scripts. I just added an hourly crontab so as to restart the MySQL server as a stop gap.
View Replies!
View Related
Backup BIG MySQL Database
What is the recommended method of backing up big MySQL database of size 2 GB ? Quote: # mysqldump gallery > gallery.sql mysqldump: Got error: 1017: Can't find file: './gallery/10tir_user_group.frm' (errno: 24) when using LOCK TABLES # I checked the table, it is fine, but can't do the backup. Quote: mysql> check table 10tir_user_group; +-------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------+-------+----------+----------+ | gallery.10tir_user_group | check | status | OK | +-------------------------+-------+----------+----------+ 1 row in set (0.07 sec) mysql>
View Replies!
View Related
Exporting Mysql Database From Helm
i have a fren who wants to transfer a hosting ..for a site made in php and hosted in helm...windows server... the site uses mysql datbase but i could see no database in the database setting(of helm)...and says no database created.. in host i could see server.mysql in php database config files i want to export database...to linux server i have not used helm much.. where actually is database located and how can it be exported?
View Replies!
View Related
Fetch Admin Password From MySQL Database
I have forgot the admin password for one of our application. All the users and passwords are stored in database table "users", is there any way I can get the password from the database.. any MySQL query that will fetch the admin password from that database table.
View Replies!
View Related
Apache: Load Vhosts From MySQL Database
if it was possible to load vhosts from a MySQL database and the only solutions i found didnt work for me. Im running Apache on Windows. Has anyone seen anything that would work with Windows? The only alternative i can think of is to read the database info using PHP and output it to a vhosts config file. I dont want to do that though because it means restarting the server to load the new config and i hear that a lot of vhosts slows the server down
View Replies!
View Related
Mysql Database Dump (150mb) Utf8
recently I had problems with backing up my ~150mb database through PHP. And I know some people are backing their databases to a dump file through terminal. My database contains UTF-8 and other characters. I have tried simply dumping with mysqldump but I doesn't seem to work, when restoring all the characters are messed up. Is there any command to tell that the database is UTF-8?
View Replies!
View Related
MySQL Database Service Stops From Running
I have a phpBB forum, which uses MySQl database. For the last two months the forum has been going down every couple of days because the database service has been continuously stoped running on the server with no reason. What can cause such a frequent database failures? Can someone attack the forum/database/server in a way that will cause the above problem? (there is a good reason I consider this possibility very seriously) How can I prevent futher database failures?
View Replies!
View Related
Copying (MySQL) Database Without Panel Access.
A customer would like to move his hosting from another host to me. But the other host does not want to cooperate and give me access to the panel or files. Threw the customer I did get the ftp information, and within the config file I have the database username and password. How is it possible to copy the database, without having the hosting panel access?
View Replies!
View Related
|
TRACKER
