Please help with indexes
Hi,
i have table which would contain data describing holiday houses. It's it:
idbigint(20) unsigned PRIauto_increment
nametinytext
descriptiontext
webpagetinytext
emailtinytext
whole_cantinyint(1) unsigned
whole_onlytinyint(1) unsigned
typeenum('house','apartment','hotel')
person_mintinyint(3) unsigned
person_maxtinyint(3) unsigned
children_mintinyint(3) unsigned
children_maxtinyint(3) unsigned
animal_mintinyint(3) unsigned
animal_maxtinyint(3) unsigned
iconsbigint(20) unsigned
date_adddatetime
date_modifydatetime
I will make a search which should find using random criteria. Sometimes
it would be find houses (type=house) which can contain 4 to 5 person
(person_min = 4 and person_max = 5), sometimes it would be find
hotels which could be reserved whole (type=hotel and whole_can=1) and
which could contain 100 person (person_min = 100 and person_max = 100)
They could be more types of queries, but mostly which person_min/max and
type columns.
How I should create indexes to boost SELECT queries? My tables are innodb.
--
Marcin Lewandowski
[ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ]
[ http://www.nosoftwarepatents.com/pl/m/intro/index.html ]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Synchronizing InnoDB tables
Hi, I want to use InnoDB tables in my project, because I can use transactions with them. I would write a hotel room's reservation system and sometimes I would need to get from particular SELECT query exact state - whether room is reserved or not. Because, when I'm using innodb I work only on copy of database, is it possible to check if any other connection is not writing to particular table? Would locking tables help? Or maybe I should do something else then locking table when writing reservation? Thanks, -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using InnoDB on 2 OSes
Hi, I've got Win2k on one FAT32 partition, and I use MySQL 4.0.23 there. I've got gentoo linux on second partition, and I want to use the same databases on linux's MySQL. I've configured linux's mysql to use databases from FAT32 partition, but InnoDB tables don't work. phpmyadmin show them as in usage. I suppose, that could be caused by fact, that when I'm shutting down windows, mysql is just killed, without proper saving innodb logfiles. Or maybe there are other reason? How to configure that? -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Jocelyn Fournier napisa(a): Hi, What about using another forum ? phpbb2 is well known to be far for what could be called optimized :) I hate phpbb, but currently we can't change it :( -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Performance
Hi, I've got webserver. There, I've got phpbb2 with circa 6000 users (average 70-100 users online). There was problems with locking or something else, when phpbb was using myisam tables. Yesterday, we have converted tables to innodb, because it should be more effective. Since then we have high system load. server root # uptime 16:11:17 up 1 day, 23:56, 4 users, load average: 1.37, 1.35, 6.63 server root # free total used free sharedbuffers cached Mem:508284 506732 1552 0 2800 322848 -/+ buffers/cache: 181084 327200 Swap: 1000400 128308 872092 MyTop shows that there are about 40-50 queries per second. MySQL is 4.0.22-log (gentoo linux) Here comes my.cnf: [client] port= 4417 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log-error = /var/log/mysql/mysqld.err innodb_data_file_path = ibdata1:64M:autoextend innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 basedir = /usr datadir = /data/mysql tmpdir = /tmp language= /usr/share/mysql/polish log-slow-queries = /data/logs/mysql/slow.log log-update = /data/logs/mysql/update.log skip-locking skip-bdb low-priority-updates max_write_lock_count = 7 character-set = latin2 set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K long_query_time = 4 wait-timeout= 60 max-connections = 150 port= 4417 [mysqldump] quick set-variable= max_allowed_packet=1M [mysql] [isamchk] set-variable= key_buffer=16M I've tried many values in innodb_data_file_path = ibdata1:64M:autoextend Thanks in advance -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
I've changed settings to: innodb_data_file_path = ibdata1:128M:autoextend innodb_buffer_pool_size=150M innodb_additional_mem_pool_size = 50M and system load is only 2 to 3. kernel napisa(a): What does the cpu % show when the machine has the high load avg ? Now, there are about 50% of normal load and system load is only circa 1.0. On myisam there was about 0.5. And here comes few lines from top: Cpu(s): 19.9% us, 2.6% sy, 0.0% ni, 74.8% id, 2.3% wa, 0.0% hi, 0.3% si Cpu(s): 14.3% us, 1.0% sy, 0.0% ni, 82.4% id, 2.0% wa, 0.0% hi, 0.3% si Cpu(s): 26.9% us, 3.3% sy, 0.0% ni, 69.4% id, 0.0% wa, 0.0% hi, 0.3% si Usually high load avgs point to disk I/O isssues. What is the size of your ibdata1 file ? If you have more ram, you can increase I've got 512 mb of RAM, and it's full (and 200mb of swap is currently used). server root # ls -l /data/mysql/ib* -rw-rw 1 mysql mysql5242880 Apr 12 23:14 /data/mysql/ib_logfile0 -rw-rw 1 mysql mysql5242880 Apr 12 18:48 /data/mysql/ib_logfile1 -rw-rw 1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1 innodb_buffer_pool_size or do some tweaks to the OS so it caches the disk a little more. -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Gary Richardson napisa(a): Hey, How much load is system vs user? I found that when my company converted some large tables on our old server, the concurrent disk IO increased. Your database server is doing more in parallel and accessing more from your disk at one time. That would be my guess. One option is to get a faster disk interconnect (SATA or SCSI, especially if you are running on IDE). I've got IDE hdd. Is there simple way to check, if it's overloaded? Would RAID1 help? (I don't know if in raid1 there are parralel reads or maybe one disk is only a mirror) The other option is to increase innodb_buffer_pool_size, which can be tough if you are running your webserver on the same box. This is where InnoDB stores your database in memory. The more it can store, the less disk IO is required. But if it would be too high, server would use swap, what makes more io calls. :( Without knowing how much data there is, I'd probably bump the systems ram up to 2 gigs (from 512) and set innodb_buffer_pool_size to 1GB. How big is the database? Your data file is set to autoextend, how big is the ibdata1 file? 700mb of innodb tables (where phpbb_posts_text contains 17 records = 116mb and phpbb_search_wordmatch contains about 500 records = 500mb, rest is smaller) and 200mb of myisam tables server root # ls -l /data/mysql/ib* -rw-rw 1 mysql mysql5242880 Apr 12 23:14 /data/mysql/ib_logfile0 -rw-rw 1 mysql mysql5242880 Apr 12 18:48 /data/mysql/ib_logfile1 -rw-rw 1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1 As I wrote in reply to kernel's message, I've changed settings to: innodb_data_file_path = ibdata1:128M:autoextend innodb_buffer_pool_size=150M innodb_additional_mem_pool_size = 50M and system load is only 2 to 3. But in my opinion, it's still to high, and website is growing so in month or two there would be serious problem if I don't fix that issue. -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb - in usage
Hi, I had got webserver with mysql 4.0.20 (if I remember well) compiled from sources on slackware 9. Now, I've bought new machine, and I've installed gentoo with mysql 4.0.22. I've copied (in shell) datadir to new machine, preserving attributes. Now, every of my innodb table in phpmyadmin is not browsable and there are text in usage near name of them. I exported using mysqldump data from old mysql, and tried to do drop dababase `name`; in new one, to delete invalid db, and there was an error like these: unknown table table1,table2,table3 where table1 etc. stands for names of my tables in `name` database. What I should do? -- marcin lewandowski gg# 188068 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL hangs
Hi, I've got webserver with php and mysql-4.0.22 There I've got large phpbb2. Sometimes, server's system load rapidly grows, and mysql are locked. Normally, there are 7-10 mysql processes, at this strange situation, there are 30-40. I don't know what can make such big system load in time of 1 minute. Maybe this could help: http://saepia.net/temp/mysql.txt It's 'show processlist' when mysql is locked. Mysql load is about 50 queries per second. There are mostly myisam tables and they are stored on ide100 drive with dma enabled. Or maybe it's DoS? -- m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Hangs
Hi, I've got webserver with php and mysql-4.0.22 There I've got large phpbb2. Sometimes, server's system load rapidly grows, and mysql are locked. Normally, there are 7-10 mysql processes, at this strange situation, there are 30-40. I don't know what can make such big system load in time of 1 minute. Maybe this could help: http://saepia.net/temp/mysql.txt It's 'show processlist' when mysql is locked. Mysql load is about 50 queries per second. There are mostly myisam tables and they are stored on ide100 drive with dma enabled. Or maybe it's DoS? -- m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bcc with mysql
unni krishnan napisa(a): hi, I want to connect to mysql using borland c. The version is bcc5.0 and for mysql it is mysql4.0.13 The compilation is ok... but its showing linker error like unresolved external mysql_init referenced from module ... what is the problem??? How to specify the correct library... If anybody knows the solutions, pls reply... unnikrishnan I'm using mysql4 with bcb6. You should: 1. download mysql. unpack it 2. Copy *.h files from includes dir to bcb include dir. 3. Copy libmysql.dll and libmysql.lib to your app's dir. 4. Convert libmysql.lib using coff2omf tool 5. My app wasn't compiling succesfully without NO_CLIENT_LONG_LONG defined 6. Add .lib to your project 7. #include winsock.h before #include mysql.h 8. In future, use google -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge vs multiple innodb performance
Hi, I'm writing an windows app, which would connect to mysql server and modify user's data. There would be a few hundred of users. Every of them should have access only to few tables. It's not a problem with mysql's authentication mechanism. Kind of compilation of the data from users' tables should be accessed on web (via php scripts). For example: every user have table LOGIN_customers with the same structure. On web, I need to make SELECT (something) FROM LOGIN1_customers, LOGIN2_customers, LOGIN3_customers WHERE (something). I thought, that MERGE tables could be good solution but they don't have transactions and they have limitations. What would be faster? MERGE tables or queries with 200-400 InnoDB tables in FROM? -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge vs multiple innodb performance
Chuck Herrick napisa(a): 200 - 400 tables is too many. Is it too many for merge, innodb or both? Try having one CUSTOMERS table. You know who is logged in, so you can use that information in a WHERE clause. Yes, but If somebody would find a password (maybe using brute-force attack) to one account, could delete data of other users... -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
